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?
--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!
