Re: Allow use of immutable functions operating on constants with constraint exclusion - Mailing list pgsql-hackers

ITAGAKI Takahiro wrote:

>Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>
>
>>"Marshall, Steve" <smarshall@wsi.com> writes:
>>
>>
>>>I have developed a small patch to optimizer/util/plancat.c that
>>>eliminates one of hte caveats associated with constraint exclusions,
>>>namely the inability to avoid searching tables based on the results of
>>>stable functions.
>>>
>>>
>>Do you not understand why this is completely unsafe?
>>
>>
>
>I think the proposal itself is very useful, because time-based
>partitioning is commonly used and functions like now() or
>CURRENT_TIMESTAMP are marked as stable.
>
>I'm not clear why the optimization is unsafe. I'm confused to read the
>definition of stable functions in our documentation. Which is required
>for stable functions 'stable in a single table scan' or 'stable in a
>SQL statements' ? If the latter definition is true, can we use them
>in constraint exclusions?
>
>| 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
>
>
The lack of safety comes from prepared statements.  If the above
optimization was used, the value of the stable function would be used
when the statement was prepared, and the query plan would then be set
using the stable function value as though it were a constant.  For
partitioned tables, this could result in a failure to scan tables needed
to meet the query constraints.

I think the optimization could work if you could exclude prepared
statements.  However, I looked at the planning code and found no clear
way to distinguish between a statement being prepared for later
execution, and a statement being planned only for immediate execution.
As a result, I don't think stable functions can (or should) be expanded
to help optimize queries using constraint exclusion.

However, I think it would be possible to expand immutable functions
operating on constants to optimize constraint exclusion.  Immutable
functions will always return the same result given the same inputs, so
this would be safe.  Currently, immutable functions are not expanded
during planning such that the first query would be optimized using
constraint exclusion, while the second query would not:

SELECT * FROM test_bulletins WHERE created_at > '2006-09-09
05:00:00+00'::timestamptz;
SELECT * FROM test_bulletins WHERE created_at > '2006-09-09
05:00:00+00'::timestamptz + '0 days'::interval;

See the attached SQL file for table creation and other SQL examples.

The real question here is if the optimization is worth the effort.
Personally, I commonly use queries of this sort, and so would be in
favor of this expansion of immutable functions operating on constrats as
an optimization.  I find it convenient to use the database to do the
time manipulation (e.g. adding intervals to timestamps).  However, the
logic to manipulate times can be pushed into application code if need
be.   I've found I have to do a lot of explaining to developers as to
why two queries that look so similar perform very differently.



--
--  Create and connect to the test database
--
CREATE DATABASE test_ce_db;
ALTER DATABASE test_ce_db OWNER TO postgres;

\connect test_ce_db;

--
--  Make a parent table and three child tables partitioned by time using created_at column.
--
CREATE TABLE test_bulletins (
    created_at timestamp with time zone NOT NULL,
    data text NOT NULL
);

CREATE TABLE test_bulletins_20060908 (CONSTRAINT time_partition_limits CHECK (((created_at >= '2006-09-08
00:00:00+00'::timestampwith time zone) AND (created_at < '2006-09-09 00:00:00+00'::timestamp with time zone))) 
)
INHERITS (test_bulletins);

CREATE TABLE test_bulletins_20060909 (CONSTRAINT time_partition_limits CHECK (((created_at >= '2006-09-09
00:00:00+00'::timestampwith time zone) AND (created_at < '2006-09-10 00:00:00+00'::timestamp with time zone))) 
)
INHERITS (test_bulletins);

CREATE TABLE test_bulletins_20060910 (CONSTRAINT time_partition_limits CHECK (((created_at >= '2006-09-10
00:00:00+00'::timestampwith time zone) AND (created_at < '2006-09-11 00:00:00+00'::timestamp with time zone))) 
)
INHERITS (test_bulletins);

--
--  Setup environment for queries.
--
SET constraint_exclusion = on;
\pset footer off;

--
--  Do test case queries.
--
SELECT 'This query should avoid use of table test_bulletins_20060908, even with OLD CE code' as "Test case 1";
EXPLAIN SELECT * FROM test_bulletins WHERE created_at > '2006-09-09 05:00:00+00'::timestamptz;

SELECT 'This query should avoid use of table test_bulletins_20060908, ONLY with NEW CE code' as "Test case 2";
EXPLAIN SELECT * FROM test_bulletins WHERE created_at > '2006-09-09 05:00:00+00'::timestamptz + '0 days'::interval;

SELECT 'This query uses a stable function; it should NOT be optimized' as "Test case 3";
EXPLAIN SELECT * FROM test_bulletins WHERE created_at > now() - (now() - '2006-09-09 05:00:00+00'::timestamptz);

SELECT 'This query uses a random value; it should NOT avoid use of table test_bulletins_20060908' as "Test case 4";
EXPLAIN SELECT * FROM test_bulletins WHERE created_at > '2006-09-09 05:00:00+00'::timestamptz - random() * '1
hour'::interval;  

--
SELECT 'Create a new tables for 20060907 and 20060911 reexecute test case 2' as "Test case 5";
CREATE TABLE test_bulletins_20060907 (CONSTRAINT time_partition_limits CHECK (((created_at >= '2006-09-07
00:00:00+00'::timestampwith time zone) AND (created_at < '2006-09-08 00:00:00+00'::timestamp with time zone))) 
)
INHERITS (test_bulletins);
CREATE TABLE test_bulletins_20060911 (CONSTRAINT time_partition_limits CHECK (((created_at >= '2006-09-11
00:00:00+00'::timestampwith time zone) AND (created_at < '2006-09-12 00:00:00+00'::timestamp with time zone))) 
)
INHERITS (test_bulletins);

EXPLAIN SELECT * FROM test_bulletins WHERE created_at > '2006-09-09 05:00:00+00'::timestamptz + '0 days'::interval;