Re: Query slows when used with view
Michael Lewis writes: >> When you join to a view, the view sticks together, as if they were all in >> parentheses. But when you substitute the text of a view into another >> query, then they are all on the same level and can be parsed differently. >> >> Consider the difference between "1+1 * 3", and "(1+1) * 3" > I thought from_collapse_limit being high enough meant that it will get > re-written and inlined into the same level. To extend your metaphor, that > it would be 1 * 3 + 1 * 3. The point is that the semantics are actually different --- in Jeff's example, the answer is 4 vs. 6, and in the OP's query, the joins have different scopes. from_collapse_limit has to do with whether the planner can rewrite the query into a different form, but it's not allowed to change the semantics by doing so. In some cases you can re-order joins without changing the semantics, just as arithmetic has associative and commutative laws. But you can't always re-order outer joins like that. I didn't dig into the details of the OP's query too much, but I believe that the two forms of his join tree are semantically different, resulting in different runtimes. regards, tom lane
Re: Query slows when used with view
> > When you join to a view, the view sticks together, as if they were all in > parentheses. But when you substitute the text of a view into another > query, then they are all on the same level and can be parsed differently. > > Consider the difference between "1+1 * 3", and "(1+1) * 3" > I thought from_collapse_limit being high enough meant that it will get re-written and inlined into the same level. To extend your metaphor, that it would be 1 * 3 + 1 * 3.
Re: Query slows when used with view
On Wed, Oct 9, 2019 at 10:56 AM Yavuz Selim Sertoğlu (ETIYA) < yavuz.serto...@etiya.com> wrote: > Thanks for the reply Tom, > > Sorry, I couldn't understand. I just copied inside of view and add > conditions from query that runs with view. > The comma parts are the same in two queries, one is inside of view the > other is in the query. > When you join to a view, the view sticks together, as if they were all in parentheses. But when you substitute the text of a view into another query, then they are all on the same level and can be parsed differently. Consider the difference between "1+1 * 3", and "(1+1) * 3" Cheers, Jeff
Re: Query slows when used with view
> > Those are not equivalent queries. Read up on the syntax of FROM; > particularly, that JOIN binds more tightly than comma. > I see this- "A JOIN clause combines two FROM items, which for convenience we will refer to as “tables”, though in reality they can be any type of FROM item. Use parentheses if necessary to determine the order of nesting. In the absence of parentheses, JOINs nest left-to-right. In any case JOIN binds more tightly than the commas separating FROM-list items." https://www.postgresql.org/docs/current/sql-select.html What is meant by nesting? Or binding for that matter? I wouldn't expect increasing from/join_collapse_limit to be helpful to the original poster since they haven't exceeded default limit of 8. Any further clarification elsewhere you could point to?
RE: Query slows when used with view
Thanks for the reply Tom, Sorry, I couldn't understand. I just copied inside of view and add conditions from query that runs with view. The comma parts are the same in two queries, one is inside of view the other is in the query. -Original Message- From: Tom Lane Sent: 09 October 2019 16:57 To: Yavuz Selim Sertoğlu (ETIYA) Cc: pgsql-performance@lists.postgresql.org Subject: Re: Query slows when used with view =?iso-8859-9?Q?Yavuz_Selim_Serto=F0lu_=28ETIYA=29?= writes: > 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. Those are not equivalent queries. Read up on the syntax of FROM; particularly, that JOIN binds more tightly than comma. regards, tom lane [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=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:yavuz.serto...@etiya.com<mailto:yavuz.serto...@etiya.com> Ü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 ÇIKTI ALMADAN ÖNCE ÇEVREYE OLAN SORUMLULUGUMUZU BIR KEZ DAHA DÜSÜNELIM. PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING ANY DOCUMENT.
Re: Query slows when used with view
=?iso-8859-9?Q?Yavuz_Selim_Serto=F0lu_=28ETIYA=29?= writes: > 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. Those are not equivalent queries. Read up on the syntax of FROM; particularly, that JOIN binds more tightly than comma. regards, tom lane
Query slows when used with view
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[]))
