Обсуждение: Constraint exclusion can't process simple constant expressions?
All,
Apparently our CE is unable to deal with even moderately complex
expressions. For example, given a CE check constraint of:
"chk_start" CHECK (start >= '2011-01-31 00:00:00-05'::timestamp with
time zone AND start < '2011-03-01 00:00:00-05'::timestamp with time zone)
PostgreSQL CE is unable to figure out not to scan this partition for a
query which contains the following filter condition:
WHERE start >= '2010-11-01'::timestamptz
AND start < ('2010-11-30'::timestamptz + '1
day'::interval)::timestamptz
Even though it can figure out this one:
WHERE call_start >= '2010-11-01'::timestamptz
AND call_start < '2010-12-01'::timestamptz
I understand why now() is a problem for CE, but I'd expect that it could
at least handle a simple expression with immutable outputs.
We need a new form of partitioning ...
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
Josh Berkus <josh@agliodbs.com> writes:
> I understand why now() is a problem for CE, but I'd expect that it could
> at least handle a simple expression with immutable outputs.
timestamptz + interval is not immutable --- in fact, the particular
example you give (ts + '1 day') is certainly dependent on timezone
setting.
regards, tom lane
Tom, > timestamptz + interval is not immutable --- in fact, the particular > example you give (ts + '1 day') is certainly dependent on timezone > setting. Why not? Given that the time zone will be the same for both the timestamptz and the interval, how would the result not be immutable? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On 21 April 2011 11:58, Josh Berkus <josh@agliodbs.com> wrote: >> timestamptz + interval is not immutable --- in fact, the particular >> example you give (ts + '1 day') is certainly dependent on timezone >> setting. > > Why not? Given that the time zone will be the same for both the > timestamptz and the interval, how would the result not be immutable? > "IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values" Emphasis on "always". If the result of the function, given the same argument values, can be different after a SET, then it doesn't qualify for immutability. At least, that's my understanding. Cheers, BJ
> Emphasis on "always". If the result of the function, given the same > argument values, can be different after a SET, then it doesn't qualify > for immutability. At least, that's my understanding. Hmmmm. But within the context of the query plan itself, the results of that expression are going to be constant. That is, for a given query execution, it's always going to be the same comparison. So this goes back to my original assertion that CE can't be fixed ... -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On 21 April 2011 12:13, Josh Berkus <josh@agliodbs.com> wrote: >> Emphasis on "always". If the result of the function, given the same >> argument values, can be different after a SET, then it doesn't qualify >> for immutability. At least, that's my understanding. > > Hmmmm. But within the context of the query plan itself, the results of > that expression are going to be constant. That is, for a given query > execution, it's always going to be the same comparison. > You may be thinking of the STABLE volatility level. It requires that the results of the function are the same for the same inputs, within the same transaction. "STABLE indicates that the function cannot modify the database, and that within a single table scan it will consistently return the same result for the same argument values, but that its result could change across SQL statements. This is the appropriate selection for functions whose results depend on database lookups, parameter variables (such as the current time zone), etc." Cheers, BJ
> You may be thinking of the STABLE volatility level. It requires that > the results of the function are the same for the same inputs, within > the same transaction. Right. But CE will only pay attention to immutable values, not stable ones, AFAICT. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
Josh Berkus <josh@agliodbs.com> writes:
>> timestamptz + interval is not immutable --- in fact, the particular
>> example you give (ts + '1 day') is certainly dependent on timezone
>> setting.
> Why not? Given that the time zone will be the same for both the
> timestamptz and the interval, how would the result not be immutable?
The reason it depends on the timezone is that the result varies if
"plus one day" means crossing a DST boundary.
regards, tom lane
On Thu, Apr 21, 2011 at 4:05 AM, Brendan Jurd <direvus@gmail.com> wrote: > > "IMMUTABLE indicates that the function cannot modify the database and > always returns the same result when given the same argument values" > > Emphasis on "always". If the result of the function, given the same > argument values, can be different after a SET, then it doesn't qualify > for immutability. At least, that's my understanding. That's a ridiculous use of the word "Immutable" In any CS class, the timezone would be an implicit input to the function. So it would be immutable in *that* sense (it also takes timezone into consideration). Perhaps the optimizer should take contextual information that cannot change inside a query as input too.
On Thu, Apr 21, 2011 at 9:30 AM, Claudio Freire <klaussfreire@gmail.com> wrote: > On Thu, Apr 21, 2011 at 4:05 AM, Brendan Jurd <direvus@gmail.com> wrote: >> >> "IMMUTABLE indicates that the function cannot modify the database and >> always returns the same result when given the same argument values" >> >> Emphasis on "always". If the result of the function, given the same >> argument values, can be different after a SET, then it doesn't qualify >> for immutability. At least, that's my understanding. > > That's a ridiculous use of the word "Immutable" > > In any CS class, the timezone would be an implicit input to the > function. So it would be immutable in *that* sense (it also takes > timezone into consideration). > > Perhaps the optimizer should take contextual information that cannot > change inside a query as input too. > In any case, the point is that the CE check (which is what CE cares about) is indeed immutable in the PG sense. If it is instantiated with a STABLE expression, it would still be equivalent to IMMUTABLE within the transaction - which is what CE cares about. Am I missing something?
Claudio, > Am I missing something? Yes, prepared statements. This whole issue arises because CE is implemented purely on the planner level. The executor can treat Immutable and Stable functions as the same; the planner cannot, AFAIK. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com