Обсуждение: Will the optimizer eliminate an unnecessary sort?

I'm using postgres 7.4.6 and I've created a table as follows:

    create table t(dh int, fh int, fn int, x bytea, ...)
    create index idx_dh_fh on t(dh, fh);

I have this query:

    select *
    from t
    where dh = 1
    and fn > 1000
    and x = 'abc'
    order by fn;

The optimizer uses the index, filters, and then sorts:

    ris=# explain select * from t where dh = 1 and x = 'abc' and fn > 1000 order
by fn;
                                            QUERY PLAN

-------------------------------------------------------------------------------------------
     Sort  (cost=651.24..651.25 rows=2 width=195)
       Sort Key: fn
       ->  Index Scan using idx_dh_fh on t  (cost=0.00..651.23 rows=2
width=195)
             Index Cond: (dh = 1)
             Filter: ((x = 'abc'::bytea) AND (fn > 1000))
    (5 rows)

So far so good. I was hoping to avoid the sort by adding another index:

    create index idx_dh_fn on t(dh, fn);

The lookup on dh can use the new index. The inequality on fn should
also be able to use the index. The sort should then be unnecessary because
the index will already have the fn values in order for the given value
of dh.

But (after running vacuum analyze), the optimizer is still generating
a plan with a sort:

    ris=# explain select * from t where dh = 1 and x = 'abc' and fn > 1000 order
by fn;
                                           QUERY PLAN

-----------------------------------------------------------------------------------------
     Sort  (cost=604.06..604.06 rows=1 width=195)
       Sort Key: fn
       ->  Index Scan using idx_dh_fn on t  (cost=0.00..604.05 rows=1
width=195)
             Index Cond: (dh = 1)
             Filter: ((x = 'abc'::bytea) AND (fn > 1000))
    (5 rows)

Is the execution plan I'm hoping for correct?

Is it something in the postgres optimizer's repertoire?

The user of this query is interested in only the first few rows. So
not only is the sort unnecessary, but all qualifying rows have to be
accessed before the sort. If the optimizer is not going to produce the
plan I want, is there some other way to achieve the desired effect?

Jack Orenstein


----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.