Re: Partitioned tables constraint_exclusion

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.50 rows=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.50 rows=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.50 rows=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.61 rows=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=#