I'm getting a extremely slow query on an indexed column. Given the query

FROM orders 
WHERE shop_id = 3828 
ORDER BY updated_at desc 

explain analyze returned:

 Limit  (cost=0.43..594.45 rows=1 width=175) (actual time=202106.830..202106.831 rows=1 loops=1)
   ->  Index Scan Backward using index_orders_on_updated_at on orders  (cost=0.43..267901.54 rows=451 width=175) (actual time=202106.827..202106.827 rows=1 loops=1)
         Filter: (shop_id = 3828)
         Rows Removed by Filter: 1604818
 Planning time: 98.579 ms
 Execution time: 202127.514 ms
(6 rows)

The table description is:

                                         Table "public.orders"
       Column       |            Type             |                           Modifiers
 id                 | integer                     | not null default nextval('orders_id_seq'::regclass)
 sent               | boolean                     | default false
 created_at         | timestamp without time zone |
 updated_at         | timestamp without time zone |
 name               | character varying(255)      |
 shop_id            | integer                     |
 recovered_at       | timestamp without time zone |
 total_price        | double precision            |
    "orders_pkey" PRIMARY KEY, btree (id)
    "index_orders_on_recovered_at" btree (recovered_at)
    "index_orders_on_shop_id" btree (shop_id)
    "index_orders_on__updated_at" btree (updated_at)

It's a Postgres server, running on an AWS RDS t2 micro instance.
The table has around 2.6 million rows.

  • Does it stay the same after doing an ANALYZE orders;? Commented Sep 1, 2015 at 13:35
  • No, it's still extremely slow.
    – davids
    Commented Sep 1, 2015 at 14:43

3 Answers 3


I dont know Postgresql too well, but you're checking across two seperate keys to find the values you're looking for, try creating it as a composite key instead

"index_orders_on_shop_id" btree (shop_id)
"index_orders_on__updated_at" btree (updated_at)


"index_orders_on_shop_id__updated_at" btree (shop_id,updated_at)

that could help

if there's a way to include values in an index that would work even better

  • 1
    This is probably the best solution. The reason seems to be that optimizer thinks it can find the right shop_id soon when going in updated_at order, reason may be bad statistics or just skewed data (statistics are not good at showing skewness), so it does not use the right index (as it can pick only one and the ordering seems more important at that moment).
    – jkavalik
    Commented Sep 1, 2015 at 11:51

There is a subtle problem hidden in your ORDER BY clause:

ORDER BY updated_at DESC 

Would sort NULL values first. I assume you do not want that. Your column updated_at can be NULL (lacks a NOT NULL constraint). The missing constraint should probably be added. Your query should be fixed either way:

FROM   orders 
WHERE  shop_id = 3828 

The multicolumn index @Ste Bov mentioned should be adapted accordingly:

CREATE INDEX orders_shop_id_updated_at_idx ON orders (shop_id, updated_at DESC NULLS LAST);

Then you get a basic Index Scan instead of the (almost as fast) Index Scan Backward, and you won't get an additional index condition: Index Cond: (updated_at IS NOT NULL).



You can save a bit of wasted disk space by optimizing the sequence of columns for your big table (which makes everything a bit faster):

id                 | integer                     | not null default nextval( ...
shop_id            | integer                     |
sent               | boolean                     | default false
name               | varchar(255)                |
total_price        | double precision            |
recovered_at       | timestamp without time zone |
created_at         | timestamp without time zone |
updated_at         | timestamp without time zone |


Add NOT NULL constraints to all columns that cannot be NULL.

Consider text or varchar instead of varchar(255), timestamptz instead of timestamp and integer for the price (as Cents) or numeric (for fractional numbers) which is an arbitrary precision type and stores your values exactly as given. Never use a lossy floating point type for a "price" or anything to do with money.


The issue is: you do use index but the number of rows returned and should be sorted is too big to fit into your instance type memory. So you can create index on shop_id + updated_at columns as btree index is always sorted. Unfortunately in your case the shop_id isn't selective enough so in addition to that the hash partition on shop_id might be useful.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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