Query slows when used with view - Mailing list pgsql-performance

Hi all,

I have a problem with views. When I use view in my query it really slows down(1.7seconds)

If I use inside of view and add conditions and joins to it, it is really fast(0.7 milliseconds).

I have no distinct/group/partition by in view so I have no idea why is this happening.

I wrote queries and plans below.

I would be very happy if you can help me.

Best regards,

Query without view;

explain analyze select

       *

from

       bss.prod_char_val

left join bss.prod on

       prod.prod_id = prod_char_val.prod_id,

       bss.gnl_st prodstatus,

       bss.gnl_char

left join bss.gnl_char_lang on

       gnl_char_lang.char_id = gnl_char.char_id,

       bss.gnl_char_val

left join bss.gnl_char_val_lang on

       gnl_char_val_lang.char_val_id = gnl_char_val.char_val_id,

       bss.gnl_st charvalstatus

       cross join bss.prod  prodentity0_

cross join bss.cust custentity2_

where

       prod.st_id = prodstatus.gnl_st_id

       and (prodstatus.shrt_code::text = any (array['ACTV'::character varying::text,

       'PNDG'::character varying::text]))

       and gnl_char_val_lang.is_actv = 1::numeric

       and gnl_char_lang.is_actv = 1::numeric

       and gnl_char_lang.lang::text = gnl_char_val_lang.lang::text

       and prod_char_val.char_id = gnl_char.char_id

       and prod_char_val.char_val_id = gnl_char_val.char_val_id

       and prod_char_val.st_id = charvalstatus.gnl_st_id

       and (charvalstatus.shrt_code::text = any (array['ACTV'::character varying::text,'PNDG'::character varying::text]))

       and gnl_char_val_lang.lang = 'en'

       and (charvalstatus.shrt_code = 'xxx'

       and prod_char_val.val = 'xxx'

       or charvalstatus.shrt_code = 'xxx'

       and prod_char_val.val = 'xxx')

       and prodentity0_.prod_id = prod_char_val.prod_id

    and custentity2_.party_id = 16424

    and prodentity0_.cust_id = custentity2_.cust_id

   order by

       prodentity0_.prod_id desc;

Sort  (cost=373.92..373.93 rows=1 width=19509) (actual time=0.098..0.098 rows=0 loops=1)

  Sort Key: prod_char_val.prod_id DESC

  Sort Method: quicksort  Memory: 25kB

  ->  Nested Loop  (cost=2.57..373.91 rows=1 width=19509) (actual time=0.066..0.066 rows=0 loops=1)

        Join Filter: (gnl_char_val.char_val_id = gnl_char_val_lang.char_val_id)

        ->  Nested Loop  (cost=2.30..373.58 rows=1 width=19447) (actual time=0.066..0.066 rows=0 loops=1)

              ->  Nested Loop  (cost=2.15..373.42 rows=1 width=18571) (actual time=0.066..0.066 rows=0 loops=1)

                    Join Filter: (gnl_char.char_id = gnl_char_lang.char_id)

                    ->  Nested Loop  (cost=1.88..373.09 rows=1 width=18488) (actual time=0.066..0.066 rows=0 loops=1)

                          ->  Nested Loop  (cost=1.73..372.92 rows=1 width=16002) (actual time=0.066..0.066 rows=0 loops=1)

                                Join Filter: (charvalstatus.gnl_st_id = prod_char_val.st_id)

                                ->  Nested Loop  (cost=1.29..214.51 rows=11 width=15914) (actual time=0.065..0.065 rows=0 loops=1)

                                      ->  Nested Loop  (cost=1.15..207.14 rows=44 width=15783) (actual time=0.065..0.065 rows=0 loops=1)

                                            ->  Nested Loop  (cost=0.72..180.73 rows=44 width=9586) (actual time=0.065..0.065 rows=0 loops=1)

                                                  ->  Seq Scan on gnl_st charvalstatus  (cost=0.00..10.61 rows=1 width=131) (actual time=0.064..0.065 rows=0 loops=1)

                                                        Filter: (((shrt_code)::text = ANY ('{ACTV,PNDG}'::text[])) AND ((shrt_code)::text = 'xxx'::text))

                                                        Rows Removed by Filter: 307

                                                  ->  Nested Loop  (cost=0.72..169.68 rows=44 width=9455) (never executed)

                                                        ->  Index Scan using idx_cust_party_id on cust custentity2_  (cost=0.29..8.31 rows=1 width=3258) (never executed)

                                                              Index Cond: (party_id = '16424'::numeric)

                                                        ->  Index Scan using idx_prod_cust_id on prod prodentity0_  (cost=0.43..160.81 rows=57 width=6197) (never executed)

                                                              Index Cond: (cust_id = custentity2_.cust_id)

                                            ->  Index Scan using pk_prod on prod  (cost=0.43..0.60 rows=1 width=6197) (never executed)

                                                  Index Cond: (prod_id = prodentity0_.prod_id)

                                      ->  Index Scan using gnl_st_pkey on gnl_st prodstatus  (cost=0.15..0.17 rows=1 width=131) (never executed)

                                            Index Cond: (gnl_st_id = prod.st_id)

                                            Filter: ((shrt_code)::text = ANY ('{ACTV,PNDG}'::text[]))

                                ->  Index Scan using idx_prod_char_val_prod_id on prod_char_val  (cost=0.44..14.38 rows=2 width=88) (never executed)

                                      Index Cond: (prod_id = prod.prod_id)

                                      Filter: (((val)::text = 'xxx'::text) OR ((val)::text = 'xxx'::text))

                          ->  Index Scan using gnl_char_pkey on gnl_char  (cost=0.14..0.16 rows=1 width=2486) (never executed)

                                Index Cond: (char_id = prod_char_val.char_id)

                    ->  Index Scan using idx_gnl_char_lang_char_id on gnl_char_lang  (cost=0.27..0.32 rows=1 width=83) (never executed)

                          Index Cond: (char_id = prod_char_val.char_id)

                          Filter: ((is_actv = '1'::numeric) AND ((lang)::text = 'en'::text))

              ->  Index Scan using gnl_char_val_pkey on gnl_char_val  (cost=0.15..0.17 rows=1 width=876) (never executed)

                    Index Cond: (char_val_id = prod_char_val.char_val_id)

        ->  Index Scan using idx_gcvl_char_val_id on gnl_char_val_lang  (cost=0.28..0.32 rows=1 width=56) (never executed)

              Index Cond: (char_val_id = prod_char_val.char_val_id)

              Filter: ((is_actv = '1'::numeric) AND ((lang)::text = 'en'::text))

