ccfdb=> \c
You are now connected to database "ccfdb" as user "ccfuser".
ccfdb=> explain SELECT A
                                                 .* FROM
                                          (
                                          select * from dcg.brick_base_ebr t1 where 1=1 and t1.ze2e_flg !='J'
                                          and t1.fiscper  >='2020007' and  '2020007' >=t1.fiscper
                                          and t1.fiscyear = '2020'

                                          )
                                          A LEFT JOIN
                                          (
                                                                      SELECT
                                                                      sd_vbeln,
                                                                      sd_posnr,
                                                                      wrbtr,
                                                                      netpr,
                                                                      matnr
                                                               FROM
                                                                      (
                                                               SELECT ROW_NUMBER
                                                                      () OVER ( PARTITION BY sd_vbeln, sd_posnr ORDER BY A.belnr DESC, A.buzei DESC ) num,*
                                                               FROM
                                                                      dcg.brick_shipment AS A
                                                                      ) AS A
                                                               WHERE
                                                                      num = 1
                                          )
                                          bs ON  A.doc_number = bs.sd_vbeln
                                          AND A.s_ord_item = bs.sd_posnr
                                          LEFT JOIN dcg.brick_billing t2
                                                        ON
                                                        A.bill_num=t2.vbeln
;                                                       AND A.bill_item=t2.posnr
                                                                               QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------
--------------------------------
 Gather  (cost=99636.42..163455.57 rows=79187 width=1107)
   Workers Planned: 3
   ->  Hash Left Join  (cost=98636.42..154536.87 rows=25544 width=1107)
         Hash Cond: ((t1.bill_num = (t2.vbeln)::bpchar) AND (t1.bill_item = t2.posnr))
         ->  Hash Left Join  (cost=60660.29..94891.77 rows=25544 width=1107)
               Hash Cond: ((t1.doc_number = (a.sd_vbeln)::bpchar) AND (t1.s_ord_item = a.sd_posnr))
               ->  Append  (cost=0.00..23310.24 rows=25545 width=1107)
                     ->  Parallel Seq Scan on brick_base_ebr t1  (cost=0.00..0.00 rows=1 width=4742)
                           Filter: ((ze2e_flg <> 'J'::bpchar) AND (fiscper >= '2020007'::numeric) AND ('2020007'::numeric >= fiscper) AN
D (fiscyear = '2020'::numeric))
                     ->  Parallel Seq Scan on brick_base_ebr_2020007 t1_1  (cost=0.00..23310.24 rows=25544 width=1107)
                           Filter: ((ze2e_flg <> 'J'::bpchar) AND (fiscper >= '2020007'::numeric) AND ('2020007'::numeric >= fiscper) AN
D (fiscyear = '2020'::numeric))
               ->  Hash  (cost=60644.50..60644.50 rows=1053 width=15)
                     ->  Subquery Scan on a  (cost=52744.22..60644.50 rows=1053 width=15)
                           Filter: (a.num = 1)
                           ->  WindowAgg  (cost=52744.22..58011.07 rows=210674 width=3565)
                                 ->  Sort  (cost=52744.22..53270.91 rows=210674 width=31)
                                       Sort Key: a_1.sd_vbeln, a_1.sd_posnr, a_1.belnr DESC, a_1.buzei DESC
                                       ->  Seq Scan on brick_shipment a_1  (cost=0.00..34115.74 rows=210674 width=31)
         ->  Hash  (cost=29742.85..29742.85 rows=414085 width=16)
               ->  Seq Scan on brick_billing t2  (cost=0.00..29742.85 rows=414085 width=16)
(20 rows)

ccfdb=> \c ccfdb a_app
You are now connected to database "ccfdb" as user "a_app".
ccfdb=> \c
You are now connected to database "ccfdb" as user "a_app".
ccfdb=> explain SELECT A
                                                 .* FROM
                                          (
                                          select * from dcg.brick_base_ebr t1 where 1=1 and t1.ze2e_flg !='J'
                                          and t1.fiscper  >='2020007' and  '2020007' >=t1.fiscper
                                          and t1.fiscyear = '2020'

                                          )
                                          A LEFT JOIN
                                          (
                                                                      SELECT
                                                                      sd_vbeln,
                                                                      sd_posnr,
                                                                      wrbtr,
                                                                      netpr,
                                                                      matnr
                                                               FROM
                                                                      (
                                                               SELECT ROW_NUMBER
                                                                      () OVER ( PARTITION BY sd_vbeln, sd_posnr ORDER BY A.belnr DESC, A.buzei DESC ) num,*
                                                               FROM
                                                                      dcg.brick_shipment AS A
                                                                      ) AS A
                                                               WHERE
                                                                      num = 1
                                          )
                                          bs ON  A.doc_number = bs.sd_vbeln
                                          AND A.s_ord_item = bs.sd_posnr
                                          LEFT JOIN dcg.brick_billing t2
                                                        ON
                                                        A.bill_num=t2.vbeln
;                                                       AND A.bill_item=t2.posnr
                                                                               QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------
--------------------------------
 Nested Loop Left Join  (cost=52752.70..93540.96 rows=2 width=2926)
   Join Filter: ((t1.doc_number = (a.sd_vbeln)::bpchar) AND (t1.s_ord_item = a.sd_posnr))
   ->  Hash Right Join  (cost=8.47..32856.97 rows=2 width=2926)
         Hash Cond: (((t2.vbeln)::bpchar = t1.bill_num) AND (t2.posnr = t1.bill_item))
         ->  Seq Scan on brick_billing t2  (cost=0.00..29742.85 rows=414085 width=16)
         ->  Hash  (cost=8.44..8.44 rows=2 width=2924)
               ->  Append  (cost=0.00..8.44 rows=2 width=2924)
                     ->  Seq Scan on brick_base_ebr t1  (cost=0.00..0.00 rows=1 width=4742)
                           Filter: ((ze2e_flg <> 'J'::bpchar) AND (fiscper >= '2020007'::numeric) AND ('2020007'::numeric >= fiscper) AN
D (fiscyear = '2020'::numeric))
                     ->  Index Scan using brick_base_ebr_fiscper_2020007 on brick_base_ebr_2020007 t1_1  (cost=0.42..8.44 rows=1 width=1
107)
                           Index Cond: ((fiscper >= '2020007'::numeric) AND ('2020007'::numeric >= fiscper))
                           Filter: ((ze2e_flg <> 'J'::bpchar) AND (fiscyear = '2020'::numeric))
   ->  Materialize  (cost=52744.22..60649.76 rows=1053 width=15)
         ->  Subquery Scan on a  (cost=52744.22..60644.50 rows=1053 width=15)
               Filter: (a.num = 1)
               ->  WindowAgg  (cost=52744.22..58011.07 rows=210674 width=3565)
                     ->  Sort  (cost=52744.22..53270.91 rows=210674 width=31)
                           Sort Key: a_1.sd_vbeln, a_1.sd_posnr, a_1.belnr DESC, a_1.buzei DESC
                           ->  Seq Scan on brick_shipment a_1  (cost=0.00..34115.74 rows=210674 width=31)
(19 rows)