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

Re: Unexpected query plan results

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Anne Rosset <arosset(at)collab(dot)net>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Unexpected query plan results
Date: 2009-05-29 22:15:37
Message-ID: 603c8f070905291515y1a451ba1j13462e78864ece6@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Fri, May 29, 2009 at 5:57 PM, Anne Rosset <arosset(at)collab(dot)net> wrote:
> Robert Haas wrote:
>
>> On Thu, May 28, 2009 at 6:46 PM, Anne Rosset <arosset(at)collab(dot)net> wrote:
>>
>>>
>>>                                                ->  Index Scan using
>>> item_pk on item  (cost=0.00..176865.31 rows=97498 width=88) (actual
>>> time=117.304..2405.060 rows=71 loops=1)
>>>                                                      Filter: ((NOT
>>> is_deleted) AND ((folder_id)::text = 'tracker3641'::text))
>>>
>>
>> The fact that the estimated row count differs from the actual row
>> count by a factor of more than 1000 is likely the root cause of your
>> problem here.  You probably want to figure out why that's happening.
>> How many rows are in that table and what value are you using for
>> default_statistics_target?
>>
>> ...Robert
>>
>
> The table has 468173 rows and the value for default_statistics_target is
> 750.
> Anne

OK, that sounds good.  If you haven't run ANALYZE or VACUUM ANALYZE
recently, you should do that first and see if it fixes anything.
Otherwise, maybe there's a hidden correlation between the deleted
column and the folder_id column.  We can assess that like this:

SELECT SUM(1) FROM item WHERE NOT deleted;
SELECT SUM(1) FROM item WHERE folder_id = 'tracker3641';
SELECT SUM(1) FROM item WHERE folder_id = 'tracker3641' AND NOT deleted;

Can you try that and send along the results?

Thanks,

...Robert

In response to

Responses

pgsql-performance by date

Next:From: Brian CoxDate: 2009-05-29 23:43:28
Subject: autovacuum hung?
Previous:From: Anne RossetDate: 2009-05-29 21:57:30
Subject: Re: Unexpected query plan results

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