Re: Partitioning and constraint exclusion

Samuel Smith wrote
> I noticed that I could get very nice partition elimination using
> constant values in the where clause.
>
> Ex:
> select * from

> where
> <constraint_col>
> between '2015-01-01' and
> '2015-02-15'
>
> However, I could not get any partition elimination for queries that did
> not have constant values in the where clause.
>
> Ex:
> select * from

> where
> <constraint_col>
> >= (select max(date) from
> <other_table>
> )
>
> Unfortunately all of our queries on the analytics team need to be
> dynamic like this and summarize data based around certain recorded
> events and dates from other tables. I saw the note in the docs about not
> being able to use current_timestamp in the where clause but I really
> need to be able to use a sub select or CTE in the where clause for the
> needed dates.
>
> I tried about 10 different ways (on both 9.1 and 9.4) to dynamically get
> the data (sub selects, cte, joins) for my constraint column but all of
> them resulted in a full scan of all partitions.
>
> I am going to try a few other ways tomorrow, I am hoping I am doing
> something wrong, or is this just typical?

In short - since the planner determines exclusion constraints and the
executor, which strictly follows the planner in the query execution process,
would be the one to determine what the value of your date is - there is no
way for a single query to provide data that would then be used to determine
exclusion constraints.

Now, that said, I don't believe you should be actual full table scans during
processing if you have proper indexes setup. An index scan should be usable
and quickly determine which tables lack data to contribute to the query
results.

In terms of separating out the date query and partition query:

PREPARE/EXECUTE in pure SQL (can, must?, be direct)
EXECUTE/USING in pl/pgsql (via a function)

You may have other reasonable options on the client side...

You should consider providing EXPLAIN ANALYZE results and maybe a test case
so others can give pointers.

David J.

--
View this message in context: http://postgresql.nabble.com/Partitioning-and-constraint-exclusion-tp5840353p5840356.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.