Thread: Enum
Is there a query i can use to get the fields of an Enum, just in case someone needs it for the client application.
Xai wrote: > Is there a query i can use to get the fields of an Enum, just in case > someone needs it for the client application. > select e.enumlabel from pg_enum as e join pg_type as t on (t.typtype='e' and e.enumtypeid=t.typbasetype) where t.typname = $1 order by e.enumtypid; I think. or something close to that.
Hello, everybody.
In my project, I have a select syntax to get record summary between three
tables. one of them is tab_main consist of 46 columns(with 27797 records),
another is tab_user consist of 32 columns(with 3 records) and the last one
is tab_property consist of 117 columns(with 30541 records). I have the
trouble that it cost me a lot of time when execute the select syntax. the
following is the select syntax and analyze result.
table structure:
tab_main(id, uid, status, bpassword, realdelflag, delflag, kind, ...)
tab_user(uid, printauth, bprtpermit, ...)
tab_property(id, mode, ...)
1. select syntax:
EXPLAIN ANALYZE SELECT count(Id) FROM (SELECT DISTINCT t1.Id AS Id FROM
tab_main t1, tab_user t2, tab_property t3 WHERE (t1.uid = 2 AND t1.status >=
21 AND t1.bpassword = 0 AND t1.realdelflag = 0 AND (t1.kind= 1 OR (t1.kind
= 0 AND (t1.delflag <> 0 OR (t2.uid = 2 AND (t2.printauth = 2 OR
t2.printauth = 3) AND t2.bprtpermit = 0 AND t3.id = t1.id AND (t3.mode = 0
OR t3.mode = 1))))))) subt0;
2. analyze result:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=19129.93..19129.94 rows=1 width=4) (actual
time=10661.656..10661.658 rows=1 loops=1)
-> Unique (cost=18672.11..19129.92 rows=1 width=4) (actual
time=8288.446..10661.586 rows=5 loops=1)
-> Sort (cost=18672.11..18901.01 rows=91562 width=4) (actual
time=8288.440..9532.507 rows=458115 loops=1)
Sort Key: t1.id
-> Nested Loop (cost=1550.00..10341.45 rows=91562 width=4)
(actual time=5.002..4724.436 rows=458115 loops=1)
Join Filter: (("inner".jobkind = 1) OR
(("inner".jobkind = 0) AND (("inner".delflag <> 0) OR (("inner".uid = 2) AND
(("inner".printright = 2) OR ("inner".printright = 3)) AND
("inner".print_permitcolorprint = 0) AND ("outer".id = "inner".id) AND
(("outer".colormode = 0) OR ("outer".colormode = 1))))))
-> Seq Scan on job_p t3 (cost=0.00..4668.41
rows=30541 width=8) (actual time=0.023..170.619 rows=30541 loops=1)
-> Materialize (cost=1550.00..1550.03 rows=3
width=24) (actual time=0.002..0.055 rows=21 loops=30541)
-> Nested Loop (cost=0.00..1550.00 rows=3
width=24) (actual time=4.949..149.081 rows=21 loops=1)
-> Seq Scan on job_ctl t1
(cost=0.00..1548.94 rows=1 width=12) (actual time=4.906..148.794 rows=7
loops=1)
Filter: ((uid = 2) AND (jobsts >= 21)
AND (pinflag = 0) AND (realdelflag = 0))
-> Seq Scan on users t2 (cost=0.00..1.03
rows=3 width=12) (actual time=0.007..0.016 rows=3 loops=7)
Total runtime: 10696.630 ms
(13 rows)
could anyone explain the result of analyze and give me some idea to speed up
the select? looking forward your response.
best wishes.
winsea
On Wed, Sep 02, 2009 at 02:31:46PM +0900, tanjunhua wrote: > I > have the trouble that it cost me a lot of time when execute the select > syntax. the following is the select syntax and analyze result. > EXPLAIN ANALYZE SELECT count(Id) FROM (SELECT DISTINCT t1.Id AS Id FROM > tab_main t1, tab_user t2, tab_property t3 WHERE (t1.uid = 2 AND t1.status > >= 21 AND t1.bpassword = 0 AND t1.realdelflag = 0 AND (t1.kind= 1 OR > (t1.kind = 0 AND (t1.delflag <> 0 OR (t2.uid = 2 AND (t2.printauth = 2 OR > t2.printauth = 3) AND t2.bprtpermit = 0 AND t3.id = t1.id AND (t3.mode = 0 > OR t3.mode = 1))))))) subt0; That WHERE clause is far too complicated to allow PG's optimizer to have a chance. The "Nested Loop" running over sequential scans is a sign that things aren't going to work out well. OR clauses are the awkward one, as you've got one at the top of your WHERE clause it's going to force PG to do slow things. It looks somewhat strange as well, do you really want to join *every* row in "tab_main" to *every* row in "tab_user" when "tab_main.kind" doesn't equal zero? Maybe if you could describe what you want to do in English then the query would make a bit more sense. -- Sam http://samason.me.uk/
thanks for your response. > Maybe if you could describe what you want to do in English then the > query would make a bit more sense. I just want those records as the below rule: 1. the record of which uid is 2, status is more than 20, bpassword is 0 and realdelflag is 0 in tab_main; 1.1 the record of which kind is 1 in those that filtered through step1; 1.2 the record of which kind is 0 in those that filtered through step1; 1.2.1 the record of which delflag doesn't equal 0 in those filtered through step1.2; 1.2.2 the record of which uid equal 2, printauth equal 2 or 3 and bprtpermit equal 0 in tab_user left join those filtered through step1.2; 1.2.2.1 the record of which mode equal to 0 or 1 in tab_property and left join those filtered through step1.2.2 using id; such as the following data, the expect result is 1 record and detail information is: id | uid | status| bpassword| realdelflag| delflag| kind -------+-----+--------+---------+-------------+---------+--------- 39731 | 2 | 21 | 0 | 0 | 0 | 1 tab_main: id | uid | status| bpassword| realdelflag| delflag| kind -------+-----+--------+---------+-------------+---------+--------- 39752 | 1 | 0 | 0 | 0 | 0 | 2 39751 | 1 | 21 | 0 | 0 | -1 | 2 39750 | 2 | 0 | 1 | 0 | 0 | 2 39749 | 2 | 21 | 1 | 0 | -1 | 2 39748 | 2 | 0 | 1 | 0 | 0 | 2 39731 | 2 | 21 | 0 | 0 | 0 | 1 39728 | 2 | 1 | 1 | 0 | 0 | 1 39727 | 2 | 1 | 0 | 0 | 0 | 1 39710 | 0 | 0 | 0 | 0 | 0 | 1 39709 | 2 | 0 | 1 | 0 | 0 | 1 39681 | 0 | 0 | 0 | 0 | -1 | 0 4333 | 0 | 0 | 0 | 0 | -1 | 0 tab_user: uid | printauth| bprtpermit -----+------------+------------------------ 1 | 1 | 1 2 | 2 | 0 3 | 1 | 1 tab_property: id | mode -------+----------- 39731 | 1 39728 | 4 39727 | 4 39710 | 1 39709 | 0 > That WHERE clause is far too complicated to allow PG's optimizer to have > a chance. The "Nested Loop" running over sequential scans is a sign > that things aren't going to work out well. > OR clauses are the awkward one, as you've got one at the top of your > WHERE clause it's going to force PG to do slow things. It is my first time to use database in practise, could you give me more detail? such as how to decision the WHERE clause complication? how to make the best choice by analyze result? Would you supply some documents about postgresql performance? bese wishes. winsea
On Wed, Sep 02, 2009 at 10:11:24PM +0900, tanjunhua wrote:
> thanks for your response.
>
> >Maybe if you could describe what you want to do in English then the
> >query would make a bit more sense.
> I just want those records as the below rule:
> 1. the record of which uid is 2, status is more than 20, bpassword is 0
> and realdelflag is 0 in tab_main;
> 1.1 the record of which kind is 1 in those that filtered through step1;
> 1.2 the record of which kind is 0 in those that filtered through step1;
> 1.2.1 the record of which delflag doesn't equal 0 in those filtered
> through step1.2;
> 1.2.2 the record of which uid equal 2, printauth equal 2 or 3 and
> bprtpermit equal 0 in tab_user left join those filtered through step1.2;
> 1.2.2.1 the record of which mode equal to 0 or 1 in tab_property and left
> join those filtered through step1.2.2 using id;
That's not a very "english" explanation. That's just a translation of
what the code does, and I can do that easily enough myself. What you're
missing is what the query "means" and the intuition as to how to go
about understanding what all that really means.
I'm guessing there's a clever combination of outer joins that would make
this go fast, but I've tried to do the translation but it's all a bit
complicated to do in my head. I think it's something like:
SELECT COUNT(DISTINCT t1.id)
FROM tab_main t1
LEFT JOIN (SELECT TRUE AS userok FROM tab_user WHERE uid = 2 AND printauth IN (2,3) AND bprtpermit = 0 GROUP BY 1)
t2ON TRUE,
LEFT JOIN tab_property t3 ON t1.id = t3.id AND t3.mode IN (0,1)
WHERE t1.uid = 2
AND t1.status >= 21
AND t1.bpassword = 0
AND t1.realdelflag = 0
AND (t1.kind = 1 OR
(t1.kind = 0 AND (t1.delflag <> 0 OR (t2.userok AND t3.id IS NOT NULL))));
but I'm not sure how much I'd trust that without some testing.
> It is my first time to use database in practise, could you give me more
> detail? such as how to decision the WHERE clause complication?
> how to make the best choice by analyze result? Would you supply some
> documents about postgresql performance?
There are lots of guides around on the internet; google is your friend!
Other than trying to rewrite your queries in different ways I'm not sure
what to suggest, it'll give you experience which is the important thing.
--
Sam http://samason.me.uk/