Re: Just trying to read first row in table based on index, pg scans and sorts instead? - Mailing list pgsql-novice

johnmudd <johnbmudd@gmail.com> writes:
> I populated my table, then added the following index. I want to read the
> first row based on this index but it takes 3 seconds to get the result. I
> hope there's a way to improve this, possibly take advantage of the index.

> Index:
>     "rx_storenbr_rxnbr_rfnbr_index" UNIQUE, btree (storenbr, rxnbr, rfnbr)

> reports=> EXPLAIN ANALYZE SELECT * FROM rx ORDER BY (storenbr,rxnbr,rfnbr)
> LIMIT 1;

Try it without the parentheses in the ORDER BY.  What you're ordering by
there is the expression ROW(storenbr,rxnbr,rfnbr), which does not match
the index.  (Maybe it should, but it doesn't.)

            regards, tom lane