Re: query optimization

> Sort  (cost=37.44..37.44 rows=1 width=1118) (actual
> time=12140.96..12141.02 rows=48 loops=1)
>   ->  Nested Loop  (cost=0.00..37.43 rows=1 width=1118) (actual
> time=115.02..12138.57 rows=48 loops=1)
>         ->  Nested Loop  (cost=0.00..33.42 rows=1 width=886) (actual
> time=0.58..20.77 rows=48 loops=1)
>               ->  Index Scan using i_sklad_ids_doc on a_sklad n
> (cost=0.00..28.58 rows=1 width=760) (actual time=0.36..8.59 rows=48
> loops=1)
>               ->  Index Scan using a_location_pkey on a_location sk
> (cost=0.00..4.82 rows=1 width=126) (actual time=0.09..0.12 rows=1
> loops=48)
>         ->  Seq Scan on a_nomen nom  (cost=0.00..3.45 rows=45 width=232)
> (actual time=0.03..165.45 rows=6702 loops=48)
> Total runtime: 12142.07 msec

It seems the query planner is completly wrong here, look on the line
Seq Scan on a_nomen nom  (cost=0.00..3.45 rows=45 width=232) (actual time=0.03..165.45 rows=6702 loops=48)

This means the planner expects 45 return rows (guessed from statistics), but actually gets 6702 rows.

Do "VACUUM ANALYZE a_nomen" and try your query again.

If it fails:
Do you have a unique index on a_nomen(ids)?

Regards,
    Mario Weilguni


>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html