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
