Re: Optimizing query

>> 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