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

Re: BUG #1797: Problem using Limit in a function, seqscan

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Magno Leite <magnomilk(at)yahoo(dot)com(dot)br>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1797: Problem using Limit in a function, seqscan
Date: 2005-07-29 14:00:38
Message-ID: 20050729140038.GA83653@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-performance
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/

In response to

pgsql-performance by date

Next:From: Bruno Wolff IIIDate: 2005-07-29 14:06:42
Subject: Re: BUG #1797: Problem using Limit in a function, seqscan
Previous:From: Alvaro HerreraDate: 2005-07-29 13:23:19
Subject: Re: Performance problems testing with Spamassassin

pgsql-bugs by date

Next:From: Bruno Wolff IIIDate: 2005-07-29 14:06:42
Subject: Re: BUG #1797: Problem using Limit in a function, seqscan
Previous:From: neeraj chaudhariDate: 2005-07-29 13:46:54
Subject: error while executing gmake the pgsql 7.2.1 on redhat linux 9

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