Planning time: 12.275 ms

Execution time: 0.770 ms

Query with view;

explain analyze select

       *

from

       bss.prod prodentity0_

cross join bss.v_prod_char_val vprodcharv1_

cross join bss.cust custentity2_

where

       vprodcharv1_.lang = 'en'

       and (vprodcharv1_.shrt_code = 'xxx'

       and vprodcharv1_.val = 'xxx'

       or vprodcharv1_.shrt_code = 'xxx'

       and vprodcharv1_.val = 'xxx')

       and prodentity0_.prod_id = vprodcharv1_.prod_id

       and custentity2_.party_id = 16424

       and prodentity0_.cust_id = custentity2_.cust_id

       order by       prodentity0_.prod_id desc;

Sort  (cost=19850.34..19850.34 rows=1 width=9616) (actual time=1661.094..1661.095 rows=6 loops=1)

  Sort Key: prodentity0_.prod_id DESC

  Sort Method: quicksort  Memory: 31kB

  ->  Nested Loop  (cost=6.72..19850.33 rows=1 width=9616) (actual time=527.507..1661.058 rows=6 loops=1)

        Join Filter: (prodentity0_.cust_id = custentity2_.cust_id)

        Rows Removed by Join Filter: 98999

        ->  Index Scan using idx_cust_party_id on cust custentity2_  (cost=0.29..8.31 rows=1 width=3258) (actual time=0.007..0.008 rows=1 loops=1)

              Index Cond: (party_id = '16424'::numeric)

        ->  Nested Loop  (cost=6.43..19841.41 rows=49 width=6352) (actual time=0.066..1644.202 rows=99005 loops=1)

              ->  Nested Loop  (cost=6.00..19812.00 rows=49 width=161) (actual time=0.061..1347.225 rows=99005 loops=1)

                    Join Filter: (gnl_char_val.char_val_id = gnl_char_val_lang.char_val_id)

                    ->  Nested Loop  (cost=5.72..19795.69 rows=49 width=162) (actual time=0.055..1110.850 rows=99005 loops=1)

                          ->  Nested Loop  (cost=5.58..19787.60 rows=49 width=142) (actual time=0.048..972.595 rows=99005 loops=1)

                                ->  Nested Loop  (cost=5.43..19754.45 rows=198 width=149) (actual time=0.045..831.933 rows=101354 loops=1)

                                      ->  Nested Loop  (cost=5.00..19375.29 rows=198 width=128) (actual time=0.038..436.324 rows=101354 loops=1)

                                            ->  Nested Loop  (cost=4.85..19241.37 rows=799 width=122) (actual time=0.032..179.888 rows=188944 loops=1)

                                                  ->  Nested Loop  (cost=4.29..15.95 rows=1 width=46) (actual time=0.014..0.044 rows=1 loops=1)

                                                        ->  Seq Scan on gnl_char  (cost=0.00..6.83 rows=1 width=20) (actual time=0.006..0.034 rows=1 loops=1)

                                                              Filter: ((shrt_code)::text = 'xxx'::text)

                                                              Rows Removed by Filter: 225

                                                        ->  Bitmap Heap Scan on gnl_char_lang  (cost=4.29..9.12 rows=1 width=26) (actual time=0.006..0.008 rows=1 loops=1)

                                                              Recheck Cond: (char_id = gnl_char.char_id)

                                                              Filter: ((is_actv = '1'::numeric) AND ((lang)::text = 'en'::text))

                                                              Rows Removed by Filter: 1

                                                              Heap Blocks: exact=1

                                                              ->  Bitmap Index Scan on idx_gnl_char_lang_char_id  (cost=0.00..4.29 rows=2 width=0) (actual time=0.003..0.003 rows=2 loops=1)

                                                                    Index Cond: (char_id = gnl_char.char_id)

                                                  ->  Index Scan using idx_prod_char_val_v02 on prod_char_val  (cost=0.56..19213.05 rows=1237 width=88) (actual time=0.018..140.837 rows=188944 loops=1)

                                                        Index Cond: (char_id = gnl_char_lang.char_id)

                                                        Filter: (((val)::text = 'xxx'::text) OR ((val)::text = 'xxx'::text))

                                                        Rows Removed by Filter: 3986

                                            ->  Index Scan using gnl_st_pkey on gnl_st charvalstatus  (cost=0.15..0.17 rows=1 width=11) (actual time=0.001..0.001 rows=1 loops=188944)

                                                  Index Cond: (gnl_st_id = prod_char_val.st_id)

                                                  Filter: ((shrt_code)::text = ANY ('{ACTV,PNDG}'::text[]))

                                                  Rows Removed by Filter: 0

                                      ->  Index Scan using pk_prod on prod  (cost=0.43..1.91 rows=1 width=21) (actual time=0.003..0.003 rows=1 loops=101354)

                                            Index Cond: (prod_id = prod_char_val.prod_id)

                                ->  Index Scan using gnl_st_pkey on gnl_st prodstatus  (cost=0.15..0.17 rows=1 width=5) (actual time=0.001..0.001 rows=1 loops=101354)

                                      Index Cond: (gnl_st_id = prod.st_id)

                                      Filter: ((shrt_code)::text = ANY ('{ACTV,PNDG}'::text[]))

                                      Rows Removed by Filter: 0

                          ->  Index Scan using gnl_char_val_pkey on gnl_char_val  (cost=0.15..0.17 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=99005)

                                Index Cond: (char_val_id = prod_char_val.char_val_id)

                    ->  Index Scan using idx_gcvl_char_val_id on gnl_char_val_lang  (cost=0.28..0.32 rows=1 width=14) (actual time=0.001..0.002 rows=1 loops=99005)

                          Index Cond: (char_val_id = prod_char_val.char_val_id)

                          Filter: ((is_actv = '1'::numeric) AND ((lang)::text = 'en'::text))

                          Rows Removed by Filter: 1

              ->  Index Scan using pk_prod on prod prodentity0_  (cost=0.43..0.60 rows=1 width=6197) (actual time=0.002..0.002 rows=1 loops=99005)

                    Index Cond: (prod_id = prod.prod_id)

