pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #1797: Problem using Limit in a function, seqscan
3+ messages / 3 participants
[nested] [flat]
* BUG #1797: Problem using Limit in a function, seqscan
@ 2005-07-29 12:52 Magno Leite <magnomilk@yahoo.com.br>
0 siblings, 2 replies; 3+ messages in thread
From: Magno Leite @ 2005-07-29 12:52 UTC (permalink / raw)
To: pgsql-bugs@postgresql.org
The following bug has been logged online:
Bug reference: 1797
Logged by: Magno Leite
Email address: magnomilk@yahoo.com.br
PostgreSQL version: 8.0
Operating system: Windows XP Professional Edition
Description: Problem using Limit in a function, seqscan
Details:
I looked for about this problem in BUG REPORT but I can't find. This is my
problem, when I try to use limit in a function, the Postgre doesn't use my
index, then it use sequencial scan. What is the problem ?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #1797: Problem using Limit in a function, seqscan
@ 2005-07-29 14:00 Michael Fuhr <mike@fuhr.org>
parent: Magno Leite <magnomilk@yahoo.com.br>
1 sibling, 0 replies; 3+ messages in thread
From: Michael Fuhr @ 2005-07-29 14:00 UTC (permalink / raw)
To: Magno Leite <magnomilk@yahoo.com.br>; +Cc: pgsql-bugs@postgresql.org
On Fri, Jul 29, 2005 at 01:52:45PM +0100, Magno Leite wrote:
> I looked for about this problem in BUG REPORT but I can't find. This is my
> problem, when I try to use limit in a function, the Postgre doesn't use my
> index, then it use sequencial scan. What is the problem ?
Without more information we can only guess, but if you're using
PL/pgSQL then a cached query plan might be responsible. Here's an
excerpt from the PREPARE documentation:
In some situations, the query plan produced for a prepared
statement will be inferior to the query plan that would have
been chosen if the statement had been submitted and executed
normally. This is because when the statement is planned and
the planner attempts to determine the optimal query plan, the
actual values of any parameters specified in the statement are
unavailable. PostgreSQL collects statistics on the distribution
of data in the table, and can use constant values in a statement
to make guesses about the likely result of executing the
statement. Since this data is unavailable when planning prepared
statements with parameters, the chosen plan may be suboptimal.
If you'd like us to take a closer look, then please post a self-
contained example, i.e., all SQL statements that somebody could
load into an empty database to reproduce the behavior you're seeing.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #1797: Problem using Limit in a function, seqscan
@ 2005-07-29 14:06 Bruno Wolff III <bruno@wolff.to>
parent: Magno Leite <magnomilk@yahoo.com.br>
1 sibling, 0 replies; 3+ messages in thread
From: Bruno Wolff III @ 2005-07-29 14:06 UTC (permalink / raw)
To: Magno Leite <magnomilk@yahoo.com.br>; +Cc: pgsql-bugs@postgresql.org; pgsql-performance
On Fri, Jul 29, 2005 at 13:52:45 +0100,
Magno Leite <magnomilk@yahoo.com.br> wrote:
>
> Description: Problem using Limit in a function, seqscan
>
> I looked for about this problem in BUG REPORT but I can't find. This is my
> problem, when I try to use limit in a function, the Postgre doesn't use my
> index, then it use sequencial scan. What is the problem ?
You haven't described the problem well enough to allow us to help you and
you posted it to the wrong list. This should be discussed on the performance
list, not the bug list.
It would help if you showed us the query you are running and run it outside
of the function with EXPLAIN ANALYSE and show us that output. Depending
on what that output shows, we may ask you other questions.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-07-29 14:06 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-29 12:52 BUG #1797: Problem using Limit in a function, seqscan Magno Leite <magnomilk@yahoo.com.br>
2005-07-29 14:00 ` Michael Fuhr <mike@fuhr.org>
2005-07-29 14:06 ` Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox