Allow use of stable functions with constraint exclusion

From: "Marshall, Steve" <smarshall(at)wsi(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Allow use of stable functions with constraint exclusion
Date: 2007-04-30 21:55:04
Message-ID: 463665B8.5050501@wsi.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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. The new code expands non-volatile functions into
constant values so they can be used in the plan to determine which
tables to search. If volatile functions are used in the query
constraints, they are correctly ignored in the planning process.

This is particularly useful for tables partitioned on a time column
where queries relative to the current system time are desirable.

I'll attach the patch file and a small SQL test case that exercises the
code. The patch is relative to the 8.2.4 release; the SQL uses
backslash commands, so its really only appropriate for use with the psql
utility. However, it does create a test database called test_ce_db and
run all the tests with a single command, e.g. psql -f test_ce_patch.sql

I'd appreciate any feedback anyone has, particularly on any corner cases
I may have missed.

Thanks,
Steve Marshall

Attachment Content-Type Size
plancat.c.PATCH text/plain 2.6 KB
test_ce_patch.sql text/plain 2.1 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Henry B. Hotz 2007-04-30 22:23:25 Fwd: [PATCHES] Preliminary GSSAPI Patches
Previous Message Tom Lane 2007-04-30 21:30:07 Re: When the locially dropped column is also physically dropped