Re: possible wrong query plan on pg 8.3.5,

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.