From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robert.Farrugia@go.com.mt
Cc: pgsql-admin@postgresql.org
Subject: Re: After VACUUM, statistics become skewed
Date: Thu, 29 May 2003 10:26:43 -0400
Message-ID: <17482.1054218403@sss.pgh.pa.us> (raw)
In-Reply-To: <20030529122409.80298925C1F@developer.postgresql.org>
References: <20030529122409.80298925C1F@developer.postgresql.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, Robert.Farrugia@go.com.mt
Subject: Re: After VACUUM, statistics become skewed
In-Reply-To: <17482.1054218403@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox