Search Postgresql Archives

Re: Slow query with join

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



>-----Message d'origine-----
>De : Tom Lane [mailto:tgl@xxxxxxxxxxxxx] 
>Envoyé : March-16-15 5:07 PM
>À : Tomas Vondra
>Cc : pgsql-general@xxxxxxxxxxxxxx; Marc Watson
>Objet : Re:  Slow query with join
>
>Tomas Vondra <tomas.vondra@xxxxxxxxxxxxxxx> writes:
>> On 16.3.2015 19:50, Marc Watson wrote:
>>> I hope someone can help me with a problem I'm having when joining a
>>> view with a table. The view is somewhat involved, but I can provide the
>>> details if necessary
>
>> First, get rid of the ORDER BY clauses in the subselects - it's
>> completely pointless, and might prevent proper optimization (e.g.
>> replacing the IN() with optimized joins.
>
>I'm suspicious that the cause may be an ORDER BY in the view.  It's
>hard to tell when we've not seen the view definition, but I see that both
>plans we've been shown are going to produce output sorted by actor.id.
>Maybe that's happenstance, or maybe not.
>
>			regards, tom lane

Thanks for replying.
First of all, there is an ORDER BY in the view - ORDER BY actor.id. Removing this, the query takes about 47 secs., which is also the same as if I simply do a select * from v_actor, with or without the ORDER BY in the view.
It is something in the view, which I will explore further. If I create a materialized view mv_actor, select * from mv_actor takes 8 sec and my original query using the materialized view  takes 16ms.

David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:
>You might want to consider whether the following is acceptable; but it would depend on the relationship between f_intervenant_ref and v_actor:
>
>SELECT *
>FROM v_actor
>JOIN f_intervenant_ref ON (actor_id = ir_actor_id)
>WHERE ir_dos_id = '5226'

The relationship between f_intervenant_ref and v_actor is simple. ir_actor_id is an integer that contains the value from the table actor.id, a serial and the primary key.  This gives the same 7 secs, or 49 secs with out the ORDER BY in the view. The explain analyze output (with the ORDER BY in the view) is :
 "Merge Join  (cost=54.78..108097.04 rows=8 width=1540) (actual time=6925.158..6962.631 rows=8 loops=1)"
"  Merge Cond: (actor.id = f_intervenant_ref.ir_actor_id)"
"  ->  Merge Left Join  (cost=0.85..554262.60 rows=20670 width=138) (actual time=2.840..6960.625 rows=3072 loops=1)"
"        Merge Cond: (actor.id = physical_actor.id)"
"        ->  Merge Left Join  (cost=0.57..1665.30 rows=20670 width=114) (actual time=0.014..3.791 rows=3072 loops=1)"
"              Merge Cond: (actor.id = moral_actor.id)"
"              ->  Index Scan using actor_pkey on actor  (cost=0.29..1275.50 rows=20670 width=78) (actual time=0.006..1.667 rows=3072 loops=1)"
"              ->  Index Scan using moral_actor_pkey on moral_actor  (cost=0.28..268.78 rows=5548 width=40) (actual time=0.005..0.005 rows=1 loops=1)"
"        ->  Index Scan using physical_actor_pkey on physical_actor  (cost=0.29..725.98 rows=15122 width=28) (actual time=0.003..3.072 rows=3072 loops=1)"
"        SubPlan 1"
"          ->  Limit  (cost=8.30..8.31 rows=1 width=8) (never executed)"
"                ->  Sort  (cost=8.30..8.31 rows=1 width=8) (never executed)"
"                      Sort Key: contact.rank"
"                      ->  Index Scan using contact_actor_idx on contact  (cost=0.28..8.29 rows=1 width=8) (never executed)"
"                            Index Cond: (actor_id = actor.id)"
"        SubPlan 2"
"          ->  Limit  (cost=8.30..8.31 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=3072)"
"                ->  Sort  (cost=8.30..8.31 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=3072)"
"                      Sort Key: contact_1.rank"
"                      Sort Method: quicksort  Memory: 25kB"
"                      ->  Index Scan using contact_actor_idx on contact contact_1  (cost=0.28..8.29 rows=1 width=8) (actual time=0.002..0.003 rows=0 loops=3072)"
"                            Index Cond: (actor_id = actor.id)"
"  ->  Index Scan using ir_actor_id_idx on f_intervenant_ref  (cost=0.28..62.58 rows=8 width=79) (actual time=0.386..0.449 rows=8 loops=1)"
"        Filter: ((ir_dos_id)::text = '5226'::text)"
"        Rows Removed by Filter: 891"
"Planning time: 0.864 ms"
"Execution time: 6962.773 ms"

>The other option is to use EXISTS:
>...
Same 7 secs.

A scaled-down version of my view runs quickly, so I'll gradually start inserting the missing pieces until I find the culprit. I'll let you know the cause when I find it.
Thx again,
Mark Watson



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux