Re: Unexpected query plan results

Список
Период
Сортировка
От Anne Rosset
Тема Re: Unexpected query plan results
Дата
Msg-id 4A241A97.4090402@collab.net
обсуждение исходный текст
Ответ на Re: Unexpected query plan results  (Robert Haas)
Ответы Re: Unexpected query plan results  (Robert Haas)
Список pgsql-performance
Дерево обсуждения
Unexpected query plan results  (Anne Rosset, )
 Re: Unexpected query plan results  ("Dave Dutcher", )
  Re: Unexpected query plan results  (Anne Rosset, )
   Re: Unexpected query plan results  ("Dave Dutcher", )
    Re: Unexpected query plan results  (Scott Mead, )
    Re: Unexpected query plan results  (Anne Rosset, )
     Re: Unexpected query plan results  (Robert Haas, )
 Re: Unexpected query plan results  (Robert Haas, )
  Re: Unexpected query plan results  (Anne Rosset, )
   Re: Unexpected query plan results  (Robert Haas, )
    Re: Unexpected query plan results  (Anne Rosset, )
     Re: Unexpected query plan results  (Robert Haas, )
      Re: Unexpected query plan results  (Anne Rosset, )
       Re: Unexpected query plan results  ("Dave Dutcher", )
        Re: Unexpected query plan results  (Anne Rosset, )
       Re: Unexpected query plan results  (Robert Haas, )
        Re: Unexpected query plan results  (Віталій Тимчишин, )
         Re: Unexpected query plan results  (Robert Haas, )
        Re: Unexpected query plan results  (Anne Rosset, )
         Re: Unexpected query plan results  (Robert Haas, )
Robert Haas wrote:

>On Fri, May 29, 2009 at 5:57 PM, Anne Rosset <> wrote:
>
>
>>Robert Haas wrote:
>>
>>
>>
>>>On Thu, May 28, 2009 at 6:46 PM, Anne Rosset <> 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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Robert Haas
Дата:
Сообщение: Re: Unexpected query plan results
Следующее
От: "Kevin Grittner"
Дата:
Сообщение: Re: Vacuuming technique doubt