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




---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster

Reply via email to