I have a problem creating a usable index for the following simple query:
SELECT * FROM my.table WHERE node = '10' ORDER BY id DESC LIMIT 1
id is a serial, so the query is to find the latest entry to a given
node and id is the primary key.
You're not necessarily getting the latest entry, just the one with the
highest "id". Sequences guarantee uniqueness but if you have
concurrent inserts not necessarily ordering.
Right you are, but I have no concurrent inserts from the same node.
Difficult to say what's happening since you don't supply any EXPLAIN
ANALYSE output.
However, if you have an index on (node,id) you might want to try:
SELECT ... ORDER BY node DESC, id DESC LIMIT 1;
That way the "ORDER BY" part clearly tells the planner that a
reverse-order on your index will be useful.
Thanks a lot, that did the trick !
explain analyze SELECT * FROM my.table WHERE node = '10' ORDER BY id
DESC LIMIT 1
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..764.00 rows=1 width=246) (actual
time=1874.890..1874.896 rows=1 loops=1)
-> Index Scan Backward using table_pkey on table
(cost=0.00..4347913.94 rows=5691 width=246) (actual
time=1874.867..1874.867 rows=1 loops=1)
Filter: ((node)::text = '10'::text)
Total runtime: 1875.111 ms
explain analyze SELECT * FROM my.table WHERE node = '10' ORDER BY node,
id DESC LIMIT 1
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=22638.36..22638.36 rows=1 width=246) (actual
time=3.001..3.007 rows=1 loops=1)
-> Sort (cost=22638.36..22652.59 rows=5691 width=246) (actual
time=2.984..2.984 rows=1 loops=1)
Sort Key: node, id
-> Index Scan using node_date on table (cost=0.00..21898.65
rows=5691 width=246) (actual time=0.077..1.852 rows=62 loops=1)
Index Cond: ((node)::text = '10'::text)
Total runtime: 3.127 ms
Poul
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
