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?  (Joshua Moore-Oliva <josh@chatgris.com>)
Re: constant scalar subselect no longer equivalent to constant?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: James Gregory
Дата:
Сообщение: Re: type-casting and LIKE queries
Следующее
От: Joshua Moore-Oliva
Дата:
Сообщение: Re: constant scalar subselect no longer equivalent to constant?