2

I'm having trouble with a query that I expect to use my indexes, but it is doing a sequential scan instead. Here's my query with explain:

explain SELECT invoice_uuid AS uuid FROM invoice_tcgplayer_line_items                                                                                                                         
JOIN invoice_tcgplayer_details USING(invoice_uuid)                                                                                                                                                            
JOIN card_inventories ON card_inventories.uuid = invoice_tcgplayer_line_items.inventory_uuid
JOIN cards ON cards.uuid = card_inventories.card_uuid
WHERE order_number = 'bcfong28' OR cards.name ~* 'bcfong28' ; 

QUERY PLAN                                                                                                                                                    

------------------------------------------------------------------------------------------------------------------
Hash Join  (cost=2087.79..2383.92 rows=3 width=16)
   Hash Cond: (invoice_tcgplayer_line_items.invoice_uuid = invoice_tcgplayer_details.invoice_uuid)
   Join Filter: ((invoice_tcgplayer_details.order_number = 'bcfong28'::text) OR (cards.name ~* 'bcfong28'::text))
   ->  Nested Loop  (cost=2064.62..2349.22 rows=2549 width=31)
         ->  Hash Join  (cost=2064.57..2103.20 rows=2549 width=32)
               Hash Cond: (invoice_tcgplayer_line_items.inventory_uuid = card_inventories.uuid)
               ->  Seq Scan on invoice_tcgplayer_line_items  (cost=0.00..29.65 rows=2549 width=32)
               ->  Hash  (cost=1734.18..1734.18 rows=94395 width=32)
                     ->  Seq Scan on card_inventories  (cost=0.00..1734.18 rows=94395 width=32)
         ->  Index Scan using cards_pkey on cards  (cost=0.06..0.10 rows=1 width=31)
               Index Cond: (uuid = card_inventories.card_uuid)
   ->  Hash  (cost=19.31..19.31 rows=1103 width=35)
         ->  Seq Scan on invoice_tcgplayer_details  (cost=0.00..19.31 rows=1103 width=35)
(13 rows)

It's doing a sequential scan on card_inventories which has a little under 100k rows in it. If i remove the cards.name ~* part of the WHERE clause (and do a SELECT on cards.name instead), it DOES use the primary key index of card_inventories.uuid. Why is this?

Here's the table description for card_inventories:

\d card_inventories
                       Table "public.card_inventories"
    Column     |            Type             | Collation | Nullable | Default
---------------+-----------------------------+-----------+----------+---------
 uuid          | uuid                        |           | not null |
 card_uuid     | uuid                        |           | not null |
 condition     | text                        |           | not null |
 language      | text                        |           | not null |
 quantity      | integer                     |           | not null |
 created_at    | timestamp without time zone |           | not null |
 updated_at    | timestamp without time zone |           | not null |
 sort_order    | text                        |           |          |
 offline       | boolean                     |           | not null |
 buylist_price | integer                     |           |          |
 overstock     | boolean                     |           | not null | false
 playset_price | integer                     |           | not null | 0
Indexes:
    "card_inventories_pkey" PRIMARY KEY, btree (uuid)
    "buylist_price_condition_idx" btree (buylist_price, condition)
    "buylist_price_idx" btree (buylist_price)
    "card_inventories_card_uuid_idx" btree (card_uuid)
    "condition_idx" btree (condition)
Foreign-key constraints:
    "card_inventories_card_uuid_fkey" FOREIGN KEY (card_uuid) REFERENCES cards(uuid) ON DELETE CASCADE

I do have a trgm fulltext index on cards.name as well so that I can use ~*.

4
  • 1
    Which table is the order_number from? Is there an index on it? And most important, do you really need the OR? Please add the \d output for all the tables. Dec 21, 2017 at 20:44
  • order_number is from invoice_tcgplayer_details as shown in the explain output. I can put \d from everything, but it's a lot (and a lot of fluff, I thought).
    – zombor
    Dec 21, 2017 at 21:10
  • For now I just split the query up into two and UNION'd them together and this accomplishes what I need. Would be nice to not have to do that, though.
    – zombor
    Dec 21, 2017 at 21:11
  • Don't worry, if it's a lot, it may help others answer better. And yes, rewriting OR into a UNION is a common pattern, sometime unavoidable. Dec 21, 2017 at 21:35

1 Answer 1

2

The order_number = 'bcfong28' OR cards.name ~* 'bcfong28' is the cause of the table scan. The planner has no available shortcut that an index would provide, so it has no choice to check all rows to see whether one contraint or the other holds.

Indexing invoice_tcgplayer_details.order_number will cut out out the sequential scan on invoice_tcgplayer_details, and this may be enough for the planner to bring the trgm index to bear on the other and combine the two through a bitmap-or hash join.

That said, the UNION approach will be pretty good, since one of the selects will be nothing more than

SELECT inventory_uuid
FROM invoice_tcgplayer_details
WHERE order_number = 'bcfong28'

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.