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 по дате отправления: