Robert Haas wrote:
On Fri, May 29, 2009 at 5:57 PM, Anne Rosset <arosset@xxxxxxxxxx> wrote:
Robert Haas wrote:
On Thu, May 28, 2009 at 6:46 PM, Anne Rosset <arosset@xxxxxxxxxx> 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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
