Re: constant scalar subselect no longer equivalent to constant?

>>>>> "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!