Thread: Just trying to read first row in table based on index, pg scans and sorts instead?
Just trying to read first row in table based on index, pg scans and sorts instead?
From
johnmudd
Date:
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;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Limit (cost=24935.26..24935.27 rows=1 width=828) (actual
time=3179.721..3179.721 rows=1 loops=1)
-> Sort (cost=24935.26..25399.14 rows=185551 width=828) (actual
time=3179.719..3179.719 rows=1 loops=1)
Sort Key: (ROW(storenbr, rxnbr, rfnbr))
Sort Method: top-N heapsort Memory: 18kB
-> Seq Scan on rx (cost=0.00..24007.51 rows=185551 width=828)
(actual time=0.038..1094.316 rows=185551 loops=1)
Total runtime: 3179.986 ms
(6 rows)
reports=>
reports=> select version();
version
------------------------------------------------------------------------------------------------------
PostgreSQL 9.1.2 on i686-pc-linux-gnu, compiled by gcc (Ubuntu/Linaro
4.4.4-14ubuntu5) 4.4.5, 32-bit
(1 row)
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Just-trying-to-read-first-row-in-table-based-on-index-pg-scans-and-sorts-instead-tp5652859p5652859.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
Re: Just trying to read first row in table based on index, pg scans and sorts instead?
From
Tom Lane
Date:
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
Re: Just trying to read first row in table based on index, pg scans and sorts instead?
From
johnmudd
Date:
Thanks, that fixed it! That's good news. The bad news is that I had made it a
rule to add the parens because a similar query would "ignore" the an index
if I left the parens off. I'll try to did up the counter example. It was for
a different table, index.
reports=> EXPLAIN ANALYZE SELECT * FROM rx ORDER BY storenbr,rxnbr,rfnbr
LIMIT 1;
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..0.68 rows=1 width=828) (actual time=0.018..0.018 rows=1
loops=1)
-> Index Scan using rx_storenbr_rxnbr_rfnbr_index2 on rx
(cost=0.00..125687.98 rows=185551 width=828) (actual time=0.016..0.016
rows=1 loops=1)
Total runtime: 0.164 ms
(3 rows)
reports=>
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Just-trying-to-read-first-row-in-table-based-on-index-pg-scans-and-sorts-instead-tp5652859p5652991.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
Re: Re: Just trying to read first row in table based on index, pg scans and sorts instead?
From
Steve Crawford
Date:
On 04/19/2012 03:58 PM, johnmudd wrote: > Thanks, that fixed it! That's good news. The bad news is that I had made it a > rule to add the parens because a similar query would "ignore" the an index > if I left the parens off. I'll try to did up the counter example. It was for > a different table, index. When you find it, be sure to include the full description of both tables. Is it possible that you used a bit of a different parenthesization, if there is such a word, when you created the different indexes? Cheers, Steve
Re: Just trying to read first row in table based on index, pg scans and sorts instead?
From
johnmudd
Date:
My mistake. Here's the other query that required parens in order to use the
index. But you'll see that it was parens in the WHERE clause, not in the
ORDER BY that helped in this example. So I tried adding parens to this ORDER
BY and, just like my original SELECT, the performance dropped off. So...
apparently it's important for me to use parens in the WHERE clase and avoid
parens in the ORDER BY.
SELECT * FROM test
WHERE (name, rowid) > ('j', 0) and (name, rowid) != ('', 0)
ORDER BY name, rowid
LIMIT 10
I populated this table with 1,000,000 rows.
CREATE TABLE test (
rowid serial PRIMARY KEY,
name varchar,
bulk varchar
);
CREATE UNIQUE INDEX first_index ON test(name, rowid);
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Just-trying-to-read-first-row-in-table-based-on-index-pg-scans-and-sorts-instead-tp5652859p5653210.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.