Search Postgresql Archives

views on partitioned tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

 




Pflichtangaben anzeigen

Nähere Informationen zur Datenverarbeitung im DB-Konzern finden Sie hier: http://www.deutschebahn.com/de/konzern/datenschutz

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux