constant scalar subselect no longer equivalent to constant?
| От | merlyn@stonehenge.com (Randal L. Schwartz) |
|---|---|
| Тема | constant scalar subselect no longer equivalent to constant? |
| Дата | |
| Msg-id | 864r66tmo0.fsf@red.stonehenge.com обсуждение |
| Ответы |
Re: constant scalar subselect no longer equivalent to constant?
Re: constant scalar subselect no longer equivalent to constant? |
| Список | pgsql-general |
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!
В списке pgsql-general по дате отправления: