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

query planning different in plpgsql?

From: Michal J(dot) Kubski <michal(dot)kubski(at)cdt(dot)pl>
To: pgsql-performance(at)postgresql(dot)org
Subject: query planning different in plpgsql?
Date: 2009-10-23 15:38:04
Message-ID: a99402025047afbcc210461b220d9b64@localhost (view raw or flat)
Thread:
Lists: pgsql-performance

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

Many thanks,
Michal

-- 
I hear and I forget. I see and I believe. I do and I understand.
(Confucius)


Responses

pgsql-performance by date

Next:From: Scott MeadDate: 2009-10-23 15:49:50
Subject: Re: query planning different in plpgsql?
Previous:From: Richard HuxtonDate: 2009-10-23 14:30:56
Subject: Re: Queryplan within FTS/GIN index -search.

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