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

Re: [PERFORM] Poor Performance for large queries

From: Richard Huxton <dev(at)archonet(dot)com>
To: John Meinel <john(at)johnmeinel(dot)com>
Cc: pgsql-hackers-win32 <pgsql-hackers-win32(at)postgresql(dot)org>,pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Poor Performance for large queries
Date: 2004-09-29 08:40:11
Message-ID: 415A74EB.9040308@archonet.com (view raw or flat)
Thread:
Lists: pgsql-hackers-win32pgsql-performance
John Meinel wrote:
> 
> So notice that when doing the actual select it is able to do the index 
> query. But for some reason with a prepared statement, it is not able to 
> do it.
> 
> Any ideas?

In the index-using example, PG knows the value you are comparing to. So, 
it can make a better estimate of how many rows will be returned. With 
the prepared/compiled version it has to come up with a plan that makes 
sense for any value.

If you look back at the explain output you'll see PG is guessing 181,923 
rows will match with the prepared query but only 1 for the second query. 
If in fact you returned that many rows, you wouldn't want to use the 
index - it would mean fetching values twice.

The only work-around if you are using plpgsql functions is to use 
EXECUTE to make sure your queries are planned for each value provided.

-- 
   Richard Huxton
   Archonet Ltd

In response to

Responses

pgsql-performance by date

Next:From: Steven RosensteinDate: 2004-09-29 13:28:48
Subject: Re: This query is still running after 10 hours...
Previous:From: John MeinelDate: 2004-09-29 06:34:07
Subject: Re: Poor Performance for large queries in functions

pgsql-hackers-win32 by date

Next:From: John MeinelDate: 2004-09-29 14:56:27
Subject: Re: [PERFORM] Poor Performance for large queries
Previous:From: John MeinelDate: 2004-09-29 06:34:07
Subject: Re: Poor Performance for large queries in functions

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