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: n...@dst.dk
Statistics Denmark, Sejrøgade 11, DK-2100 Copenhagen
www.dst.dk/en<https://www.dst.dk/en> | Twitter<https://twitter.com/dstdk> |
LinkedIn<https://www.linkedin.com/company/statistics-denmark/> |
Facebook<https://www.facebook.com/danmarksstatistik>
[cid:image001.png@01D745A1.DEE94640]
