>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
Tom> FWIW, you don't need a subselect here anymore; it should work as well
Tom> (or as poorly) to do "where stamp > (now() - '1 hour' :: interval)".
Ahh. In 7.2, that was seen as a "calculate a lot" expression, so
I ended up with a seq scan. The subselect kludge was enough for it
to compute it once, and figure out that it could use the index.
>> In 7.3.2, I get a sequential scan! Ugh! It takes about 15 seconds.
Tom> Can you force an indexscan by doing "set enable_seqscan to off"?
Yes. That does it, but of course, I'd rather not change global status
thingies that way. :)
Tom> If not, the problem is likely a datatype mismatch as Josh suggested.
Tom> If you can, then the problem is that the planner doesn't think this is
Tom> selective enough to justify an indexscan. (I'm kinda surprised that
Tom> 7.2 wouldn't have thought the same.) The difficulty is that since
Tom> now() isn't a constant, the planner doesn't know what value the stamp
Tom> column will get compared to, and so it has to fall back on a default
Tom> selectivity estimate that will not be favorable to an indexscan.
So this changed between 7.2 and 7.3?
What's odd is that even writing a function didn't help:
add function ago(interval) returns timestamp with time zone
stable
language 'sql'
as 'select now() - $1';
I thought the addition of the "stable" keyword would make the return
value be the same as a constant. It's not enough, apparently. Is
that a bug, that a stable function and a constant are planned
differently?
Tom> If that's your problem, the answer is to add a dummy condition to turn
Tom> the query into a range scan. This should work:
Tom> where stamp > (now() - '1 hour'::interval)
Tom> and stamp < (now() + '1 hour'::interval);
Tom> The planner still doesn't know the selectivity of the now() conditions,
Tom> but its default estimate for a range query is lots tighter than for
Tom> a one-sided inequality. It should be willing to indexscan this way.
Ahh! It does!
explain select stamp from requests where stamp between now() - '1 hour'::interval and now();
does in fact give me an index scan by default.
Thanks for the workaround. This planner stuff always seems like black
magic. :)
--
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!
