pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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"...



view thread (17+ messages)  latest in thread

Message-ID: <opsg57tnztcq72hf@musicbox>
Permalink:  ../opsg57tnztcq72hf@musicbox/
Also on:    postgresql.org/message-id/opsg57tnztcq72hf@musicbox

 · 

reply

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