Re: Questions with the planner - Mailing list pgsql-general

From Ron Mahoney
Subject Re: Questions with the planner
Date
Msg-id Pine.LNX.4.44.0203211013270.14999-100000@hoser.x.infotech-nj.com
Whole thread Raw
In response to Questions with the planner  (Orion Henry <orion@trustcommerce.com>)
List pgsql-general
I experienced the same thing. On the same platform.  Any calculation on
the timestamp caused the planner to not use the index on the timestamp
field and fall back to a seqscan of the table.  If I just put a timestamp
value there (no calculation) it used the index.  I tried casting the
results of the calc to timestamp too and that did not help.  I resorted to
calculating the dates in my code and just using a date with no calculation
in the query.

Anyone know if this has been fixed on 7.2?

>
> I was noticing something odd about the query planner.  I'm using
> postgresql-7.1.3-2 so if this has been fixed in a more recent version
> please let me know...
>
> Ok here's a plan for my query.  The meat is not so import its just that
> I am pulling data out of a really big table from March 11th to present.
> Now -- the query planner does the smart thing and uses the date index.
> So far so good.
[snip]
> Now the only difference here is that I add "+ 0" to the rval of the date.
> This makes the database lose its ability to use the date index.  In this
> case it fails over to a much less useful index.  But the question being...
> why does it not use the index here.  One thing that crossed my mind was that
> perhaps the addition makes it into a Date type... well casting it back to
> Timestamp fixes nothing.  Once I arithmetic on the timestamp I just cant
> find any way to use the index.  Maybe it's the math that messes it up...
> for reason thinking the result of a "+" is not cachable. ( yes I'm sure I
> can get around this by making a "iscachable" function and passing the
> timestamp through that...
[snip]


pgsql-general by date:

Previous
From: "Nigel J. Andrews"
Date:
Subject: Re: Postmaster processes running out of control?
Next
From: Stephan Szabo
Date:
Subject: Re: [NOVICE] Conditional constraint?