Hi buddies,
I've got a query as below, it runs several times with different execution plan
and totally different execution time. The one using hash-join is slow and the
one using semi-hash join is very fast. However, I have no control over the
optimizer behavior of PostgreSQL database. Or, do I have?
The database version is 9.3.4
SELECT dem_type,
dem_value,
Count(*)
FROM demo_weekly a
WHERE date = '2013-11-30'
AND userid IN ( select userid from test1)
AND dem_type IN ( 'Gender', 'Age', 'Hobbies' )
GROUP BY dem_type,
dem_value ;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=322386.94..322786.94 rows=40000 width=29) (actual
time=3142.849..3142.927 rows=19 loops=1)
-> Hash Semi Join (cost=14460.06..314403.08 rows=1064514 width=29) (actual
time=803.671..2786.979 rows=1199961 loops=1)
Hash Cond: ((a.userid)::text = (test1.userid)::text)
-> Append (cost=0.00..277721.30 rows=2129027 width=78) (actual
time=536.829..1691.270 rows=2102611 loops=1)
-> Seq Scan on demo_weekly a (cost=0.00..0.00 rows=1
width=808) (actual time=0.002..0.002 rows=0 loops=1)
Filter: ((date = '2013-11-30'::date) AND ((dem_type)::text
= ANY ('{Gender,Age,"Hobbies"}'::text[])))
-> Bitmap Heap Scan on demo_weekly_20131130 a_1
(cost=50045.63..277721.30 rows=2129026 width=78) (actual time=536.826..1552.203
rows=2102611 loops=1)
Recheck Cond: ((dem_type)::text = ANY
('{Gender,Age,"Hobbies"}'::text[]))
Filter: (date = '2013-11-30'::date)
-> Bitmap Index Scan on demo_weekly_20131130_dt_idx
(cost=0.00..49513.37 rows=2129026 width=0) (actual time=467.453..467.453
rows=2102611 loops=1)
Index Cond: ((dem_type)::text = ANY
('{Gender,Age,"Hobbies"}'::text[]))
-> Hash (cost=8938.36..8938.36 rows=441736 width=50) (actual
time=266.501..266.501 rows=441736 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 35541kB
-> Seq Scan on test1 (cost=0.00..8938.36 rows=441736 width=50)
(actual time=0.023..87.869 rows=441736 loops=1)
Total runtime: 3149.004 ms
(15 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=318351.90..318751.90 rows=40000 width=29) (actual
time=23668.646..23668.723 rows=19 loops=1)
-> Hash Join (cost=5316.68..310497.81 rows=1047212 width=29) (actual
time=1059.182..23218.864 rows=1199961 loops=1)
Hash Cond: ((a.userid)::text = (test1.userid)::text)
-> Append (cost=0.00..276382.82 rows=2094423 width=78) (actual
time=528.116..2002.462 rows=2102611 loops=1)
-> Seq Scan on demo_weekly a (cost=0.00..0.00 rows=1
width=808) (actual time=0.001..0.001 rows=0 loops=1)
Filter: ((date = '2013-11-30'::date) AND ((dem_type)::text
= ANY ('{Gender,Age,"Hobbies"}'::text[])))
-> Bitmap Heap Scan on demo_weekly_20131130 a_1
(cost=49269.46..276382.82 rows=2094422 width=78) (actual time=528.114..1825.265
rows=2102611 loops=1)
Recheck Cond: ((dem_type)::text = ANY
('{Gender,Age,"Hobbies"}'::text[]))
Filter: (date = '2013-11-30'::date)
-> Bitmap Index Scan on demo_weekly_20131130_dt_idx
(cost=0.00..48745.85 rows=2094422 width=0) (actual time=458.694..458.694
rows=2102611 loops=1)
Index Cond: ((dem_type)::text = ANY
('{Gender,Age,"Hobbies"}'::text[]))
-> Hash (cost=5314.18..5314.18 rows=200 width=516) (actual
time=530.930..530.930 rows=441736 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 35541kB
-> HashAggregate (cost=5312.18..5314.18 rows=200 width=516)
(actual time=298.301..411.734 rows=441736 loops=1)
-> Seq Scan on test1 (cost=0.00..5153.94 rows=63294
width=516) (actual time=0.068..91.378 rows=441736 loops=1)
Total runtime: 23679.096 ms
(16 rows)
