Re: [HACKERS] Index scan?

Theo Kramer <theo@flame.co.za> writes:
> Does anyone know why the following occurs?
>   coza=> explain select * from accounts where domain >= '%' order by domain;
>   NOTICE:  QUERY PLAN:

>   Index Scan using domain_idx on accounts  (cost=1434.50 rows=19611 width=106)

> and
>   coza=> explain select * from accounts order by domain;
>   NOTICE:  QUERY PLAN:

>   Sort  (cost=3068.39 rows=58830 width=106)
>     -> Seq Scan on accounts  (cost=3068.39 rows=58830 width=106)

> Surely both queries give the same result set, yet the second example does not
> use the index causing unnecessary overhead.

Yeah, this is a known limitation of the planner: it's only bright enough
to skip an explicit sort step for an ORDER BY clause when the plan that
*would be chosen anyway in the absence of ORDER BY* happens to produce
a properly sorted result.  In your first example the WHERE clause can
be exploited to scan only part of the index (notice the difference in
estimated output row counts), so an indexscan gets chosen --- and that
just happens to deliver the sorted result you want.  In the second
example the plan-picker sees no reason to use anything more expensive
than a sequential scan :-(

We need to push awareness of the output ordering requirement down into
the code that chooses the basic plan.  It's on the TODO list (or should
be) but I dunno when someone will get around to it.
        regards, tom lane