Re: Unexpected query plan results

Robert Haas wrote:

>On Fri, May 29, 2009 at 5:57 PM, Anne Rosset <arosset@collab.net> wrote:
>
>
>>Robert Haas wrote:
>>
>>
>>
>>>On Thu, May 28, 2009 at 6:46 PM, Anne Rosset <arosset@collab.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
>
>
Hi Robert,
we did a vacuum analyze and the results are the same.
Here are the results of the queries :

SELECT SUM(1) FROM item WHERE is_deleted = 'f'; sum --------- 1824592 (1
row)
SELECT SUM(1) FROM item WHERE folder_id = 'tracker3641
</sf/sfmain/do/go/tracker3641?returnUrlKey=1243878161701>'; sum --------
122412 (1 row)
SELECT SUM(1) FROM item WHERE folder_id = 'tracker3641
</sf/sfmain/do/go/tracker3641?returnUrlKey=1243878161701>' AND
is_deleted = 'f'; sum ----- 71 (1 row)
SELECT SUM(1) FROM item WHERE folder_id = 'tracker3641
</sf/sfmain/do/go/tracker3641?returnUrlKey=1243878161701>' AND
is_deleted = 't'; sum -------- 122341 (1 row)

Thanks for your help,
Anne