Re: Join efficiency
От | tanjunhua |
---|---|
Тема | Re: Join efficiency |
Дата | |
Msg-id | 0f5301ca2b8e$f57beb90$aa1c10ac@RKC.local обсуждение исходный текст |
Ответ на | Join efficiency ("Russ Brown" <postgres@dot4dot.plus.com>) |
Список | pgsql-general |
I'm sorry for my mistake, the postgre version is: test_db=# SELECT version(); version --------------------------------------------------------------------------------------------------------------- PostgreSQL 8.1.8 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21) (1 row) ----- Original Message ----- From: "tanjunhua" <tanjh@riso.co.jp> To: <pgsql-general@postgresql.org> Sent: Wednesday, September 02, 2009 2:31 PM Subject: [GENERAL] Join efficiency > 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
В списке pgsql-general по дате отправления: