From: | merlyn(at)stonehenge(dot)com (Randal L(dot) Schwartz) |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | constant scalar subselect no longer equivalent to constant? |
Date: | 2003-03-14 07:47:11 |
Message-ID: | 864r66tmo0.fsf@red.stonehenge.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)stonehenge(dot)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!
From | Date | Subject | |
---|---|---|---|
Next Message | Joshua Moore-Oliva | 2003-03-14 08:00:00 | Re: constant scalar subselect no longer equivalent to constant? |
Previous Message | Mohd Toha Taifor | 2003-03-14 07:20:29 | Re: Is there any procedure to start postgre database server automatically |