Home > mailing lists

Thread: Query slows when used with view

Query slows when used with view

From

Yavuz Selim Sertoğlu (ETIYA)

Date:

09 October 2019, 12:31:40

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]));

body { background-color: #fff; } table { border-spacing: 1px; border-collapse: collapse; } td img {} .details { font-family: Arial Narrow; font-size: 10px; font-weight: bold; color: #69737a; padding-bottom: 5px; } .share { text-align: right; } .share img { margin-left: 10px; }

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

Üniversiteler Mahallesi 1606.cadde No:4 Cyberpark C Blok Zemin kat ofis no :Z25A-Z44

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

From

Tom Lane

Date:

09 October 2019, 13:57:01

=?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

RE: Query slows when used with view

From

Yavuz Selim Sertoğlu (ETIYA)

Date:

09 October 2019, 14:55:58

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.

Re: Query slows when used with view

From

Michael Lewis

Date:

09 October 2019, 15:23:14

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

From

Jeff Janes

Date:

09 October 2019, 15:24:00

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

Re: Query slows when used with view

From

Michael Lewis

Date:

09 October 2019, 15:32:37

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

From

Tom Lane

Date:

09 October 2019, 16:07:47

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