Re: [HACKERS] Partitioned tables constraint_exclusion

2007-04-09 Thread Jim Nasby
See Simon's reply... timestamptz math is *not* IMMUTABLE, because sessions are free to change their timezone at any time. I bet you can get some invalid results using that function with a clever test case. On Mar 26, 2007, at 3:48 PM, Weslee Bilodeau wrote: Weslee Bilodeau wrote: Mainly

Re: [HACKERS] Partitioned tables constraint_exclusion

2007-04-09 Thread Weslee Bilodeau
Jim Nasby wrote: See Simon's reply... timestamptz math is *not* IMMUTABLE, because sessions are free to change their timezone at any time. I bet you can get some invalid results using that function with a clever test case. I'm pretty sure it could easily be broken. But to make it easier for

[HACKERS] Partitioned tables constraint_exclusion

2007-03-26 Thread Weslee Bilodeau
I'm not sure if this is a bug, missing feature, misunderstanding on my part? I checked the TODO list and couldn't find anything on it. I currently have a 750 million row table, indexes are 10 GB, so trying to partition it. The basic - constraint_exclusion + exact match = OK

Re: [HACKERS] Partitioned tables constraint_exclusion

2007-03-26 Thread Simon Riggs
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

Re: [HACKERS] Partitioned tables constraint_exclusion

2007-03-26 Thread Weslee Bilodeau
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

Re: [HACKERS] Partitioned tables constraint_exclusion

2007-03-26 Thread Weslee Bilodeau
Weslee Bilodeau wrote: 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