CODEWITHSUNDEEP
postgresqlfull-text-searchtsvector
victorhazbun
victorhazbun

Reputation: 841

Postgres full text search to_tsvector not using index

The query matches the index, why is doing Seq Scan on movies? It should be using Bitmap Heap Scan.

psql (PostgreSQL) 15.4 (Homebrew)

Index:

​CREATE​ ​INDEX​ movies_title_searchable ​ON​ movies​ ​USING​ gin(to_tsvector(​'english'​, title));

Query:

EXPLAIN ANALYZE
SELECT title
FROM movies
WHERE to_tsvector('english', title) @@ to_tsquery('english', 'night & day') LIMIT 1;

->  Seq Scan on movies  (cost=0.00..2.31 rows=1 width=32) (actual time=0.035..0.035 rows=1 loops=1)
        Filter: (to_tsvector('english'::regconfig, title) @@ '''night'' & ''day'''::tsquery)
        Rows Removed by Filter: 3

Upvotes: 1

Views: 667

Answers (1)

victorhazbun
victorhazbun

Reputation: 841

Apparently, postgres won't use the index due the table size (too small).

The query planner may determine a direct read from the heap is preferable to the cost of an index scan, even if there are index hits available. It’s most common for Postgres to choose this path if the index scan itself would return a significant portion of data in the table. As a result, Postgres frequently skips over indexes for small tables.

Upvotes: 1

Related Questions