Re: constant scalar subselect no longer equivalent to constant?

>>>>> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(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!