Tom Lane wrote:
>
> mlw <markw@mohawksoft.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.)
The table is pretty big, and I was performing the query with a binary cursor.
It really did take a little while to get results. I was using the query to
perform data analysis on a table. (If you are familiar with NetPerceptions,
think something like that)
The application framework, without any extra processing, executed the entire
query with a sequential scan in about 4 minutes, it performed the index scan in
about 34 minutes. The analysis app, takes about two hours to run with the
sequential scan.
So you are very right, it is much more efficient to run the sequential scan for
the whole table.