Skip site navigation (1) Skip section navigation (2)

Re: possible wrong query plan on pg 8.3.5,

From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: zz_11(at)mail(dot)bg
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-performance(at)postgresql(dot)org
Subject: Re: possible wrong query plan on pg 8.3.5,
Date: 2009-09-14 15:51:39
Message-ID: dcc563d10909140851p5d7f8b9qf6fc8274d7d94d70@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
2009/9/14  <zz_11(at)mail(dot)bg>:
> Also I waited to the end of this query to gather info for explain analyze.
> It is it:
>
>  explain analyze  select d.ids from a_doc d  join a_sklad s on
> (d.ids=s.ids_doc)  join a_nomen n on (n.ids=s.ids_num)  join a_nom_gr nmgr
> on (nmgr.ids=n.ids_grupa)  join a_gar_prod_r gr on (gr.ids_a_sklad=s.ids and
> gr.sernum!='ok')  join a_location l on (l.ids=s.ids_sklad)  join a_klienti
> kl on (kl.ids=d.ids_ko)  left outer join a_slujiteli sl on
> (sl.ids=d.ids_slu_ka)  left outer join a_slujiteli slu on
> (slu.ids=d.ids_slu_targ)  where d.op=1  AND d.date_op >= 12320 AND d.date_op
> <= 12362 and n.num like '191%';
>
>             QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Nested Loop Left Join  (cost=63.64..133732.47 rows=4 width=64) (actual
> time=616059.833..1314396.823 rows=91 loops=1)
>   ->  Nested Loop  (cost=63.64..133699.35 rows=4 width=128) (actual
> time=616033.205..1313991.756 rows=91 loops=1)
>         ->  Nested Loop  (cost=63.64..133688.22 rows=4 width=192) (actual
> time=616033.194..1313991.058 rows=91 loops=1)
>               ->  Nested Loop Left Join  (cost=63.64..133687.10 rows=4
> width=256) (actual time=616033.183..1313936.577 rows=91 loops=1)
>                     ->  Nested Loop  (cost=63.64..133685.78 rows=4
> width=320) (actual time=616033.177..1313929.258 rows=91 loops=1)
>                           ->  Nested Loop  (cost=63.64..133646.56 rows=6
> width=384) (actual time=616007.069..1313008.701 rows=91 loops=1)
>                                 ->  Nested Loop  (cost=63.64..127886.54
> rows=2833 width=192) (actual time=376.309..559763.450 rows=211357 loops=1)
>                                       ->  Nested Loop
>  (cost=63.64..107934.83 rows=13709 width=256) (actual
> time=224.058..148475.499 rows=370803 loops=1)
>                                             ->  Index Scan using i_nomen_num

This nested loop looks like the big problem, although it could also be
that it's running an index scan earlier that should be a seq scan
given the amount the estimate is off on rows.

For grins, try running your query after issuing this command:

set enable_nestloop=off;

and see what the run time looks like.

In response to

Responses

pgsql-performance by date

Next:From: zz_11Date: 2009-09-14 16:07:09
Subject: Re: possible wrong query plan on pg 8.3.5,
Previous:From: Robert HaasDate: 2009-09-14 15:30:03
Subject: Re: possible wrong query plan on pg 8.3.5,

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group