Обсуждение: 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

On Wednesday 21 May 2003 7:07 pm, Q wrote:
> Can anyone tell me how to unsubscribe?
> I'm really sorry, I don't see any instructions anywhere.

One option is to go to http://developer.postgresql.org/mailsub.php,
choose the admin list, choose unsubscribe and enter your email address.

HTH

Gary
--
Gary Stainburn

This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000

Robert.Farrugia@go.com.mt writes:
> Here are the results.

I asked for EXPLAIN ANALYZE for both cases.  When you're worried about
planner estimates diverging from reality, it does not help to not know
what reality is.

            regards, tom lane

Robert.Farrugia@go.com.mt writes:
> Running a slightly modified query i.e. removing the in and using an
> equality, the planner uses the "correct" index this time.

Hm.  What were the definitions of the two indexes, exactly?

There is an asymmetry in the planner's handling of AND/OR clauses that
can cause it to fail to recognize that a multicolumn index is usable,
depending on whether the OR clause (the IN part of your query) applies
to the first index column or a later one.  I'm not sure if this relates
to your problem or not, though...

            regards, tom lane

Robert.Farrugia@go.com.mt writes:
> The definitions of the indexes are:

> mo_200302_calling_idx uses (answertime::timestamp,
> callingnumber_type::char(1), callingnumber_value::varchar) in this order
> mo_200302_called_idx uses (answertime::timestamp,
> callednumber_type::char(1), callednumber_value::varchar) in this order

> If I am understanding you well, the planner may ignore the index if the OR
> clause uses the second or third column of a multi-column index, even
> though the first column is used.  Is there a way to force the planner to
> use a particular index ?

In this particular case the problem is that the planner does not believe
the index is applicable.  The only way to make it think that is to
change the query or the index definition so that the OR clause (i.e.,
the IN) contains references to the first index column.  For example, you
could rewrite your query from its current effective form:

select ... where
    answertime between '2003/2/3 00:00:00' and '2003/2/14 23:59:59'
    and dialleddigits_value = '50043992'
    and (callednumber_type = 'P' or callednumber_type = 'M')

to something like

select ... where
    (answertime between '2003/2/3 00:00:00' and '2003/2/14 23:59:59'
     and dialleddigits_value = '50043992'
     and callednumber_type = 'P')
   OR
    (answertime between '2003/2/3 00:00:00' and '2003/2/14 23:59:59'
     and dialleddigits_value = '50043992'
     and callednumber_type = 'M')

which can be used for two indexscans on the first two columns of
mo_200302_called_idx.  (BTW, if this is a frequent query, maybe you
should have an index that includes dialleddigits_value as the third
column?)  You could factor out "and dialleddigits_value = '50043992'"
or not here, that doesn't matter; but the OR'd-together subclauses
have to all mention the leading term of the index, ie, answertime.

Plan B would be to leave the query alone and change the index column
order so that callednumber_type is first.  This is probably not a great
idea though, since answertime is a much better first key for most
purposes.

It's moderately annoying that the planner is not bright enough to figure
out this transformation for itself.  I suspect what is needed is a whole
fresh look at the OR-index processing algorithm; it seems excessively
complicated and yet it still doesn't do everything one would want.

            regards, tom lane