Planning time: 6.947 ms

Execution time: 1661.278 ms

This is the view;
create or replace

view bss.v_prod_char_val as select

       prod_char_val.prod_char_val_id,

       prod_char_val.prod_id,

       prod_char_val.char_id,

       prod_char_val.char_val_id,

       prod_char_val.val,

       prod_char_val.trnsc_id,

       prod_char_val.sdate,

       prod_char_val.edate,

       prod_char_val.st_id,

       prod_char_val.cdate,

       prod_char_val.cuser,

       prod_char_val.udate,

       prod_char_val.uuser,

       gnl_char_lang.name as char_name,

       gnl_char_val_lang.val_lbl as char_val_name,

       charvalstatus.shrt_code as prod_char_val_st_shrt_code,

       gnl_char_val_lang.lang,

       gnl_char.shrt_code,

       gnl_char_val.shrt_code as char_val_shrt_code,

       prod.bill_acct_id

from

       bss.prod_char_val

left join bss.prod on

       prod.prod_id = prod_char_val.prod_id,

       bss.gnl_st prodstatus,

       bss.gnl_char

left join bss.gnl_char_lang on

       gnl_char_lang.char_id = gnl_char.char_id,

       bss.gnl_char_val

left join bss.gnl_char_val_lang on

       gnl_char_val_lang.char_val_id = gnl_char_val.char_val_id,

       bss.gnl_st charvalstatus

where

       prod.st_id = prodstatus.gnl_st_id

       and (prodstatus.shrt_code::text = any (array['ACTV'::character varying::text,

       'PNDG'::character varying::text]))

       and gnl_char_val_lang.is_actv = 1::numeric

       and gnl_char_lang.is_actv = 1::numeric

       and gnl_char_lang.lang::text = gnl_char_val_lang.lang::text

       and prod_char_val.char_id = gnl_char.char_id

       and prod_char_val.char_val_id = gnl_char_val.char_val_id

       and prod_char_val.st_id = charvalstatus.gnl_st_id

       and (charvalstatus.shrt_code::text = any (array['ACTV'::character varying::text,

       'PNDG'::character varying::text]));