Re: After VACUUM, statistics become skewed

Поиск
Список
Период
Сортировка
От Robert.Farrugia@go.com.mt
Тема Re: After VACUUM, statistics become skewed
Дата
Msg-id 20030522075734.C53F29234B3@developer.postgresql.org
обсуждение исходный текст
Ответ на After VACUUM, statistics become skewed  (Robert.Farrugia@go.com.mt)
Ответы Re: After VACUUM, statistics become skewed
Список pgsql-admin
Дерево обсуждения

Hi Tom,

I delete the rows from the pg_statistics table and rerun the same query;  Here are the results.

NOTICE:  QUERY PLAN:
Aggregate  (cost=218105.86..218105.88 rows=1 width=12)
  ->  Group  (cost=218105.86..218105.87 rows=3 width=12)
        ->  Sort  (cost=218105.86..218105.86 rows=3 width=12)
              ->  Index Scan using mo_200302_calling_idx on mobileorig_200302  (cost=0.00..218105.84 rows=3 width=12)
EXPLAIN

As you can see it is still using the wrong index but the cost is less.

I am attaching the output of the pg_stats table for the table shown in the example.



Thanks
Robert


Tom Lane <tgl@sss.pgh.pa.us>

21/05/2003 18:51

       
        To:        Robert.Farrugia@go.com.mt
        cc:        pgsql-admin@postgresql.org
        Subject:        Re: [ADMIN] After VACUUM, statistics become skewed



Robert.Farrugia@go.com.mt writes:
> Given a table around 4GB in size containing millions of records, before
> vacuuming the following query used the correct index
> (mo_200302_called_idx) which is built on answertime and callednumber_type.

Could we see EXPLAIN ANALYZE results for both the good plan and the bad
plan, for the same query?  You're not giving us comparable results.

It would also be useful to show the pg_stats entries for the table.
I suspect that the planner is being fooled by some odd statistics of
your data, but we have not got any info here with which to speculate.

> Usually by dropping the table and reloading it from
> disk may solve the problem (it may take more than one try), but this is
> becoming unpractical due to the amount of data to restore each time.

You can get back to the un-analyzed state by deleting the rows in
pg_statistic for this table, viz
                delete from pg_statistic where starelid = (select oid from
                                 pg_class where relname = 'mobileorig_200302');
Grotty, but it sure beats dropping and reloading a big table.

                                                  regards, tom lane


Вложения

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

Предыдущее
От: "Mendola Gaetano"
Дата:
Сообщение: Primary key duplicated: NUMBER OF INDEX' TUPLES (21539) IS NOT THE SAME AS HEAP (18607)'
Следующее
От: "A.Bhuvaneswaran"
Дата:
Сообщение: Re: SECURITY