On 2/9/23 10:03, Hans Buschmann wrote:
> Hello Tomas,
>
>
> Thank you for looking at.
>
>
> First, I miscalculated the factor which should be about 50, not 500. Sorry.
>
> Then I want to show you the table definitions (simple, very similar,
> ommited child_tables and additional indexes, here using always "ONLY"):
>
> cpsdb_matcol=# \d sa_upper;
> Tabelle ╗public.sa_upper½
> Spalte | Typ | Sortierfolge | NULL erlaubt? |
> Vorgabewert
> --+---+--+---+--
> id_sup | integer | | not null |
> generated by default as identity
> sup_season | smallint | | |
> sup_sa_code | character varying(10) | C | |
> sup_mat_code | character varying(4) | C | |
> sup_clr_code | character varying(3) | C | |
> Indexe:
> "sa_upper_active_pkey" PRIMARY KEY, btree (id_sup)
>
>
> cpsdb_matcol=# \d sa_lining+;
> Tabelle ╗public.sa_lining½
> Spalte | Typ | Sortierfolge | NULL erlaubt? |
> Vorgabewert
> --+---+--+---+--
> id_sli | integer | | not null |
> generated by default as identity
> sli_season | smallint | | |
> sli_sa_code | character varying(10) | C | |
> sli_mat_code | character varying(4) | C | |
> sli_clr_code | character varying(3) | C | |
> Indexe:
> "sa_lining_active_pkey" PRIMARY KEY, btree (id_sli)
>
>
> cpsdb_matcol=# \d sa_insole;
> Tabelle ╗public.sa_insole½
> Spalte | Typ | Sortierfolge | NULL erlaubt? |
> Vorgabewert
> --+---+--+---+--
> id_sin | integer | | not null |
> generated by default as identity
> sin_season | smallint | | |
> sin_sa_code | character varying(10) | C | |
> sin_mat_code | character varying(4) | C | |
> sin_clr_code | character varying(3) | C | |
> Indexe:
> "sa_insole_active_pkey" PRIMARY KEY, btree (id_sin)
>
>
> cpsdb_matcol=# \d sa_outsole;
> Tabelle ╗public.sa_outsole½
> Spalte | Typ | Sortierfolge | NULL erlaubt? |
> Vorgabewert
> --+---+--+---+--
> id_sou | integer | | not null |
> generated by default as identity
> sou_season | smallint | | |
> sou_sa_code | character varying(10) | C | |
> sou_mat_code | character varying(4) | C | |
> sou_clr_code | character varying(3) | C | |
> Indexe:
> "sa_outsole_active_pkey" PRIMARY KEY, btree (id_sou)
>
> The xxx_target tables are very similiar, here the upper one as an example:
> They are count_aggregates of the whole dataset, where
> up_mat_code=sup_mat_code etc.
>
> cpsdb_matcol=# \d upper_target
> Tabelle ╗admin.upper_target½
> Spalte | Typ | Sortierfolge | NULL erlaubt? | Vorgabewert
> -+--+--+---+-
> id_up | smallint | | |
> nup | integer | | |
> up_mat_code | text | C | |
>
>
>
> I have reworked the two queries to show their complete explain plans:
>
> 1. query with left join in the qupd CTE:
>
> \set only 'ONLY'
>
> cpsdb_matcol=# explain analyze -- explain analyze verbose -- explain --
> select * from ( -- select count(*) from ( -- select length(sel) from (
> cpsdb_matcol-# with
> cpsdb_matcol-# qup as (
> cpsdb_matcol(# select
> cpsdb_matcol(# curr_season -- all xxx_seasosn are always smallint
> cpsdb_matcol(# ,curr_code-- all xx_code are always varchar(10)
> cpsdb_matcol(# ,array_agg(id_up order by
> id_up)||array_fill(0::smallint,array[10]) as mat_arr
> cpsdb_matcol(# ,array_agg(curr_mat_code order by id_up) as matcode_arr
> cpsdb_matcol(# ,bit_or(imask) as ibitmask
> cpsdb_matcol(# from(
> cpsdb_matcol(# select
> cpsdb_matcol(# sup_season as curr_season
> cpsdb_matcol(# ,sup_sa_code as curr_code
> cpsdb_matcol(# ,sup_mat_code as curr_mat_code
> cpsdb_matcol(# ,sup_clr_code as curr_clr_code
> cpsdb_matcol(# ,id_up
> cpsdb_matcol(#
