views on partitioned tables - Mailing list pgsql-general

Hello,

we are using several partitioned tables. We regularly encounter the problem, that we would like to help analysts (and ourselves) with joining these tables by building predefined Views.

Is it possible to handle the partitions when querying the Views if the tables are hidden in Subqueries? Are there other recommendations on how to achieve this? Below is an example. Every table involved has around 90 partitions.

Some of my experiments so far:

• Parameters to force a partition choice when querying the View by using current_setting('set_partition.abfahrt_tag_plan') at several places. This has terrible impact on downstream processes or when multiple partitions are needed.
• Materialized Views. Additional storage costs and refresh-management.
• Adding unnecessary joins clauses to suggest using partitions when querying from outside.

-- ereignis_sv_soll (tag_plan) and vereinigung_sv_soll (abfahrt_tag_plan) are partitioned.

CREATE VIEW public.vereinigung_sv_soll_expanded as
WITH vereinigung_sv_soll_deduplicated as
         (
             SELECT max(vereinigung_key) as vereinigung_key
             from (
                      SELECT vereinigung_key,
                             string_agg(vereinigung.fahrtid || vereinigung.abfahrt_ereignisid || vereinigung.ankunft_ereignisid, ' | '
                                        order by vereinigung.fahrtid) as vereinigung_eine_zeile
                      FROM vereinigung_sv_soll vereinigung
                      GROUP BY vereinigung_key
                  ) temp
             GROUP BY vereinigung_single_row
         )
SELECT
    vereinigung_sv_soll.abfahrt_tag_plan,
    vereinigung_sv_soll.…,
    abfahrt.start_tag,
    abfahrt.…,

    ankunft.evanr as ankunft_evanr,
    ankunft.…,

FROM vereinigung_sv_soll
         INNER JOIN ereignis_sv_soll abfahrt
                    ON vereinigung_sv_soll.abfahrt_ereignisid = abfahrt.ereignisid
         INNER JOIN  ereignis_sv_soll ankunft
                     ON vereinigung_sv_soll.ankunft_ereignisid = ankunft.ereignisid
         INNER JOIN vereinigung_sv_soll_deduplicated
                    ON vereinigung_sv_soll.vereinigung_key = vereinigung_sv_soll_deduplicated.vereinigung_key
;

Kind regards,

Holger Vornholt
Reisendeninformation (T.RS)

Deutsche Bahn AG

Hahnstr. 40, 60528 Frankfurt a. Main
Chat | Call | +49152 37557535