Re: A question on the query planner - Mailing list pgsql-performance

Tom Lane wrote:

>>Hmmm ... [squints] ... it's not supposed to do that ...
>>
>>
>
>The attached patch seems to make it better.
>
>
>
The patch definitely makes things more consistent...unfortunately it is
more
consistent toward the slower execution times.  Of course I am looking at
this
simply from a straight performance standpoint and not a viewpoint of
what *should*
be happening. At any rate here are the query plans with the various
settings.

Default Settings:

                                                                 QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=15290.20..15300.34 rows=4058 width=80) (actual
time=2944.650..2951.292 rows=4672 loops=1)
   Sort Key: q.date_time
   ->  Hash Join  (cost=13529.79..15046.99 rows=4058 width=80) (actual
time=2678.033..2873.475 rows=4672 loops=1)
         Hash Cond: (("outer".car_id)::text = ("inner".car_id)::text)
         ->  Seq Scan on cars c  (cost=0.00..227.01 rows=9401 width=37)
(actual time=19.887..50.971 rows=9401 loops=1)
         ->  Hash  (cost=13475.65..13475.65 rows=4058 width=62) (actual
time=2643.377..2643.377 rows=0 loops=1)
               ->  Hash Join  (cost=1088.19..13475.65 rows=4058
width=62) (actual time=86.739..2497.558 rows=10284 loops=1)
                     Hash Cond: (("outer".zip)::text = ("inner".zip)::text)
                     ->  Seq Scan on quotes q  (cost=0.00..10664.25
rows=336525 width=27) (actual time=0.223..1308.561 rows=340694 loops=1)
                     ->  Hash  (cost=1086.90..1086.90 rows=516 width=52)
(actual time=84.329..84.329 rows=0 loops=1)
                           ->  Seq Scan on zips z  (cost=0.00..1086.90
rows=516 width=52) (actual time=78.363..82.901 rows=718 loops=1)
                                 Filter: ((state)::text = 'WA'::text)
 Total runtime: 2955.366 ms

SET enable_seqscan=false;


QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=103557.82..103567.97 rows=4058 width=80) (actual
time=1015.122..1021.750 rows=4673 loops=1)
   Sort Key: q.date_time
   ->  Merge Join  (cost=102734.94..103314.61 rows=4058 width=80)
(actual time=802.908..941.520 rows=4673 loops=1)
         Merge Cond: ("outer"."?column7?" = ("inner".car_id)::text)
         ->  Sort  (cost=102734.94..102745.08 rows=4058 width=62)
(actual time=802.112..812.755 rows=4827 loops=1)
               Sort Key: (q.car_id)::text
               ->  Nested Loop  (cost=0.00..102491.73 rows=4058
width=62) (actual time=148.535..555.653 rows=10285 loops=1)
                     ->  Index Scan using zip_zips_index on zips z
(cost=0.00..1272.69 rows=516 width=52) (actual time=148.243..155.577
rows=718 loops=1)
                           Filter: ((state)::text = 'WA'::text)
                     ->  Index Scan using zip_quotes_index on quotes q
(cost=0.00..195.55 rows=48 width=27) (actual time=0.042..0.454 rows=14
loops=718)
                           Index Cond: (("outer".zip)::text = (q.zip)::text)
         ->  Index Scan using cars_car_id_btree_index on cars c
(cost=0.00..506.87 rows=9401 width=37) (actual time=0.220..46.910
rows=12019 loops=1)
 Total runtime: 1027.339 ms

There is still a 3x decrease in execution time here, but it is overall
slower than before the
patch was applied.

SET enable_mergejoin = false; AND SET enable_seqscan = false;


QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=104586.15..104596.29 rows=4058 width=80) (actual
time=887.719..894.358 rows=4673 loops=1)
   Sort Key: q.date_time
   ->  Hash Join  (cost=102545.88..104342.94 rows=4058 width=80) (actual
time=593.710..815.541 rows=4673 loops=1)
         Hash Cond: (("outer".car_id)::text = ("inner".car_id)::text)
         ->  Index Scan using cars_car_id_btree_index on cars c
(cost=0.00..506.87 rows=9401 width=37) (actual time=0.182..37.306
rows=9401 loops=1)
         ->  Hash  (cost=102491.73..102491.73 rows=4058 width=62)
