Re: query planning different in plpgsql?

On Fri, Oct 23, 2009 at 11:38 AM, Michal J. Kubski <michal(dot)kubski(at)cdt(dot)pl>wrote:

>
>
> Hi,
>
> Is there any way to get the query plan of the query run in the stored
> procedure?
> I am running the following one and it takes 10 minutes in the procedure
> when it is pretty fast standalone.
>
> Any ideas would be welcome!
>
> # EXPLAIN ANALYZE SELECT m.domain_id, nsr_id FROM nsr_meta m, last_snapshot
> l WHERE m.domain_id = l.domain_id;
>
> QUERY PLAN
>
>
> ------------------------------------------------------------------------------------------------------------------------------------------------------------
> Nested Loop (cost=0.00..562432.32 rows=12227848 width=16) (actual
> time=1430.034..7476.081 rows=294418 loops=1)
> -> Seq Scan on last_snapshot l (cost=0.00..3983.68 rows=60768 width=8)
> (actual time=0.010..57.304 rows=60641 loops=1)
> -> Index Scan using idx_nsr_meta_domain_id on nsr_meta m
> (cost=0.00..6.68 rows=201 width=16) (actual time=0.111..0.115 rows=5
> loops=60641)
> Index Cond: (m.domain_id = l.domain_id)
> Total runtime: 7635.625 ms
> (5 rows)
>
> Time: 7646.243 ms
>

Do you not have an index on last_snapshot.domain_id?

--Scott