Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: johnmudd <johnbmudd(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Just trying to read first row in table based on index, pg scans and sorts instead?
Date: 2012-04-19 22:11:44
Message-ID: 17573.1334873504@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
johnmudd <johnbmudd(at)gmail(dot)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

In response to

Responses

pgsql-novice by date

Next:From: johnmuddDate: 2012-04-19 22:58:19
Subject: Re: Just trying to read first row in table based on index, pg scans and sorts instead?
Previous:From: johnmuddDate: 2012-04-19 21:41:35
Subject: Just trying to read first row in table based on index, pg scans and sorts instead?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group