Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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: test_ce_patch.sql
Description: text/plain (2.1 KB)
Attachment: plancat.c.PATCH
Description: text/plain (2.6 KB)

Responses

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group