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=# ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match