Postgres Section - Performance Issue

Question

I have a table broken down by weeks. There is an index in start_time. When I ran this -

EXPLAIN SELECT COUNT(uniq_id) FROM call_fact WHERE CAST(start_time AS DATE) = '2018-01-23' AND source_key=2;

It scans each partitioned table, and does not fit the correct child table. Can the query optimizer be smart enough to choose the right child table? Is there a way to get postgres to switch to the correct children’s 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

Answer 1

What is the type start_time? I guess it is timestamp, not date.

The expression CAST(start_time AS DATE)does not allow the optimizer to use the index on start_time. Usually, any expression above a column does not allow the optimizer to use the index.

.

SELECT COUNT(uniq_id) 
FROM call_fact 
WHERE 
    source_key=2
    AND start_time >= '2018-01-23'
    AND start_time <  '2018-01-24'
;

Postgres , 2018-01-23 date timestamp. ( start_time date. , . SQL Server " ", Postgres.

, / , timestamp, .

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'
;

, start_time.

Answer 2

. .

- :

PREPARE query(timestamp) as 
    select max(b)
    from  customer
    where b > $1 - interval '1 months';     
   EXPLAIN ANALYZE EXECUTE query(current_date);
   -- You should deallocate your statement when you are done using it.
   DEALLOCATE query;

PREPARE https://www.postgresql.org/docs/current/static/sql-prepare.html