Re: strange pg_stats behaviour?

Hubert depesz Lubaczewski <depesz(at)depesz(dot)pl> writes:
> # explain analyze SELECT aid, foto FROM auction WHERE data_off < now() -
> # '31 days'::interval;
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------
> Seq Scan on auction (cost=0.00..14985.38 rows=9493 width=11) (actual
> time=21.33..1252.29 rows=1293 loops=1)
> Filter: (data_off < (now() - '31 days'::interval))
> Total runtime: 1253.61 msec
> (3 rows)

The planner doesn't know what value data_off will be compared to at
runtime, so it has to fall back on a default selectivity estimate.
Increasing the amount of stats data won't help in the slightest.

A cheat I've occasionally suggested for this is to define a function
like

create function ago(interval) returns timestamptz as
'select now() - $1' language sql immutable strict;

Then an expression like "WHERE data_off < ago('31 days')" will be
indexable because the ago() expression will be constant-folded at
the start of planning. However, this is a cheat because ago() is
*not* really immutable --- you will likely get burnt if you try to use
this technique for queries inside plpgsql functions, for example.

I don't know a good way to solve this problem in the general case.
I'm not willing to make the default selectivity estimate for a one-sided
inequality be low enough to provoke an indexscan; that's just asking for
trouble, because the query could easily be fetching much or all of the
table.

Another workaround that you could look at is

SELECT ...
WHERE data_off < now() - '31 days'::interval AND
data_off > '-infinity';

The extra clause doesn't hurt your results, and the default selectivity
estimate for a range-bounded query *is* small enough to provoke an
indexscan (in most cases, anyway).

regards, tom lane