CODEWITHSUNDEEP
sqlpostgresqlindexing
Jack
Jack

Reputation: 5434

Order By Using Index Slow For Only Some Data

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

Answers (1)

jjanes
jjanes

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

Related Questions