Re: constant scalar subselect no longer equivalent to constant?

merlyn(at)stonehenge(dot)com (Randal L. Schwartz) writes:
> 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;

FWIW, you don't need a subselect here anymore; it should work as well
(or as poorly) to do "where stamp > (now() - '1 hour' :: interval)".

> In 7.3.2, I get a sequential scan! Ugh! It takes about 15 seconds.

Can you force an indexscan by doing "set enable_seqscan to off"?
If not, the problem is likely a datatype mismatch as Josh suggested.
If you can, then the problem is that the planner doesn't think this is
selective enough to justify an indexscan. (I'm kinda surprised that
7.2 wouldn't have thought the same.) The difficulty is that since
now() isn't a constant, the planner doesn't know what value the stamp
column will get compared to, and so it has to fall back on a default
selectivity estimate that will not be favorable to an indexscan.

If that's your problem, the answer is to add a dummy condition to turn
the query into a range scan. This should work:
where stamp > (now() - '1 hour'::interval)
and stamp < (now() + '1 hour'::interval);
The planner still doesn't know the selectivity of the now() conditions,
but its default estimate for a range query is lots tighter than for
a one-sided inequality. It should be willing to indexscan this way.

regards, tom lane