David Rowley <dgrowleyml@gmail.com> writes:
> It feels like something is a bit lacking in our cost model here. I'm
> just not sure what that is.
The example you show is the same old problem that we've understood for
decades: for cost-estimation purposes, we assume that matching rows
are more or less evenly distributed in the table. Their actual
location doesn't matter that much if you're scanning the whole table;
but if you're hoping that a LIMIT will be able to stop after scanning
just a few rows, it does matter.
While it'd be pretty easy to insert some ad-hoc penalty into the
LIMIT estimation to reduce the chance of being fooled this way,
that would also discourage us from using fast-start plans when
they *do* help. So I don't see any easy fix.
regards, tom lane