Taking a complete shot in the dark here, but I did read that in the upgrade
from 7.2.x -- 7.3 the type timestamp was changed to default from
timestamp with time zone
to
timestamp without time zone
to be more adhesive to SQL standards....
and now() is of type timestamp with time zone, and if you only declared your
field of type timestamp it will be timestamp without time zone, therefore the
database may be converting the value each time therefore causing a speed
reduction?
The fast that constant timestamp works seems to further prove my theory since
then you have timestamp without time zone.
I'd be interested to know if this is the solution :)
Josh.
On March 14, 2003 02:47 am, Randal L. Schwartz wrote:
> I upgraded from 7.2.1 to 7.3.2 over the past weekend. One of my
> favorite queries went from an indexed scan to a sequential scan. Ugh.
>
> Here's the details... 200,000+ records, indexed on "stamp" (a timestamp).
> My query was:
>
> select count(*), otherthing from requests
> where stamp > (select now() - '1 hour' :: interval)
> group by 2
> order by 1 desc
> limit 10;
>
> In 7.2.1, the subselect was treated as a constant, and so the btree
> index was used to reduce the scan significantly. I would get the
> result within a few seconds.
>
> In 7.3.2, I get a sequential scan! Ugh! It takes about 15 seconds.
>
> However, if I replace the subselect with a constant timestamp
> ('2003-03-10'), I get an index scan, so it isn't that the analyze
> hasn't recognized the number of records.
>
> Did I break something during the upgrade?
>
> Is there anything I can do to get the index scan back?
>
> Is this a known change between 7.2.1 and 7.3.2?
