pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael McFarland <sidlonDoesntLikeSpam@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: Re: adding 'limit' leads to very slow query
Date: Wed, 09 Mar 2005 11:00:20 -0500
Message-ID: <opsndn2ujisvs6tg@localhost.localdomain> (raw)
References: <opsnaj0hctsvs6tg@localhost.localdomain>
	<20050307230123.L97147@megazone.bigpanda.com>

    I continue to be stumped by this.  You are right that I should have  
listed the estimates provided by explain... basically for the select where  
bar = 41, it's estimating there will be 40,000 rows instead of 7, out of  
what's actuallly 5 million records in the table.

    So far I've tried increase statistics for the bar column from the  
default 10 to 100 (vacuum analyzing after) and the explain-plan hasn't  
changed.  I also notice that afterward, the pg_stats record for the bar  
column still only lists the top 5 values of bar (out of 68 unique values  
in the table).  Are there any other settings I could try to improve the  
detail of the statistics?

    By the way, I think I do have a workaround for this particular query:
         select * from (select * from foo where barId = 412 order by id  
desc) as tempview limit 25;
This query uses the bar index and completes instantly.  However, I feel  
like I should find the heart of the problem, since bad statistics could  
end up affecting other plans, right?

  - Mike


On Mon, 7 Mar 2005 23:03:43 -0800 (PST), Stephan Szabo  
<sszabo@megazone.bigpanda.com> wrote:

> On Mon, 7 Mar 2005, Michael McFarland wrote:
>
>>    I'm trying to understand why a particular query is slow, and it seems
>> like the optimizer is choosing a strange plan.  See this summary:
>>
>>
>> * I have a large table, with an index on the primary key 'id' and on a
>> field 'foo'.
>> > select count(*) from foo;
>> 1,000,000
>> > select count(*) from foo where bar = 41;
>> 7
>>
>> * This query happens very quickly.
>> > explain select * from foo where barId = 412 order by id desc;
>> Sort ()
>>    Sort key= id
>>   ->   Index scan using bar_index on foo ()
>>      Index cond: barId = 412
>>
>> But this query takes forever
>>
>> > explain select * from foo where barId = 412 order by id desc limit 25;
>> Limit ()
>>   ->   Index scan backward using primarykey_index
>>    Filter:  barID = 412
>
> You didn't show the row estimates, but I'd guess that it's expecting
> either that ther are more rows that match barId=412 than there actually
> are (which may be solvable by raising the statistics target on the column
> and re-analyzing) such that going backwards on id in order to make 25
> matching rows isn't a bad plan or that barId and id are correlated which
> is unfortunately not going to be recognized right now.
>

-- 
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/



view thread (5+ messages)  latest in thread

Message-ID: <opsndn2ujisvs6tg@localhost.localdomain>
Permalink:  ../opsndn2ujisvs6tg@localhost.localdomain/
Also on:    postgresql.org/message-id/opsndn2ujisvs6tg@localhost.localdomain

 · 

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: sidlonDoesntLikeSpam@yahoo.com
  Subject: Re: adding 'limit' leads to very slow query
  In-Reply-To: <opsndn2ujisvs6tg@localhost.localdomain>

* 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