Partition elimination and date variables

Question

I have a table that is partitioned on a date column, with a default partition. In our deletion process, we drop partitions that are older than the retention date and then delete records that are older than the retention date in order to catch any that are in the default.

The delete statement is not using partition elimination.

I select the date from a table into a date variable, then use that in a query:

declare v_retention_date date;

select retention_date
from schemaname.parametertable
into v_retention_date;

select count(*)
into v_row_count
from schemaname.tablename 
where pdate < v_retention_date;

Other queries are doing partition elimination, those are passed in as text parameters to the procedure. Why might this query not do so?

Answer 1

Got an answer from Pivotal - I should have realised this, but parameterised queries are planned in ignorance of the parameter value, and partition elimination is part of the query plan, so we can't use variables for partition elimination.

I am re-tooling our partition elimination to use views that are dynamically constructed with an IN list of dates. This specific query is now just looking at the overflow partition as that's a lot simpler.