Re: Partitioned tables constraint_exclusion - Mailing list pgsql-hackers

Simon Riggs wrote:
> On Mon, 2007-03-26 at 09:38 -0700, Weslee Bilodeau wrote:
> 
>> mytest=# explain select count(*) from master where var_ts > (
>> '2007-03-26 16:03:27.370627+00'::timestamptz - '1 month'::interval
>> )::timestamptz ;
> 
> If you're able to supply a constant value, why not subtract 1 month
> before you submit the query?
> 
> AFAIK timestamptz arithmetic depends upon the current timezone which is
> a STABLE value and so won't currently work with partitioning.
> 
> Having partitioning work with STABLE functions should be a TODO item if
> it isn't already, but that requires some thought to implement and won't
> happen for 8.3.
> 

Mainly its because the value comes from a reporting system that has
minimal brains, it passes values it gets from the user directly into a
query.

IE, they enter '1 month', which I use to populate the interval value,
"ts > ( NOW() - $VALUE )"

But, in the example I did a "timestamp - interval", the exact date, not
NOW() - Still didn't work.

I'm guessing anything that has to think, math, etc is not valid for
constrain_exclusion?

Its not in the docs anywhere, so trying to isolate what can and can't be
done.

Weslee

mytest=# explain select count(*) from master where var_ts > (
'2007-03-26 16:03:27.370627+00'::timestamptz + '1 second'::interval
)::timestamptz ;                                                         QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------Aggregate
(cost=105.77..105.78 rows=1 width=0)  ->  Append  (cost=7.10..102.10 rows=1468 width=0)        ->  Bitmap Heap Scan on
master (cost=7.10..22.60 rows=367 width=0)              Recheck Cond: (var_ts > ('2007-03-26
 
16:03:27.370627+00'::timestamp with time zone + '00:00:01'::interval))              ->  Bitmap Index Scan on
master_var_ts_key
(cost=0.00..7.01 rows=367 width=0)                    Index Cond: (var_ts > ('2007-03-26
16:03:27.370627+00'::timestamp with time zone + '00:00:01'::interval))        ->  Seq Scan on kid_200601 master
(cost=0.00..26.50rows=367
 
width=0)              Filter: (var_ts > ('2007-03-26
16:03:27.370627+00'::timestamp with time zone + '00:00:01'::interval))        ->  Seq Scan on kid_200602 master
(cost=0.00..26.50rows=367
 
width=0)              Filter: (var_ts > ('2007-03-26
16:03:27.370627+00'::timestamp with time zone + '00:00:01'::interval))        ->  Seq Scan on kid_200603 master
(cost=0.00..26.50rows=367
 
width=0)              Filter: (var_ts > ('2007-03-26
16:03:27.370627+00'::timestamp with time zone + '00:00:01'::interval))
(12 rows)

mytest=# explain select count(*) from master where var_ts > (
'2007-03-26 16:03:27.370627+00' ) ;                                             QUERY PLAN
------------------------------------------------------------------------------------------------------Aggregate
(cost=22.60..22.61rows=1 width=0)  ->  Append  (cost=7.09..21.68 rows=367 width=0)        ->  Bitmap Heap Scan on
master (cost=7.09..21.68 rows=367 width=0)              Recheck Cond: (var_ts > '2007-03-26
 
16:03:27.370627+00'::timestamp with time zone)              ->  Bitmap Index Scan on master_var_ts_key
(cost=0.00..7.00 rows=367 width=0)                    Index Cond: (var_ts > '2007-03-26
16:03:27.370627+00'::timestamp with time zone)
(6 rows)

mytest=#