Search Postgresql Archives

Re: join question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



"we're even more in the dark than you are."

:)

so here are the plans, that's the real table run.

                                                                                             QUERY PLAN after                                                                              

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux