Postgresql – Postgres query not using indexes with conditions from multiple tables

postgresql

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 ~*.

Best Answer

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'
Related Question