Partition pruning is not effective (when using now(), current_time, current_date etc)

[killua001]

base info

version 1.3.1, pg 9.5.5, rhel 6.

partition table tbl_user_access_rt, partition key: statis_date , 1day per partion.

Check constraints:
    "pathman_tbl_user_access_rt_20160811_4_check" CHECK (statis_date >= '2016-08-11 00:00:00'::timestamp without time zone AND statis_date < '2016-08-12 00:00:00'::timestamp without time zone)
Inherits: tbl_user_access_rt

sql text

SELECT statis_date
FROM tbl_user_access_rt
WHERE appid = 'E4F5750CF8504252C93993F71D86EF39' AND device_type in ('W')
and statis_date > current_date - 2
GROUP BY statis_date
ORDER BY statis_date DESC
LIMIT 2;

sql plan

 Limit  (cost=0.56..2544.27 rows=2 width=8) (actual time=164.982..164.982 rows=0 loops=1)
   ->  Group  (cost=0.56..254371.75 rows=200 width=8) (actual time=164.980..164.980 rows=0 loops=1)
         Group Key: tbl_user_access_rt_20170526.statis_date
         ->  Append  (cost=0.56..254371.02 rows=290 width=8) (actual time=164.980..164.980 rows=0 loops=1)
               ->  Index Scan Backward using tbl_user_access_rt_290_appid_statis_date_idx on tbl_user_access_rt_20170526  (cost=0.56..41543.23 rows=1 width=8) (actual time=26.398..26.398 ro
ws=0 loops=1)
                     Index Cond: (((appid)::text = 'E4F5750CF8504252C93993F71D86EF39'::text) AND (statis_date > (('now'::cstring)::date - 2)))
                     Filter: ((device_type)::text = 'W'::text)
                     Rows Removed by Filter: 17064
               ->  Index Scan Backward using tbl_user_access_rt_289_appid_statis_date_idx on tbl_user_access_rt_20170525  (cost=0.56..115856.15 rows=1 width=8) (actual time=70.193..70.193 r
ows=0 loops=1)
                     Index Cond: (((appid)::text = 'E4F5750CF8504252C93993F71D86EF39'::text) AND (statis_date > (('now'::cstring)::date - 2)))
                     Filter: ((device_type)::text = 'W'::text)
                     Rows Removed by Filter: 44489
               ->  Index Scan Backward using tbl_user_access_rt_288_appid_statis_date_idx on tbl_user_access_rt_20170524  (cost=0.56..96074.94 rows=1 width=8) (actual time=63.910..63.910 ro
ws=0 loops=1)
                     Index Cond: (((appid)::text = 'E4F5750CF8504252C93993F71D86EF39'::text) AND (statis_date > (('now'::cstring)::date - 2)))
                     Filter: ((device_type)::text = 'W'::text)
                     Rows Removed by Filter: 42511
               ->  Index Scan Backward using tbl_user_access_rt_287_statis_date_idx on tbl_user_access_rt_20170523  (cost=0.44..2.96 rows=1 width=8) (actual time=0.043..0.043 rows=0 loops=1
)
                     Index Cond: (statis_date > (('now'::cstring)::date - 2))
                     Filter: (((appid)::text = 'E4F5750CF8504252C93993F71D86EF39'::text) AND ((device_type)::text = 'W'::text))
               ->  Index Scan Backward using tbl_user_access_rt_286_statis_date_idx on tbl_user_access_rt_20170522  (cost=0.44..2.96 rows=1 width=8) (actual time=0.017..0.017 rows=0 loops=1
)
                     Index Cond: (statis_date > (('now'::cstring)::date - 2))
                     Filter: (((appid)::text = 'E4F5750CF8504252C93993F71D86EF39'::text) AND ((device_type)::text = 'W'::text))
               ->  Index Scan Backward using tbl_user_access_rt_285_statis_date_idx on tbl_user_access_rt_20170521  (cost=0.44..2.96 rows=1 width=8) (actual time=0.013..0.013 rows=0 loops=1
)
                     Index Cond: (statis_date > (('now'::cstring)::date - 2))
                     Filter: (((appid)::text = 'E4F5750CF8504252C93993F71D86EF39'::text) AND ((device_type)::text = 'W'::text))
               ->  Index Scan Backward using tbl_user_access_rt_284_statis_date_idx on tbl_user_access_rt_20170520  (cost=0.44..2.96 rows=1 width=8) (actual time=0.010..0.010 rows=0 loops=1
)
                     Index Cond: (statis_date > (('now'::cstring)::date - 2))
                     Filter: (((appid)::text = 'E4F5750CF8504252C93993F71D86EF39'::text) AND ((device_type)::text = 'W'::text))
               ->  Index Scan Backward using tbl_user_access_rt_283_statis_date_idx on tbl_user_access_rt_20170519  (cost=0.44..2.96 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=1
)
                     Index Cond: (statis_date > (('now'::cstring)::date - 2))
                     Filter: (((appid)::text = 'E4F5750CF8504252C93993F71D86EF39'::text) AND ((device_type)::text = 'W'::text))
               ->  Index Scan Backward using tbl_user_access_rt_282_statis_date_idx on tbl_user_access_rt_20170518  (cost=0.44..2.96 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=1
)
                     Index Cond: (statis_date > (('now'::cstring)::date - 2))
                     Filter: (((appid)::text = 'E4F5750CF8504252C93993F71D86EF39'::text) AND ((device_type)::text = 'W'::text))
               ->  Index Scan Backward using tbl_user_access_rt_281_statis_date_idx on tbl_user_access_rt_20170517  (cost=0.44..2.96 rows=1 width=8) (actual time=0.010..0.010 rows=0 loops=1
)
                     Index Cond: (statis_date > (('now'::cstring)::date - 2))
                     Filter: (((appid)::text = 'E4F5750CF8504252C93993F71D86EF39'::text) AND ((device_type)::text = 'W'::text))
               ->  Index Scan Backward using tbl_user_access_rt_280_statis_date_idx on tbl_user_access_rt_20170516  (cost=0.44..2.96 rows=1 width=8) (actual time=0.013..0.013 rows=0 loops=1
)
                     Index Cond: (statis_date > (('now'::cstring)::date - 2))
                     Filter: (((appid)::text = 'E4F5750CF8504252C93993F71D86EF39'::text) AND ((device_type)::text = 'W'::text))
               ->  Index Scan Backward using tbl_user_access_rt_279_statis_date_idx on tbl_user_access_rt_20170515  (cost=0.44..2.96 rows=1 width=8) (actual time=0.015..0.015 rows=0 loops=1
)
                     Index Cond: (statis_date > (('now'::cstring)::date - 2))
                     Filter: (((appid)::text = 'E4F5750CF8504252C93993F71D86EF39'::text) AND ((device_type)::text = 'W'::text))
               ->  Index Scan Backward using tbl_user_access_rt_278_statis_date_idx on tbl_user_access_rt_20170514  (cost=0.44..2.96 rows=1 width=8) (actual time=0.015..0.015 rows=0 loops=1
)
                     Index Cond: (statis_date > (('now'::cstring)::date - 2))
                     Filter: (((appid)::text = 'E4F5750CF8504252C93993F71D86EF39'::text) AND ((device_type)::text = 'W'::text))
               ->  Index Scan Backward using tbl_user_access_rt_277_statis_date_idx on tbl_user_access_rt_20170513  (cost=0.44..2.96 rows=1 width=8) (actual time=0.016..0.016 rows=0 loops=1
)
                     Index Cond: (statis_date > (('now'::cstring)::date - 2))
                     Filter: (((appid)::text = 'E4F5750CF8504252C93993F71D86EF39'::text) AND ((device_type)::text = 'W'::text))

