Martin F <pg@mfriebe.de> writes:
> Index Cond: (tbl_foo.id IS NOT NULL)
> only "id" is the pk, and declared "not null".
> So why this index condition?
The IS NOT NULL condition is generated as part of transforming a "min(x)"
query into an indexscan, on the basis that "select min(x) from ..." is
equivalent to "select x from ... where x is not null order by x limit 1".
Without the NOT NULL check, that's an incorrect transformation.
You're right that we could observe that the NOT NULL is implied by a table
constraint and drop it, but it seems unlikely to be worth the planner
cycles to do so; the condition doesn't cost much at runtime. (Also,
plans that depend on table constraints for correctness have added
bookkeeping costs from tracking such dependency.)
> The select with filter choose an IMHO better plan
>> Index Only Scan using tbl_foo_date on public.tbl_foo
[ shrug... ] Can't get too excited about that. The first rule of working
with the Postgres planner is that planning results on toy tables do not
scale to large tables; too many of the cost factors are nonlinear.
But the bigger picture here, which would become more obvious if you were
working with a non-toy amount of data, is that you're asking the planner
to choose between two bad options. Basically it can either scan the data
in id order (using the pkey index) and stop when it hits the first row
satisfying the created_at condition (which might be arbitrarily far in);
or it can scan all the data satisfying the created_at condition (possibly
using the other index to optimize that) and remember the smallest id seen
while doing so. Your query with the aggregate FILTER condition is a
further-pessimized version of the second approach, because it has to scan
the *whole* index; a per-aggregate FILTER doesn't get applied as an index
condition, but only while executing that aggregate.
If you are concerned about the performance of this specific query shape,
what you actually want is an index on (id, created_at). That allows
stopping at the first index entry satisfying the created_at condition,
knowing that it must have the min id value that does so.
On a toy table the performance of any of these variants is going to be so
close that it's not certain which one the planner will pick (and it will
hardly matter anyway). On a large table the correctly-chosen index will
make an enormous difference.
regards, tom lane