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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers-win32 pgsql-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
From | Date | Subject | |
---|---|---|---|
Next Message | John Meinel | 2004-09-29 14:56:27 | Re: [PERFORM] Poor Performance for large queries |
Previous Message | John Meinel | 2004-09-29 06:34:07 | Re: Poor Performance for large queries in functions |
From | Date | Subject | |
---|---|---|---|
Next Message | Steven Rosenstein | 2004-09-29 13:28:48 | Re: This query is still running after 10 hours... |
Previous Message | John Meinel | 2004-09-29 06:34:07 | Re: Poor Performance for large queries in functions |