force partition pruning

Hi all

I need a litte advice on how to

Postgres 13.2

A metadata query pulls partition keys:

select m.period_version from register.register_metadata m where m.current_version and m.period between '201712' and '201912' ;

A query using these in an in-list easily makes the planner do partition pruning.

select * from register.register d where d.period_version in ('201712_1', '201812_1', '201912_1');

However combining the metadataquery into the dataquery makes the planner decide to scan all partitions.

select * from register.register d where d.period_version in (select m.period_version from register.register_metadata m where m.current_version and m.period between '201712' and '201912');

I am quite aware that the latter query requires partition pruning to take place during execution not during planning.

My question here is how do I package the two-step proces into an interface that analysts can actually use?

One possibility is to have a prepare step that creates a temporary view with the hard-coded values built-in. And then query data via the temp view. This works ok, but there is an issue with possible naming conflicts on the temp view (not that this could not be worked around).

Ideally I would like a function to figure out the query and then return the data from that dynamically executed query. Complicating matters is the fact that there are more than one set of data/metatable tables and each datatable has a different set of columns. This excludes a table returning function since that must list the columns present.

Best regards

Niels Jespersen

Chief Adviser

IT Center

Mobile phone:+45 42 42 93 73
Email: njn@dst.dk

Statistics Denmark, Sejrøgade 11, DK-2100 Copenhagen

www.dst.dk/en | Twitter | LinkedIn | Facebook