I'm getting a extremely slow query on an indexed column. Given the query
SELECT *
FROM orders
WHERE shop_id = 3828
ORDER BY updated_at desc
LIMIT 1
explain analyze
returned:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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 |
Indexes:
"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.
ANALYZE orders;
?