pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery slows when used with view
7+ messages / 4 participants
[nested] [flat]
* Query slows when used with view
@ 2019-10-09 12:31 =?iso-8859-9?Q?Yavuz_Selim_Serto=F0lu_=28ETIYA=29?= <yavuz.sertoglu@etiya.com>
0 siblings, 1 reply; 7+ messages in thread
From: Yavuz Selim Sertoðlu (ETIYA) @ 2019-10-09 12:31 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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:yavuz.sertoglu@etiya.com<mailto:yavuz.sertoglu@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<www.etiya.com/gizlilik;
ÇIKTI ALMADAN ÖNCE ÇEVREYE OLAN SORUMLULUGUMUZU BIR KEZ DAHA DÜSÜNELIM.
PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING ANY DOCUMENT.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query slows when used with view
@ 2019-10-09 13:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: =?iso-8859-9?Q?Yavuz_Selim_Serto=F0lu_=28ETIYA=29?= <yavuz.sertoglu@etiya.com>
0 siblings, 2 replies; 7+ messages in thread
From: Tom Lane @ 2019-10-09 13:57 UTC (permalink / raw)
To: Yavuz Selim Sertoðlu (ETIYA) <yavuz.sertoglu@etiya.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
=?iso-8859-9?Q?Yavuz_Selim_Serto=F0lu_=28ETIYA=29?= <yavuz.sertoglu@etiya.com> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Query slows when used with view
@ 2019-10-09 14:55 =?iso-8859-9?Q?Yavuz_Selim_Serto=F0lu_=28ETIYA=29?= <yavuz.sertoglu@etiya.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 7+ messages in thread
From: Yavuz Selim Sertoðlu (ETIYA) @ 2019-10-09 14:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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 <tgl@sss.pgh.pa.us>
Sent: 09 October 2019 16:57
To: Yavuz Selim Sertoðlu (ETIYA) <yavuz.sertoglu@etiya.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Query slows when used with view
=?iso-8859-9?Q?Yavuz_Selim_Serto=F0lu_=28ETIYA=29?= <yavuz.sertoglu@etiya.com> 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&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:yavuz.sertoglu@etiya.com<mailto:yavuz.sertoglu@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<www.etiya.com/gizlilik;
ÇIKTI ALMADAN ÖNCE ÇEVREYE OLAN SORUMLULUGUMUZU BIR KEZ DAHA DÜSÜNELIM.
PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING ANY DOCUMENT.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query slows when used with view
@ 2019-10-09 15:23 Michael Lewis <mlewis@entrata.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: Michael Lewis @ 2019-10-09 15:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Yavuz Selim Sertoğlu (ETIYA) <yavuz.sertoglu@etiya.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
>
> 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?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query slows when used with view
@ 2019-10-09 15:24 Jeff Janes <jeff.janes@gmail.com>
parent: =?iso-8859-9?Q?Yavuz_Selim_Serto=F0lu_=28ETIYA=29?= <yavuz.sertoglu@etiya.com>
0 siblings, 1 reply; 7+ messages in thread
From: Jeff Janes @ 2019-10-09 15:24 UTC (permalink / raw)
To: Yavuz Selim Sertoğlu (ETIYA) <yavuz.sertoglu@etiya.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On Wed, Oct 9, 2019 at 10:56 AM Yavuz Selim Sertoğlu (ETIYA) <
yavuz.sertoglu@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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query slows when used with view
@ 2019-10-09 15:32 Michael Lewis <mlewis@entrata.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Michael Lewis @ 2019-10-09 15:32 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Yavuz Selim Sertoğlu (ETIYA) <yavuz.sertoglu@etiya.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
>
> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query slows when used with view
@ 2019-10-09 16:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Lewis <mlewis@entrata.com>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2019-10-09 16:07 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; +Cc: Jeff Janes <jeff.janes@gmail.com>; Yavuz Selim Sertoğlu (ETIYA) <yavuz.sertoglu@etiya.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Michael Lewis <mlewis@entrata.com> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2019-10-09 16:07 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-10-09 12:31 Query slows when used with view =?iso-8859-9?Q?Yavuz_Selim_Serto=F0lu_=28ETIYA=29?= <yavuz.sertoglu@etiya.com>
2019-10-09 13:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-10-09 14:55 ` =?iso-8859-9?Q?Yavuz_Selim_Serto=F0lu_=28ETIYA=29?= <yavuz.sertoglu@etiya.com>
2019-10-09 15:24 ` Jeff Janes <jeff.janes@gmail.com>
2019-10-09 15:32 ` Michael Lewis <mlewis@entrata.com>
2019-10-09 16:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-10-09 15:23 ` Michael Lewis <mlewis@entrata.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox