Re: Partitioning and constraint exclusion

Samuel,

* Samuel Smith (pgsql(at)net153(dot)net) wrote:
> I noticed that I could get very nice partition elimination using
> constant values in the where clause.
>
> Ex:
> select * from <table> 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 <table> where <constraint_col> >= (select max(date)
> from <other_table>)

That's correct.

> 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.

Not sure if this will help, but the planner is smart enough to implement
one-time filters for certain cases. Instead of using inheiritance-based
partitioning, you can use a view like so:

CREATE VIEW v AS
SELECT * FROM table1 WHERE column1 = 5
UNION ALL
SELECT * FROM table2 WHERE column1 = 6
;

Then for cases where we can prove that no results will be returned from
the individual union-all branch, we'll skip it:

SELECT * FROM v WHERE column1 = (select max(column1) from table3);

Unfortunately, we don't appear to support that for an inequality as you
show above. I'm not sure offhand why not but it didn't work in my
testing.

Another approach to dealing with this is to use plpgsql functions and
'return execute' which essentially compute the constant and then build a
dyanmic SQL query using the constant and return the results. It's a bit
awkward compared to just writing the query, but it does work.

Thanks!

Stephen