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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