.....

[funbringer]

Hello @killua001,

I'm sorry, but you didn't provide any description to your issue. I don't see any problem here.

If you're trying to say that current_date should be optimized, see issue #66 for a comprehensive answer.

[funbringer]

If you'd like to prune partitions by function's value, you should use prepared statements:

create table test (val date not null);

select create_range_partitions('test',
                               'val',
                               current_date - '5 days'::interval, /* start date */
                               '1 day'::interval,
                               10); /* number of partitions */

/* this allows us to use RuntimeAppend */
prepare q (date) as select * from test where val < $1;

/* eventually (6th attempt) you'll see something like this */
explain (analyze, costs off) execute q(current_date);
                              QUERY PLAN
-----------------------------------------------------------------------
 Custom Scan (RuntimeAppend) (actual time=0.125..0.125 rows=0 loops=1)
   Prune by: (test.val < $1)
   ->  Seq Scan on test_1 (actual time=0.010..0.010 rows=0 loops=1)
         Filter: (val < $1)
   ->  Seq Scan on test_2 (actual time=0.002..0.002 rows=0 loops=1)
         Filter: (val < $1)
   ->  Seq Scan on test_3 (actual time=0.003..0.003 rows=0 loops=1)
         Filter: (val < $1)
   ->  Seq Scan on test_4 (actual time=0.002..0.002 rows=0 loops=1)
         Filter: (val < $1)
   ->  Seq Scan on test_5 (actual time=0.002..0.002 rows=0 loops=1)
         Filter: (val < $1)
 Execution time: 0.212 ms
(13 rows)

As you can see, RuntimeAppend selected only 5 partitions (instead of 10).

---

On a side note, this feature is called partition pruning, not PartitionFilter (which is a custom node for INSERTs).

[killua001]

Hi @funbringer
Got it, Thank you.