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

Reply via email to