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@sss.pgh.pa.us>
25/05/2003 06:12
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: > 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...