X-Original-To: pgsql-admin@postgresql.org
Received: from localhost (developer.postgresql.org [64.117.224.193])
	by developer.postgresql.org (Postfix) with ESMTP id 3D122925C20
	for <pgsql-admin@postgresql.org>; Thu, 29 May 2003 08:24:21 -0400 (EDT)
Received: from developer.postgresql.org ([64.117.224.193])
	by localhost (developer.postgresql.org [64.117.224.193:10024])
	(amavisd-new)
	with ESMTP id 37331-05 for <pgsql-admin@postgresql.org>;
	Thu, 29 May 2003 08:24:10 -0400 (EDT)
Received: from Europa.go.com.mt (unknown [194.158.48.18])
	by developer.postgresql.org (Postfix) with ESMTP id 80298925C1F
	for <pgsql-admin@postgresql.org>; Thu, 29 May 2003 08:24:09 -0400 (EDT)
To: tgl@sss.pgh.pa.us
Cc: pgsql-admin@postgresql.org
Subject: Re: After VACUUM, statistics become skewed
MIME-Version: 1.0
From: Robert.Farrugia@go.com.mt
Date: Thu, 29 May 2003 14:24:06 +0200
Content-Type: multipart/alternative;
	boundary="=_alternative 00441067C1256D35_="
Message-Id: <20030529122409.80298925C1F@developer.postgresql.org>
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200305/397
X-Sequence-Number: 8959

This is a multipart message in MIME format.
--=_alternative 00441067C1256D35_=
Content-Type: text/plain; charset="us-ascii"

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



--=_alternative 00441067C1256D35_=
Content-Type: text/html; charset="us-ascii"


<br><font size=2 face="sans-serif">Sorry Tom for the late reply but I was off for the past few days.</font>
<br>
<br><font size=2 face="sans-serif">The definitions of the indexes are:</font>
<br>
<br><font size=2 face="sans-serif">mo_200302_calling_idx uses (answertime::timestamp, callingnumber_type::char(1), callingnumber_value::varchar) in this order</font>
<br><font size=2 face="sans-serif">mo_200302_called_idx uses (answertime::timestamp, callednumber_type::char(1), callednumber_value::varchar) in this order</font>
<br>
<br><font size=2 face="sans-serif">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. &nbsp;Is there a way to force the planner to use a particular index ?</font>
<br><font size=2 face="sans-serif"><br>
Regards<br>
Robert</font>
<br>
<br>
<br>
<br>
<table width=100%>
<tr valign=top>
<td>
<td><font size=1 face="sans-serif"><b>Tom Lane &lt;tgl@sss.pgh.pa.us&gt;</b></font>
<p><font size=1 face="sans-serif">25/05/2003 06:12</font>
<br>
<td><font size=1 face="Arial">&nbsp; &nbsp; &nbsp; &nbsp; </font>
<br><font size=1 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; To: &nbsp; &nbsp; &nbsp; &nbsp;Robert.Farrugia@go.com.mt</font>
<br><font size=1 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; cc: &nbsp; &nbsp; &nbsp; &nbsp;pgsql-admin@postgresql.org</font>
<br><font size=1 face="sans-serif">&nbsp; &nbsp; &nbsp; &nbsp; Subject: &nbsp; &nbsp; &nbsp; &nbsp;Re: [ADMIN] After VACUUM, statistics become skewed</font></table>
<br>
<br>
<br><font size=2 face="Courier New">Robert.Farrugia@go.com.mt writes:<br>
&gt; Running a slightly modified query i.e. removing the in and using an <br>
&gt; equality, the planner uses the &quot;correct&quot; index this time.<br>
<br>
Hm. &nbsp;What were the definitions of the two indexes, exactly?<br>
<br>
There is an asymmetry in the planner's handling of AND/OR clauses that<br>
can cause it to fail to recognize that a multicolumn index is usable,<br>
depending on whether the OR clause (the IN part of your query) applies<br>
to the first index column or a later one. &nbsp;I'm not sure if this relates<br>
to your problem or not, though...<br>
<br>
 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; regards, tom lane<br>
</font>
<br>
<br>
--=_alternative 00441067C1256D35_=--