6

I have table items with following schema (in postgres v9.3.5):

  Column   | Type   |                         Modifiers                  | Storage  
-----------+--------+----------------------------------------------------+----------
 id        | bigint | not null default nextval('items_id_seq'::regclass) | plain    
 data      | text   | not null                                           | extended 
 object_id | bigint | not null                                           | plain    
Indexes:
    "items_pkey" PRIMARY KEY, btree (id)
    "items_object_id_idx" btree (object_id)
Has OIDs: no

When I execute query it hangs for a very long time:

SELECT * FROM "items" WHERE "object_id" = '123' ORDER BY "id" DESC LIMIT 1;

After VACUUM ANALYZE the query execution improved a lot, but still not perfect.

# EXPLAIN ANALYZE SELECT * FROM "items" WHERE "object_id" = '123' ORDER BY "id" DESC LIMIT 1;
                                                                            QUERY PLAN                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.44..1269.14 rows=1 width=63) (actual time=873796.061..873796.061 rows=0 loops=1)
   ->  Index Scan Backward using items_pkey on items  (cost=0.44..1164670.11 rows=918 width=63) (actual time=873796.059..873796.059 rows=0 loops=1)
         Filter: (object_id = 123::bigint)
         Rows Removed by Filter: 27942522
 Total runtime: 873796.113 ms
(5 rows)

The strange thing is that when I execute

SELECT * FROM "items" WHERE "object_id" = '123' LIMIT 1;

it returns 0 rows and I can do it in my code to optimize my web application's performance, but why it can be done by Postgres itself? I came to Postgres from MySQL and never seen such strange things there.

=====

I have found that it uses different query plan, different index, but why?

# EXPLAIN ANALYZE SELECT * FROM "items" WHERE "object_id" = '123' LIMIT 1;
                                                                          QUERY PLAN                                    
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..3.34 rows=1 width=63) (actual time=0.014..0.014 rows=0 loops=1)
   ->  Index Scan using items_object_id_operation_idx on items  (cost=0.56..2579.16 rows=929 width=63) (actual time=0.013..0.013 rows=0 loops=1)
         Index Cond: (object_id = 123::bigint)
 Total runtime: 0.029 ms
(4 rows)
  • There is index on "object_id", so it filters it fine. "id" is also having primary index. Does it matter if it is ASC or DESC index in PostgreSQL? Because in MySQL it doesn't matter. – happy_marmoset Aug 12 '15 at 13:03
  • @happy_marmoset Yes, a (a asc, b desc) is slightly different than a (a asc, b asc) one. But for this query, the increase in efficiency will be only slight, I think. (MySQL has only ASC indexes.) – ypercubeᵀᴹ Aug 12 '15 at 13:27
  • How many distinct ids and object_ids are there in the table? A unique index on (object_id asc, id desc) might help in this particular case. – mustaccio Aug 12 '15 at 14:15
  • I have tested ORDER BY "id" ASC LIMIT 1, it also same slow. I have added index btree(object_id asc, id desc) as @mustaccio suggested and it worked, through still do not understand why it doesn't work without that index. – happy_marmoset Aug 13 '15 at 7:02
8

Trying to explain why there is difference in performance between the two queries.

This one: SELECT * FROM "items" WHERE "object_id" = '123' LIMIT 1 is satisfied by any one row with the matching object_id, so the index on object_id is a natural choice. The query requires minimal I/O: index scan to find the first matching value plus one heap read to fetch the entire row.

The alternative: SELECT * FROM "items" WHERE "object_id" = '123' ORDER BY "id" DESC LIMIT 1 requires all rows with the matching object_id be sorted by another column, id, then the row with the maximum value of id be returned. If you were to use the index on object_id you would need to perform the following operations: scan the index to find every matching object_id; for every match go fetch the actual row; then sort all fetched rows by id and return the one with the largest id.

The alternative chosen by the optimizer, presumably based on the object_id histogram, is: scan the index on id backwards, in its entirety; for each value go fetch the row and check if the value of object_id matches; return the first matching row, which will have the maximum possible id value. This alternative avoids sorting the rows, so I guess the optimizer prefers it to using the index on object_id.

The presence of an index on (object_id asc, id desc) allows for yet another alternative: scan this new index for the first entry matching the supplied object_id value, which by definition will have the highest id value; go fetch one matching row and return. Obviously, this is the most efficient approach.

  • Thanks for the explanation. For me, changes LIMIT 1 to LIMIT 2 or more solves the problem. – WTIFS Jul 26 at 14:01
1

To optimize query

SELECT * FROM "items" WHERE "object_id" = '123' ORDER BY "id" DESC LIMIT 1;

I have done following

SELECT * FROM 
    (SELECT * FROM "items" 
     WHERE "object_id" = '123'
     ORDER BY "id" DESC) AS "items" 
ORDER BY "id" DESC LIMIT 1;

It helped without adding index (object_id asc, id desc) which suggested by @mustaccio.

# EXPLAIN SELECT * FROM 
    (SELECT * FROM "items" 
     WHERE "object_id" = '123'
     ORDER BY "id" DESC) AS "items" 
ORDER BY "id" DESC LIMIT 1;
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Limit  (cost=16629.84..16629.86 rows=1 width=59)
   ->  Sort  (cost=16629.84..16640.44 rows=4239 width=59)
         Sort Key: items.id
         ->  Bitmap Heap Scan on items  (cost=125.42..16374.45 rows=4239 width=59)
               Recheck Cond: (object_id = 123::bigint)
                   ->  Bitmap Index Scan on items_object_id_idx  (cost=0.00..124.36 rows=4239 width=0)
                     Index Cond: (object_id = 123::bigint)
(7 rows)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.