Re: After VACUUM, statistics become skewed

Sorry Tom for the late reply but I was off for the past few days.

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 ?

Regards
Robert

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
25/05/2003 06:12

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

Robert(dot)Farrugia(at)go(dot)com(dot)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