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

Convert check constraints into One-Time_Filter on prepared statements

From: ITAGAKI Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Convert check constraints into One-Time_Filter on prepared statements
Date: 2008-10-13 09:00:44
Message-ID: 20081013172100.87A1.52131E4D@oss.ntt.co.jp (view raw or flat)
Thread:
Lists: pgsql-hackers
Hello,

Constraint exclusion (CE) is very useful for partitioned tables,
but prepared statements interfere with CE if the parameter
contains partition keys, because CE is a planner-time optimization
but the actual parameters are given at executor-time.

I have an idea to use constraint exclusion and prepared statements
together -- converting check constraints into One-Time Filter.

For example, when we have "test" table partitioned by test_{year}:

  CREATE TABLE test PARTITIONED BY
    PARTITION test_2008 CHECK('2008-01-01' <= t AND t < '2009-01-01')
    PARTITION test_2009 CHECK('2009-01-01' <= t AND t < '2010-01-01')
    PARTITION test_2010 CHECK('2010-01-01' <= t AND t < '2011-01-01')

and prepare a statement that have a partitioned key in the parameter:

  PREPARE p(timestamp) AS
    SELECT * FROM test WHERE $1 <= t AND t < $1 + '1 mon';

Then planner converts check constraints into One-Time Filter.
Plan will be the following:

 EXPLAIN EXECUTE p('2008-07-01');
-------------------------------------------------------------------------
  Append
    ->  Result
          One-Time Filter: (('2008-01-01' <= $1) AND ($1 < '2009-01-01'))
            ->  Index Scan on test_2008_t_key
                  Index Cond: (($1 <= t) AND (t < ($1 + '1 mon')))
    ->  Result
          One-Time Filter: (('2009-01-01' <= $1) AND ($1 < '2010-01-01'))
            ->  Index Scan on test_2009_t_key
                  Index Cond: (($1 <= t) AND (t < ($1 + '1 mon')))
    ->  Result
          One-Time Filter: (('2010-01-01' <= $1) AND ($1 < '2011-01-01'))
            ->  Index Scan on test_2010_t_key
                  Index Cond: (($1 <= t) AND (t < ($1 + '1 mon')))

We can avoid internal scans when One-Time Filter returns false for each
partition. So we can reuse the plan and receive benefit from CE.

Is this concept ok and worth trying?
If it is reasonable, I'll try it. Comments welcome.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center


Responses

pgsql-hackers by date

Next:From: Bramandia RamadhanaDate: 2008-10-13 09:14:18
Subject: Re: Block nested loop join
Previous:From: Heikki LinnakangasDate: 2008-10-13 08:58:54
Subject: Re: pg_upgrade: convert on read is dead end

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