From: Robert.Farrugia@go.com.mt
To: tgl@sss.pgh.pa.us
Cc: pgsql-admin@postgresql.org
Subject: Re: After VACUUM, statistics become skewed
Date: Thu, 29 May 2003 14:24:06 +0200
Message-ID: <20030529122409.80298925C1F@developer.postgresql.org> (raw)
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...
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: Robert.Farrugia@go.com.mt, tgl@sss.pgh.pa.us
Subject: Re: After VACUUM, statistics become skewed
In-Reply-To: <20030529122409.80298925C1F@developer.postgresql.org>
* 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