Menu
I have a table partitioned by week. There is an index on start_time When I run this -
EXPLAIN SELECT COUNT(uniq_id) FROM call_fact WHERE CAST(start_time AS DATE) = '2018-01-23' AND source_key=2;
It scans every partitioned table rather than going to the correct child table.Shouldn't the query optimizer be smart enough to pick the right child table? Is there a way to force postgres to go to the correct child table?
EXPLAIN(analyze, buffers) SELECT COUNT(uniq_id) FROM call_fact WHERE CAST(start_time AS DATE) = '2018-01-23' AND source_key=2;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=649579.52..649579.53 rows=1 width=26) (actual time=845.998..845.998 rows=1 loops=1)
Buffers: shared hit=34839 read=1312
-> Append (cost=0.12..647969.68 rows=643937 width=26) (actual time=175.416..792.387 rows=457658 loops=1)
Buffers: shared hit=34839 read=1312
-> Index Scan using call_fact_cast_start_time_idx on call_fact (cost=0.12..8.14 rows=1 width=26) (actual time=0.006..0.006 rows=0 loops=1)
Index Cond: ((start_time)::date = '2018-01-23'::date)
Filter: (source_key = 2)
Buffers: shared hit=1
-> Bitmap Heap Scan on call_fact_2017w42 (cost=259.52..43804.31 rows=13604 width=26) (actual time=16.957..16.957 rows=0 loops=1)
Recheck Cond: ((start_time)::date = '2018-01-23'::date)
Filter: (source_key = 2)
Buffers: shared read=3
-> Bitmap Index Scan on call_fact_2017w42_cast_start_time_idx (cost=0.00..256.12 rows=13825 width=0) (actual time=16.954..16.954 rows=0 loops=1)
Index Cond: ((start_time)::date = '2018-01-23'::date)
Buffers: shared read=3
-> Bitmap Heap Scan on call_fact_2017w43 (cost=258.00..43186.24 rows=13425 width=26) (actual time=14.656..14.656 rows=0 loops=1)
Recheck Cond: ((start_time)::date = '2018-01-23'::date)
Filter: (source_key = 2)
Buffers: shared read=3
-> Bitmap Index Scan on call_fact_2017w43_cast_start_time_idx (cost=0.00..254.64 rows=13628 width=0) (actual time=14.653..14.653 rows=0 loops=1)
Index Cond: ((start_time)::date = '2018-01-23'::date)
Buffers: shared read=3
-> Bitmap Heap Scan on call_fact_2017w44 (cost=272.45..45811.42 rows=14264 width=26) (actual time=5.930..5.930 rows=0 loops=1)
Recheck Cond: ((start_time)::date = '2018-01-23'::date)
Filter: (source_key = 2)
Buffers: shared read=3
-> Bitmap Index Scan on call_fact_2017w44_cast_start_time_idx (cost=0.00..268.88 rows=14460 width=0) (actual time=5.921..5.921 rows=0 loops=1)
Index Cond: ((start_time)::date = '2018-01-23'::date)
Buffers: shared read=3
-> Bitmap Heap Scan on call_fact_2017w45 (cost=258.80..43538.05 rows=13534 width=26) (actual time=10.631..10.631 rows=0 loops=1)
Recheck Cond: ((start_time)::date = '2018-01-23'::date)
Filter: (source_key = 2)
Buffers: shared read=3
-> Bitmap Index Scan on call_fact_2017w45_cast_start_time_idx (cost=0.00..255.41 rows=13731 width=0) (actual time=10.630..10.630 rows=0 loops=1)
Index Cond: ((start_time)::date = '2018-01-23'::date)
Buffers: shared read=3
-> Bitmap Heap Scan on call_fact_2017w46 (cost=258.38..43396.98 rows=13479 width=26) (actual time=14.289..14.289 rows=0 loops=1)
Recheck Cond: ((start_time)::date = '2018-01-23'::date)
Filter: (source_key = 2)
Buffers: shared read=3
-> Bitmap Index Scan on call_fact_2017w46_cast_start_time_idx (cost=0.00..255.01 rows=13678 width=0) (actual time=14.281..14.281 rows=0 loops=1)
Index Cond: ((start_time)::date = '2018-01-23'::date)
Buffers: shared read=3
-> Bitmap Heap Scan on call_fact_2017w47 (cost=203.86..34194.57 rows=10556 width=26) (actual time=13.386..13.386 rows=0 loops=1)
Recheck Cond: ((start_time)::date = '2018-01-23'::date)
Filter: (source_key = 2)
Buffers: shared read=3
-> Bitmap Index Scan on call_fact_2017w47_cast_start_time_idx (cost=0.00..201.22 rows=10772 width=0) (actual time=13.384..13.384 rows=0 loops=1)
Index Cond: ((start_time)::date = '2018-01-23'::date)
Buffers: shared read=3
-> Bitmap Heap Scan on call_fact_2017w48 (cost=279.31..47044.41 rows=14662 width=26) (actual time=10.149..10.149 rows=0 loops=1)
Recheck Cond: ((start_time)::date = '2018-01-23'::date)
Filter: (source_key = 2)
Buffers: shared read=3
-> Bitmap Index Scan on call_fact_2017w48_cast_start_time_idx (cost=0.00..275.64 rows=14828 width=0) (actual time=10.143..10.143 rows=0 loops=1)
Index Cond: ((start_time)::date = '2018-01-23'::date)
Buffers: shared read=3
-> Bitmap Heap Scan on call_fact_2017w49 (cost=270.62..45091.94 rows=14064 width=26) (actual time=8.687..8.687 rows=0 loops=1)
Recheck Cond: ((start_time)::date = '2018-01-23'::date)
Filter: (source_key = 2)
Buffers: shared read=3
-> Bitmap Index Scan on call_fact_2017w49_cast_start_time_idx (cost=0.00..267.10 rows=14223 width=0) (actual time=8.684..8.684 rows=0 loops=1)
Index Cond: ((start_time)::date = '2018-01-23'::date)
Buffers: shared read=3
-> Bitmap Heap Scan on call_fact_2017w50 (cost=251.05..41960.30 rows=13073 width=26) (actual time=18.181..18.181 rows=0 loops=1)
Recheck Cond: ((start_time)::date = '2018-01-23'::date)
Filter: (source_key = 2)
Buffers: shared read=3
-> Bitmap Index Scan on call_fact_2017w50_cast_start_time_idx (cost=0.00..247.78 rows=13247 width=0) (actual time=18.175..18.175 rows=0 loops=1)
Index Cond: ((start_time)::date = '2018-01-23'::date)
Buffers: shared read=3
-> Bitmap Heap Scan on call_fact_2017w51 (cost=238.23..39983.46 rows=12434 width=26) (actual time=10.728..10.728 rows=0 loops=1)
Recheck Cond: ((start_time)::date = '2018-01-23'::date)
Filter: (source_key = 2)
Buffers: shared read=3
-> Bitmap Index Scan on call_fact_2017w51_cast_start_time_idx (cost=0.00..235.12 rows=12625 width=0) (actual time=10.726..10.726 rows=0 loops=1)
Index Cond: ((start_time)::date = '2018-01-23'::date)
Buffers: shared read=3
-> Bitmap Heap Scan on call_fact_2017w52 (cost=231.37..38822.25 rows=12069 width=26) (actual time=5.907..5.907 rows=0 loops=1)
Recheck Cond: ((start_time)::date = '2018-01-23'::date)
Filter: (source_key = 2)
Buffers: shared read=3
-> Bitmap Index Scan on call_fact_2017w52_cast_start_time_idx (cost=0.00..228.36 rows=12257 width=0) (actual time=5.905..5.905 rows=0 loops=1)
Index Cond: ((start_time)::date = '2018-01-23'::date)
Buffers: shared read=3
-> Bitmap Heap Scan on call_fact_2018w01 (cost=272.97..46008.85 rows=14313 width=26) (actual time=13.909..13.909 rows=0 loops=1)
Recheck Cond: ((start_time)::date = '2018-01-23'::date)
Filter: (source_key = 2)
Buffers: shared read=3
-> Bitmap Index Scan on call_fact_2018w01_cast_start_time_idx (cost=0.00..269.39 rows=14528 width=0) (actual time=13.908..13.908 rows=0 loops=1)
Index Cond: ((start_time)::date = '2018-01-23'::date)
Buffers: shared read=3
-> Bitmap Heap Scan on call_fact_2018w02 (cost=259.65..43842.27 rows=13642 width=26) (actual time=12.807..12.807 rows=0 loops=1)
Recheck Cond: ((start_time)::date = '2018-01-23'::date)
Filter: (source_key = 2)
Buffers: shared read=3
-> Bitmap Index Scan on call_fact_2018w02_cast_start_time_idx (cost=0.00..256.24 rows=13841 width=0) (actual time=12.806..12.806 rows=0 loops=1)
Index Cond: ((start_time)::date = '2018-01-23'::date)
Buffers: shared read=3
-> Bitmap Heap Scan on call_fact_2018w03 (cost=245.87..41477.94 rows=12905 width=26) (actual time=10.540..10.540 rows=0 loops=1)
Recheck Cond: ((start_time)::date = '2018-01-23'::date)
Filter: (source_key = 2)
Buffers: shared read=3
-> Bitmap Index Scan on call_fact_2018w03_cast_start_time_idx (cost=0.00..242.64 rows=13095 width=0) (actual time=10.538..10.538 rows=0 loops=1)
Index Cond: ((start_time)::date = '2018-01-23'::date)
Buffers: shared read=3
-> Index Scan using call_fact_2018w04_cast_start_time_idx on call_fact_2018w04 (cost=0.43..49688.01 rows=457898 width=26) (actual time=8.644..562.016 rows=457658 loops=1)
Index Cond: ((start_time)::date = '2018-01-23'::date)
Filter: (source_key = 2)
Rows Removed by Filter: 5782
Buffers: shared hit=34822 read=1270
-> Index Scan using call_fact_2018w05_cast_start_time_idx on call_fact_2018w05 (cost=0.42..4.44 rows=1 width=26) (actual time=0.039..0.039 rows=0 loops=1)
Index Cond: ((start_time)::date = '2018-01-23'::date)
Filter: (source_key = 2)
Buffers: shared hit=3
-> Index Scan using call_fact_2018w06_cast_start_time_idx on call_fact_2018w06 (cost=0.14..8.16 rows=1 width=98) (actual time=0.004..0.004 rows=0 loops=1)
Index Cond: ((start_time)::date = '2018-01-23'::date)
Filter: (source_key = 2)
Buffers: shared hit=1
-> Index Scan using call_fact_2018w07_cast_start_time_idx on call_fact_2018w07 (cost=0.14..8.16 rows=1 width=98) (actual time=0.003..0.003 rows=0 loops=1)
Index Cond: ((start_time)::date = '2018-01-23'::date)
Filter: (source_key = 2)
Buffers: shared hit=1
-> Index Scan using call_fact_2018w08_cast_start_time_idx on call_fact_2018w08 (cost=0.14..8.16 rows=1 width=98) (actual time=0.003..0.003 rows=0 loops=1)
Index Cond: ((start_time)::date = '2018-01-23'::date)
Filter: (source_key = 2)
Buffers: shared hit=1
-> Index Scan using call_fact_2018w09_cast_start_time_idx on call_fact_2018w09 (cost=0.14..8.16 rows=1 width=98) (actual time=0.003..0.003 rows=0 loops=1)
Index Cond: ((start_time)::date = '2018-01-23'::date)
Filter: (source_key = 2)
Buffers: shared hit=1
-> Index Scan using call_fact_2018w10_cast_start_time_idx on call_fact_2018w10 (cost=0.14..8.16 rows=1 width=98) (actual time=0.003..0.003 rows=0 loops=1)
Index Cond: ((start_time)::date = '2018-01-23'::date)
Filter: (source_key = 2)
Buffers: shared hit=1
-> Index Scan using call_fact_2018w11_cast_start_time_idx on call_fact_2018w11 (cost=0.14..8.16 rows=1 width=98) (actual time=0.003..0.003 rows=0 loops=1)
Index Cond: ((start_time)::date = '2018-01-23'::date)
Filter: (source_key = 2)
Buffers: shared hit=1
-> Index Scan using call_fact_2018w12_cast_start_time_idx on call_fact_2018w12 (cost=0.14..8.16 rows=1 width=98) (actual time=0.002..0.002 rows=0 loops=1)
Index Cond: ((start_time)::date = '2018-01-23'::date)
Filter: (source_key = 2)
Buffers: shared hit=1
-> Index Scan using call_fact_2018w13_cast_start_time_idx on call_fact_2018w13 (cost=0.14..8.16 rows=1 width=98) (actual time=0.003..0.003 rows=0 loops=1)
Index Cond: ((start_time)::date = '2018-01-23'::date)
Filter: (source_key = 2)
Buffers: shared hit=1
-> Index Scan using call_fact_2018w14_cast_start_time_idx on call_fact_2018w14 (cost=0.14..8.16 rows=1 width=98) (actual time=0.002..0.002 rows=0 loops=1)
Index Cond: ((start_time)::date = '2018-01-23'::date)
Filter: (source_key = 2)
Buffers: shared hit=1
-> Index Scan using call_fact_2018w15_source_key_idx on call_fact_2018w15 (cost=0.14..8.16 rows=1 width=98) (actual time=0.003..0.003 rows=0 loops=1)
Index Cond: (source_key = 2)
Filter: ((start_time)::date = '2018-01-23'::date)
Buffers: shared hit=1
-> Index Scan using call_fact_2018w16_source_key_idx on call_fact_2018w16 (cost=0.14..8.16 rows=1 width=98) (actual time=0.003..0.003 rows=0 loops=1)
Index Cond: (source_key = 2)
Filter: ((start_time)::date = '2018-01-23'::date)
Buffers: shared hit=1
-> Index Scan using call_fact_2018w17_cast_start_time_idx on call_fact_2018w17 (cost=0.14..8.16 rows=1 width=98) (actual time=0.004..0.004 rows=0 loops=1)
Index Cond: ((start_time)::date = '2018-01-23'::date)
Filter: (source_key = 2)
Buffers: shared hit=1
-> Index Scan using call_fact_2018w18_cast_start_time_idx on call_fact_2018w18 (cost=0.14..8.16 rows=1 width=98) (actual time=0.005..0.005 rows=0 loops=1)
Index Cond: ((start_time)::date = '2018-01-23'::date)
Filter: (source_key = 2)
Buffers: shared hit=1
Planning time: 68.906 ms
Execution time: 846.648 ms
675
What is the type of start_time? I assume it is timestamp, not a date.
The expression CAST(start_time AS DATE) prevents optimiser from using an index on start_time. Usually any kind of expression over the column prevents optimiser from using an index.
The common way to write this type of query is to compare original column value without wrapping it into expression with a closed-open interval.
SELECT COUNT(uniq_id)
FROM call_fact
WHERE
source_key=2
AND start_time >= '2018-01-23'
AND start_time < '2018-01-24'
;
Postgres should be smart enough to implicitly convert the constant expression 2018-01-23 of the date type into the timestamp type. (rather than converting the values of the start_time column into the date type. What type is converted into what type is governed by rules for the implicit type conversions. In SQL Server it is called the "type precedence", but I could not find a relevant page in the Postgres docs.
To make it explicit and not relying on type precedence and implicit rules I would explicitly write the date/time literal in such a way that it is of timestamp type that matches the type of the column.
SELECT COUNT(uniq_id)
FROM call_fact
WHERE
source_key=2
AND start_time >= '2018-01-23 00:00:00'
AND start_time < '2018-01-24 00:00:00'
;
When the query is written in this way nothing should stop optimiser from using the index on start_time.
88
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
