> Having muttered about the primary key using two columns I see the planner can
> see the table size without having to revert to an index. Which makes sense if
> only I'd turned my brain on first.
The planner only gets the estimate from the last analyze/vacuum.
> Anyway, the question still stands, why does postgres do this query this
> way? It is doing the full sequential scan, i.e. fetching the tuples from
> disk, when this data is not necessary for the query result. Is it to do with
> calling requirement of count(), other aggregate functions and/or functions in
> general when used in the return list and/or that it requires too much
> intelligence for the system to determine such optimisations?
The reason is that it needs to visit those rows anyway to see whether they
are visible to your transaction. If you're visiting every row, sequential
scan is faster than index scan. If the index had the visibility
information this could be done purely by index, but there are problems
with doing that as well (I don't know them, but they're in messages on
this topic from the past).