Re: Query planner, 7.2b1 select ... order by

mlw <markw(at)mohawksoft(dot)com> writes:
> My problem is, if you do a select by the user name, it does an index
> scan. If you do a select from the whole table, ordered by the user
> name, it does a sequential scan not an index scan. It is arguable that
> this may be a faster query, but at the cost of many more resources and
> a very long delay before any results are returned. Is this the best
> behavior?

Unless you use a LIMIT, preferring the cheapest total cost still seems
like a win to me. (libpq, at least, doesn't give back any results till
the whole query has been retrieved, so the claims of "higher cost" and
"long delay till first result" are both specious.)

If you do use a LIMIT, that affects the plan choice.

If you use a cursor, things get more interesting, since the planner
has no way to know how much of the query you intend to retrieve,
nor whether you'd be willing to sacrifice total time for fast initial
response on the first few rows. Currently it's set to optimize
plans for cursors on the basis of assuming that 10% of the total rows
will be fetched. Given the more-than-10X discrepancy between seqscan
and indexscan costs in your example, that'll probably still give you
the seqscan choice. Hiroshi suggested making this fraction be a
user-settable parameter, which seems like a good idea to me but we
haven't gotten around to it yet.

regards, tom lane