(actual time=593.040..593.040 rows=0 loops=1)
               ->  Nested Loop  (cost=0.00..102491.73 rows=4058
width=62) (actual time=146.647..551.975 rows=10285 loops=1)
                     ->  Index Scan using zip_zips_index on zips z
(cost=0.00..1272.69 rows=516 width=52) (actual time=146.378..153.767
rows=718 loops=1)
                           Filter: ((state)::text = 'WA'::text)
                     ->  Index Scan using zip_quotes_index on quotes q
(cost=0.00..195.55 rows=48 width=27) (actual time=0.044..0.464 rows=14
loops=718)
                           Index Cond: (("outer".zip)::text = (q.zip)::text)
 Total runtime: 898.438 ms

Again a decrease in execution time.

On the other hand:
SET enable_hasdjoin=false;


QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=62829.86..62840.00 rows=4058 width=80) (actual
time=11368.025..11374.629 rows=4673 loops=1)
   Sort Key: q.date_time
   ->  Merge Join  (cost=62006.97..62586.65 rows=4058 width=80) (actual
time=11188.371..11295.156 rows=4673 loops=1)
         Merge Cond: (("outer".car_id)::text = "inner"."?column7?")
         ->  Index Scan using cars_car_id_btree_index on cars c
(cost=0.00..506.87 rows=9401 width=37) (actual time=0.167..37.728
rows=9401 loops=1)
         ->  Sort  (cost=62006.97..62017.12 rows=4058 width=62) (actual
time=11187.581..11196.343 rows=4827 loops=1)
               Sort Key: (q.car_id)::text
               ->  Merge Join  (cost=60037.99..61763.76 rows=4058
width=62) (actual time=10893.572..10975.658 rows=10285 loops=1)
                     Merge Cond: ("outer"."?column6?" = "inner"."?column4?")
                     ->  Sort  (cost=1110.15..1111.44 rows=516 width=52)
(actual time=86.679..87.166 rows=718 loops=1)
                           Sort Key: (z.zip)::text
                           ->  Seq Scan on zips z  (cost=0.00..1086.90
rows=516 width=52) (actual time=79.023..83.921 rows=718 loops=1)
                                 Filter: ((state)::text = 'WA'::text)
                     ->  Sort  (cost=58927.84..59769.15 rows=336525
width=27) (actual time=9848.479..10319.275 rows=340426 loops=1)
                           Sort Key: (q.zip)::text
                           ->  Seq Scan on quotes q
(cost=0.00..10664.25 rows=336525 width=27) (actual time=0.227..2171.917
rows=340740 loops=1)
 Total runtime: 11408.120 ms

Which really is not that surprising.

And Finally:
set enable_hashjoin=false; enable_seqscan=false;


QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=103557.82..103567.97 rows=4058 width=80) (actual
time=1206.168..1212.880 rows=4673 loops=1)
   Sort Key: q.date_time
   ->  Merge Join  (cost=102734.94..103314.61 rows=4058 width=80)
(actual time=809.448..949.110 rows=4673 loops=1)
         Merge Cond: ("outer"."?column7?" = ("inner".car_id)::text)
         ->  Sort  (cost=102734.94..102745.08 rows=4058 width=62)
(actual time=808.660..819.317 rows=4827 loops=1)
               Sort Key: (q.car_id)::text
               ->  Nested Loop  (cost=0.00..102491.73 rows=4058
width=62) (actual time=151.457..559.886 rows=10285 loops=1)
                     ->  Index Scan using zip_zips_index on zips z
(cost=0.00..1272.69 rows=516 width=52) (actual time=151.179..158.375
rows=718 loops=1)
                           Filter: ((state)::text = 'WA'::text)
                     ->  Index Scan using zip_quotes_index on quotes q
(cost=0.00..195.55 rows=48 width=27) (actual time=0.042..0.455 rows=14
loops=718)
                           Index Cond: (("outer".zip)::text = (q.zip)::text)
         ->  Index Scan using cars_car_id_btree_index on cars c
(cost=0.00..506.87 rows=9401 width=37) (actual time=0.213..47.307
rows=12019 loops=1)
 Total runtime: 1218.459 ms


Anyway, thanks for the attention to this issue. And I hope that this
helps some.

Jared