From: Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
To: pgsql-performance@postgresql.org
Subject: Re: vacuum analyze slows sql query
Date: Tue, 09 Nov 2004 01:04:25 +0100
Message-ID: <opsg57tnztcq72hf@musicbox> (raw)
In-Reply-To: <20041108231951.23927.qmail@web52105.mail.yahoo.com>
References: <20041108231951.23927.qmail@web52105.mail.yahoo.com>
>> Lets say for a second that you manage to trick it into using index scan,
>> and then you actually call the function with one of the values that
>> returns 1,000s of rows. Probably it will take 10-100 times longer than
>> if it used a seq scan.
> I don't know if it matters (I suspect that it does) but I am using
> LIMIT 1 in the sub-query/stored function. All I need is one single
> row meeting any of the criteria laid out in the stored procedure to
> establish an offer_id is "pending".
So, in your case if you LIMIT the index scan will always be fast, and the
seq scan will be catastrophic, because you don't need to retrieve all the
rows, but just one. (IMHO the planner screws these LIMIT clauses becauses
it expects the data to be randomly distributed in the first page while in
real life it's not).
You could use EXIST to test the existence of a subquery (after all, thats
its purpose), or you could :
When SELECT ... FROM table WHERE stuff=value LIMIT 1
obstinately uses a seq scan, spray a little order by :
When SELECT ... FROM table WHERE stuff=value ORDER BY stuff LIMIT 1
the ORDER BY will make the planner think "I could use the index to
order"...
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@boutiquenumerique.com
Subject: Re: vacuum analyze slows sql query
In-Reply-To: <opsg57tnztcq72hf@musicbox>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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