Query slows when used with view

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Query slows when used with view

Yavuz Selim Sertoğlu (ETIYA)

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

 

 
 
Yavuz Selim Sertoğlu
Solution Support Specialist II
 
T:+90 312 265 01 50
M:+90 552 997 52 02
E:[hidden email]
 
Ü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.

Reply | Threaded
Open this post in threaded view
|

Re: Query slows when used with view

Tom Lane-2
=?iso-8859-9?Q?Yavuz_Selim_Serto=F0lu_=28ETIYA=29?= <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

RE: Query slows when used with view

Yavuz Selim Sertoğlu (ETIYA)
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 <[hidden email]>
Sent: 09 October 2019 16:57
To: Yavuz Selim Sertoğlu (ETIYA) <[hidden email]>
Cc: [hidden email]
Subject: Re: Query slows when used with view

=?iso-8859-9?Q?Yavuz_Selim_Serto=F0lu_=28ETIYA=29?= <[hidden email]> 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:[hidden email]<mailto:[hidden email]>

Ü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.


Reply | Threaded
Open this post in threaded view
|

Re: Query slows when used with view

Michael Lewis
In reply to this post by Tom Lane-2
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."

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?
Reply | Threaded
Open this post in threaded view
|

Re: Query slows when used with view

Jeff Janes
In reply to this post by Yavuz Selim Sertoğlu (ETIYA)


On Wed, Oct 9, 2019 at 10:56 AM Yavuz Selim Sertoğlu (ETIYA) <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: Query slows when used with view

Michael Lewis
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.
Reply | Threaded
Open this post in threaded view
|

Re: Query slows when used with view

Tom Lane-2
Michael Lewis <[hidden email]> 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