From: | Ron Mahoney <rmahoney(at)infotech-nj(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Questions with the planner |
Date: | 2002-03-21 15:19:09 |
Message-ID: | Pine.LNX.4.44.0203211013270.14999-100000@hoser.x.infotech-nj.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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]
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2002-03-21 16:04:32 | Re: [NOVICE] Conditional constraint? |
Previous Message | Nigel J. Andrews | 2002-03-21 14:10:08 | Re: Postmaster processes running out of control? |