Reputation: 5434
One org that has 30k items and takes forever for this simple query to run (doesn't finish, I have to stop it). A separate org that has 60k items returns very quickly (400 msec). Why is this happening? Do I need to rebuild index?
This is my query:
SELECT items.*
FROM items
WHERE items.org_id = '123-456-abc'
ORDER BY items.name ASC
LIMIT 10
OFFSET 0
Here is the EXPLAIN
output for the org with 30k items:
Limit (cost=0.56..3734.80 rows=10 width=307)
-> Index Scan using index_items_on_name on items (cost=0.56..11272191.50 rows=30186 width=307)
Filter: (org_id = '123-456-abc'::uuid)
For the 60k items org:
Limit (cost=0.56..1831.41 rows=10 width=307)
-> Index Scan using index_items_on_name on items (cost=0.56..11272191.50 rows=61568 width=307)
Filter: (org_id = '789-123-def'::uuid)
There are 20 columns on this items
table, and I have a non-unique index on the name
column called index_items_on_name
(definition says btree, sort order ASC, and NULLs LAST). I also have a trigram index on barcode, sku, and name, but it's probably not relevant because the EXPLAIN
says it's using index_items_on_name
.
Upvotes: 0
Views: 316
Reputation: 44423
Schwern is correct as for why it is slow. To make it faster, add an index on (org_id, name)
. That way it can use one index to do both things, find just the org requested, then read them already in order.
Upvotes: 2