Re: join question - Mailing list pgsql-general
| From | Grzegorz Jaśkiewicz |
|---|---|
| Subject | Re: join question |
| Date | |
| Msg-id | 2f4958ff0810221552r1c6bc907s6ce6d1aec22980b2@mail.gmail.com Whole thread Raw |
| In response to | Re: join question (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: join question
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: join question ("marcin mank" <marcin.mank@gmail.com>) |
| List | pgsql-general |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=37807.04..37807.05 rows=1 width=50) (actual time=9788.642..9805.832 rows=20000 loops=1)
Sort Key: s.nodeid
Sort Method: external sort Disk: 1320kB
-> Nested Loop (cost=376.60..37807.03 rows=1 width=50) (actual time=15.454..9629.198 rows=20000 loops=1)
-> Nested Loop Anti Join (cost=376.60..37800.27 rows=1 width=50) (actual time=15.347..9077.445 rows=20000 loops=1)
-> Nested Loop (cost=376.60..37797.99 rows=1 width=50) (actual time=15.308..8927.428 rows=20000 loops=1)
-> Hash Anti Join (cost=376.60..37791.22 rows=1 width=8) (actual time=15.195..8216.448 rows=20000 loops=1)
Hash Cond: (e.accountid = account.id)
-> Bitmap Heap Scan on efoo e (cost=368.23..37709.63 rows=19523 width=8) (actual time=14.981..8166.262 rows=20000 loops=1)
Recheck Cond: (packageid = 497)
Filter: ((startdate <= now()) AND (enddate > now()))
-> Bitmap Index Scan on efoo_packageid_idx (cost=0.00..363.35 rows=19523 width=0) (actual time=9.694..9.694 rows=20000 loops=1)
Index Cond: (packageid = 497)
-> Hash (cost=8.35..8.35 rows=1 width=8) (actual time=0.136..0.136 rows=1 loops=1)
-> Index Scan using account_banned_idx on account (cost=0.00..8.35 rows=1 width=8) (actual time=0.129..0.131 rows=1 loops=1)
Index Cond: (banned = true)
Filter: banned
-> Index Scan using bbaccididx on bb s (cost=0.00..6.76 rows=1 width=42) (actual time=0.030..0.032 rows=1 loops=20000)
Index Cond: (s.accountid = e.accountid)
-> Index Scan using bbar_bbid_key on bbar b (cost=0.00..2.27 rows=1 width=11) (actual time=0.005..0.005 rows=0 loops=20000)
Index Cond: ((b.bbid)::text = (s.id)::text)
-> Index Scan using acct_ididx on account a (cost=0.00..6.75 rows=1 width=24) (actual time=0.024..0.025 rows=1 loops=20000)
Index Cond: (a.id = e.accountid)
Total runtime: 9815.280 ms
and before:
QUERY PLAN before
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=130129.98..130178.78 rows=19521 width=50) (actual time=16156.145..16170.234 rows=20000 loops=1)
Sort Key: s.nodeid
Sort Method: external merge Disk: 1312kB
-> Hash Anti Join (cost=78755.00..128101.84 rows=19521 width=50) (actual time=12836.008..16071.668 rows=20000 loops=1)
Hash Cond: ((s.id)::text = (b.bbid)::text)
-> Hash Join (cost=78752.17..127830.60 rows=19523 width=50) (actual time=12825.755..16043.271 rows=20000 loops=1)
Hash Cond: (e.accountid = s.accountid)
-> Merge Join (cost=39100.97..79171.13 rows=19523 width=32) (actual time=11496.544..12614.860 rows=20000 loops=1)
Merge Cond: (a.id = e.accountid)
-> Index Scan using acct_ididx on account a (cost=0.00..37277.39 rows=1000002 width=24) (actual time=0.183..859.610 rows=999950 loops=1)
Filter: (banned <> true)
-> Sort (cost=39100.93..39149.73 rows=19523 width=8) (actual time=11496.268..11507.031 rows=20000 loops=1)
Sort Key: e.accountid
Sort Method: external sort Disk: 472kB
-> Bitmap Heap Scan on efoo e (cost=368.23..37709.63 rows=19523 width=8) (actual time=14.640..11395.226 rows=20000 loops=1)
Recheck Cond: (packageid = 497)
Filter: ((startdate <= now()) AND (enddate > now()))
-> Bitmap Index Scan on efoo_packageid_idx (cost=0.00..363.35 rows=19523 width=0) (actual time=9.377..9.377 rows=20000 loops=1)
Index Cond: (packageid = 497)
-> Hash (cost=18850.09..18850.09 rows=1000009 width=42) (actual time=1326.158..1326.158 rows=1000009 loops=1)
-> Seq Scan on bb s (cost=0.00..18850.09 rows=1000009 width=42) (actual time=0.032..424.731 rows=1000009 loops=1)
-> Hash (cost=1.81..1.81 rows=81 width=11) (actual time=10.111..10.111 rows=81 loops=1)
-> Seq Scan on bbar b (cost=0.00..1.81 rows=81 width=11) (actual time=9.971..10.013 rows=81 loops=1)
Total runtime: 16206.639 ms
(24 rows)
Time: 16217,107 ms
--
GJ
--
GJ
pgsql-general by date: