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]));
[http://www.etiya.com/images/e-newsletter/signature/e_logo_1.png]
[http://www.etiya.com/images/e-newsletter/signature/e_adres.png]<http://www.etiya.com>
[http://www.etiya.com/images/e-newsletter/signature/facebook_icon.png]<https://www.facebook.com/Etiya-249050755136326/>
[http://www.etiya.com/images/e-newsletter/signature/linkedin_icon.png]
<https://www.linkedin.com/company/etiya?trk=tyah&trkInfo=tas%3Aetiya%2Cidx%3A1-1-1>
[http://www.etiya.com/images/e-newsletter/signature/instagram_icon.png]
<https://www.instagram.com/etiya_/>
[http://www.etiya.com/images/e-newsletter/signature/youtube_icon.png]
<https://www.youtube.com/channel/UCWjknu72sHoKKt2nujuU2kA>
[http://www.etiya.com/images/e-newsletter/signature/twitter_icon.png]
<https://twitter.com/etiya_>
[http://www.etiya.com/images/e-newsletter/signature/0.png]
Yavuz Selim Sertoğlu
Solution Support Specialist II
T:+90 312 265 01 50
M:+90 552 997 52 02
E:[email protected]<mailto:[email protected]>
Üniversiteler Mahallesi 1606.cadde No:4 Cyberpark C Blok Zemin kat ofis no
:Z25A-Z44
[http://www.etiya.com/images/e-newsletter/signature/tmf_award.jpg]
<https://www.etiya.com/press/view/etiya-wins-tm-forum-excellence-award-for-disruptive-innovation>
Yasal Uyari :
Bu elektronik posta asagidaki adreste bulunan Kosul ve Sartlara tabidir;
http://www.etiya.com/gizlilik<www.etiya.com/gizlilik>
ÇIKTI ALMADAN ÖNCE ÇEVREYE OLAN SORUMLULUGUMUZU BIR KEZ DAHA DÜSÜNELIM.
PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING ANY DOCUMENT.
