pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedConstraint exclusion can't process simple constant expressions?
11+ messages / 4 participants
[nested] [flat]
* Constraint exclusion can't process simple constant expressions?
@ 2011-04-20 23:49 Josh Berkus <josh@agliodbs.com>
2011-04-21 00:48 ` Re: Constraint exclusion can't process simple constant expressions? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Josh Berkus @ 2011-04-20 23:49 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Constraint exclusion can't process simple constant expressions?
2011-04-20 23:49 Constraint exclusion can't process simple constant expressions? Josh Berkus <josh@agliodbs.com>
@ 2011-04-21 00:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-04-21 01:58 ` Re: Constraint exclusion can't process simple constant expressions? Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2011-04-21 00:48 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Constraint exclusion can't process simple constant expressions?
2011-04-20 23:49 Constraint exclusion can't process simple constant expressions? Josh Berkus <josh@agliodbs.com>
2011-04-21 00:48 ` Re: Constraint exclusion can't process simple constant expressions? Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-04-21 01:58 ` Josh Berkus <josh@agliodbs.com>
2011-04-21 02:05 ` Re: Constraint exclusion can't process simple constant expressions? Brendan Jurd <direvus@gmail.com>
2011-04-21 02:44 ` Re: Constraint exclusion can't process simple constant expressions? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 11+ messages in thread
From: Josh Berkus @ 2011-04-21 01:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Constraint exclusion can't process simple constant expressions?
2011-04-20 23:49 Constraint exclusion can't process simple constant expressions? Josh Berkus <josh@agliodbs.com>
2011-04-21 00:48 ` Re: Constraint exclusion can't process simple constant expressions? Tom Lane <tgl@sss.pgh.pa.us>
2011-04-21 01:58 ` Re: Constraint exclusion can't process simple constant expressions? Josh Berkus <josh@agliodbs.com>
@ 2011-04-21 02:05 ` Brendan Jurd <direvus@gmail.com>
2011-04-21 02:13 ` Re: Constraint exclusion can't process simple constant expressions? Josh Berkus <josh@agliodbs.com>
2011-04-21 07:30 ` Re: Constraint exclusion can't process simple constant expressions? Claudio Freire <klaussfreire@gmail.com>
1 sibling, 2 replies; 11+ messages in thread
From: Brendan Jurd @ 2011-04-21 02:05 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Constraint exclusion can't process simple constant expressions?
2011-04-20 23:49 Constraint exclusion can't process simple constant expressions? Josh Berkus <josh@agliodbs.com>
2011-04-21 00:48 ` Re: Constraint exclusion can't process simple constant expressions? Tom Lane <tgl@sss.pgh.pa.us>
2011-04-21 01:58 ` Re: Constraint exclusion can't process simple constant expressions? Josh Berkus <josh@agliodbs.com>
2011-04-21 02:05 ` Re: Constraint exclusion can't process simple constant expressions? Brendan Jurd <direvus@gmail.com>
@ 2011-04-21 02:13 ` Josh Berkus <josh@agliodbs.com>
2011-04-21 02:17 ` Re: Constraint exclusion can't process simple constant expressions? Brendan Jurd <direvus@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Josh Berkus @ 2011-04-21 02:13 UTC (permalink / raw)
To: Brendan Jurd <direvus@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Constraint exclusion can't process simple constant expressions?
2011-04-20 23:49 Constraint exclusion can't process simple constant expressions? Josh Berkus <josh@agliodbs.com>
2011-04-21 00:48 ` Re: Constraint exclusion can't process simple constant expressions? Tom Lane <tgl@sss.pgh.pa.us>
2011-04-21 01:58 ` Re: Constraint exclusion can't process simple constant expressions? Josh Berkus <josh@agliodbs.com>
2011-04-21 02:05 ` Re: Constraint exclusion can't process simple constant expressions? Brendan Jurd <direvus@gmail.com>
2011-04-21 02:13 ` Re: Constraint exclusion can't process simple constant expressions? Josh Berkus <josh@agliodbs.com>
@ 2011-04-21 02:17 ` Brendan Jurd <direvus@gmail.com>
2011-04-21 02:18 ` Re: Constraint exclusion can't process simple constant expressions? Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 11+ messages in thread
From: Brendan Jurd @ 2011-04-21 02:17 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Constraint exclusion can't process simple constant expressions?
2011-04-20 23:49 Constraint exclusion can't process simple constant expressions? Josh Berkus <josh@agliodbs.com>
2011-04-21 00:48 ` Re: Constraint exclusion can't process simple constant expressions? Tom Lane <tgl@sss.pgh.pa.us>
2011-04-21 01:58 ` Re: Constraint exclusion can't process simple constant expressions? Josh Berkus <josh@agliodbs.com>
2011-04-21 02:05 ` Re: Constraint exclusion can't process simple constant expressions? Brendan Jurd <direvus@gmail.com>
2011-04-21 02:13 ` Re: Constraint exclusion can't process simple constant expressions? Josh Berkus <josh@agliodbs.com>
2011-04-21 02:17 ` Re: Constraint exclusion can't process simple constant expressions? Brendan Jurd <direvus@gmail.com>
@ 2011-04-21 02:18 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 11+ messages in thread
From: Josh Berkus @ 2011-04-21 02:18 UTC (permalink / raw)
To: Brendan Jurd <direvus@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Constraint exclusion can't process simple constant expressions?
2011-04-20 23:49 Constraint exclusion can't process simple constant expressions? Josh Berkus <josh@agliodbs.com>
2011-04-21 00:48 ` Re: Constraint exclusion can't process simple constant expressions? Tom Lane <tgl@sss.pgh.pa.us>
2011-04-21 01:58 ` Re: Constraint exclusion can't process simple constant expressions? Josh Berkus <josh@agliodbs.com>
2011-04-21 02:05 ` Re: Constraint exclusion can't process simple constant expressions? Brendan Jurd <direvus@gmail.com>
@ 2011-04-21 07:30 ` Claudio Freire <klaussfreire@gmail.com>
2011-04-21 07:34 ` Re: Constraint exclusion can't process simple constant expressions? Claudio Freire <klaussfreire@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Claudio Freire @ 2011-04-21 07:30 UTC (permalink / raw)
To: Brendan Jurd <direvus@gmail.com>; +Cc: Josh Berkus <josh@agliodbs.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Constraint exclusion can't process simple constant expressions?
2011-04-20 23:49 Constraint exclusion can't process simple constant expressions? Josh Berkus <josh@agliodbs.com>
2011-04-21 00:48 ` Re: Constraint exclusion can't process simple constant expressions? Tom Lane <tgl@sss.pgh.pa.us>
2011-04-21 01:58 ` Re: Constraint exclusion can't process simple constant expressions? Josh Berkus <josh@agliodbs.com>
2011-04-21 02:05 ` Re: Constraint exclusion can't process simple constant expressions? Brendan Jurd <direvus@gmail.com>
2011-04-21 07:30 ` Re: Constraint exclusion can't process simple constant expressions? Claudio Freire <klaussfreire@gmail.com>
@ 2011-04-21 07:34 ` Claudio Freire <klaussfreire@gmail.com>
2011-04-21 17:36 ` Re: Constraint exclusion can't process simple constant expressions? Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 11+ messages in thread
From: Claudio Freire @ 2011-04-21 07:34 UTC (permalink / raw)
To: Brendan Jurd <direvus@gmail.com>; +Cc: Josh Berkus <josh@agliodbs.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
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?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Constraint exclusion can't process simple constant expressions?
2011-04-20 23:49 Constraint exclusion can't process simple constant expressions? Josh Berkus <josh@agliodbs.com>
2011-04-21 00:48 ` Re: Constraint exclusion can't process simple constant expressions? Tom Lane <tgl@sss.pgh.pa.us>
2011-04-21 01:58 ` Re: Constraint exclusion can't process simple constant expressions? Josh Berkus <josh@agliodbs.com>
2011-04-21 02:05 ` Re: Constraint exclusion can't process simple constant expressions? Brendan Jurd <direvus@gmail.com>
2011-04-21 07:30 ` Re: Constraint exclusion can't process simple constant expressions? Claudio Freire <klaussfreire@gmail.com>
2011-04-21 07:34 ` Re: Constraint exclusion can't process simple constant expressions? Claudio Freire <klaussfreire@gmail.com>
@ 2011-04-21 17:36 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 11+ messages in thread
From: Josh Berkus @ 2011-04-21 17:36 UTC (permalink / raw)
To: Claudio Freire <klaussfreire@gmail.com>; +Cc: Brendan Jurd <direvus@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Constraint exclusion can't process simple constant expressions?
2011-04-20 23:49 Constraint exclusion can't process simple constant expressions? Josh Berkus <josh@agliodbs.com>
2011-04-21 00:48 ` Re: Constraint exclusion can't process simple constant expressions? Tom Lane <tgl@sss.pgh.pa.us>
2011-04-21 01:58 ` Re: Constraint exclusion can't process simple constant expressions? Josh Berkus <josh@agliodbs.com>
@ 2011-04-21 02:44 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 11+ messages in thread
From: Tom Lane @ 2011-04-21 02:44 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2011-04-21 17:36 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-04-20 23:49 Constraint exclusion can't process simple constant expressions? Josh Berkus <josh@agliodbs.com>
2011-04-21 00:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-04-21 01:58 ` Josh Berkus <josh@agliodbs.com>
2011-04-21 02:05 ` Brendan Jurd <direvus@gmail.com>
2011-04-21 02:13 ` Josh Berkus <josh@agliodbs.com>
2011-04-21 02:17 ` Brendan Jurd <direvus@gmail.com>
2011-04-21 02:18 ` Josh Berkus <josh@agliodbs.com>
2011-04-21 07:30 ` Claudio Freire <klaussfreire@gmail.com>
2011-04-21 07:34 ` Claudio Freire <klaussfreire@gmail.com>
2011-04-21 17:36 ` Josh Berkus <josh@agliodbs.com>
2011-04-21 02:44 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox