Re: Why sequential scan when there's a supporting index?

Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net> writes:
> test2=# explain select max(tx_date) from t_lane_tx;
> NOTICE: QUERY PLAN:

> Aggregate (cost=2209999.20..2209999.20 rows=1 width=4)
> -> Seq Scan on t_lane_tx (cost=0.00..2125170.96 rows=33931296
> width=4)

I'm beginning to think this should be in the FAQ ...

Try it like this:

regression=# explain select unique1 from tenk1 order by unique1 desc limit 1;

Limit (cost=0.00..0.11 rows=1 width=4)
-> Index Scan Backward using tenk1_unique1 on tenk1 (cost=0.00..1071.99 rows=10000 width=4)

Although the LIMIT clause isn't standard, this approach is attractive
compared to max() because you can fetch any or all values in the row
containing the maximal element, which is a very useful thing. Also,
the approach scales to situations where you want to sort by multiple
columns.

Improving the handling of max() has been on the TODO list for awhile,
but most of the hacker community considers it low priority because of
the availability of the above workaround. Also, Postgres has a very
generalized black-box approach to aggregate functions, so no one's been
able to think of a reasonably clean way to teach the planner that some
aggregates are connected to index sort ordering.

regards, tom lane