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
*** plancat.c.orig 2007-04-30 12:17:55.785145396 -0400
--- plancat.c 2007-04-30 14:15:48.093639183 -0400
***************
*** 473,478 ****
--- 473,479 ----
List *constraint_pred;
List *safe_constraints;
ListCell *lc;
+ bool found_unsafe_restrictions;
/* Skip the test if constraint exclusion is disabled */
if (!constraint_exclusion)
***************
*** 486,491 ****
--- 487,493 ----
* Note: strip off RestrictInfo because predicate_refuted_by() isn't
* expecting to see any in its predicate argument.
*/
+ found_unsafe_restrictions = false;
safe_restrictions = NIL;
foreach(lc, rel->baserestrictinfo)
{
***************
*** 493,498 ****
--- 495,502 ----
if (!contain_mutable_functions((Node *) rinfo->clause))
safe_restrictions = lappend(safe_restrictions,
rinfo->clause);
+ else
+ found_unsafe_restrictions = true;
}
if (predicate_refuted_by(safe_restrictions, safe_restrictions))
***************
*** 522,527 ****
--- 526,559 ----
}
/*
+ * Check the restrictions against the relation constraints.
+ * If we found mutable functions in the restrictions, try to simplify
+ * them prior to checking. Effectively, this folds stable and
immutable
+ * functions into constant values.
+ */
+ if (found_unsafe_restrictions)
+ {
+ List * simp_restrictions = NIL;
+ foreach(lc, rel->baserestrictinfo)
+ {
+ RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc);
+ Node * baseNode = (Node *) rinfo->clause;
+
+ if (IsA(baseNode, FuncExpr) || IsA(baseNode, OpExpr))
+ {
+ Node * simpNode =
estimate_expression_value(baseNode);
+ simp_restrictions = lappend(simp_restrictions,
simpNode);
+ }
+ else
+ {
+ simp_restrictions = lappend(simp_restrictions,
baseNode);
+ }
+ }
+
+ if (predicate_refuted_by(safe_constraints, simp_restrictions))
+ return true;
+ }
+ /*
* The constraints are effectively ANDed together, so we can just try to
* refute the entire collection at once. This may allow us to make
proofs
* that would fail if we took them individually.
***************
*** 531,538 ****
* have volatile and nonvolatile subclauses, and it's OK to make
* deductions with the nonvolatile parts.
*/
! if (predicate_refuted_by(safe_constraints, rel->baserestrictinfo))
return true;
return false;
}
--- 563,572 ----
* have volatile and nonvolatile subclauses, and it's OK to make
* deductions with the nonvolatile parts.
*/
! else if (predicate_refuted_by(safe_constraints, rel->baserestrictinfo))
! {
return true;
+ }
return false;
}
--
-- 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'::timestamp with 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'::timestamp with 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'::timestamp with 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 current timestamp; it should avoid use of table
test_bulletins_20060908, ONLY with NEW CE code' 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;
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
