Hi,
I have a GIN index but the planner rechecks the condition. When many rows
are foud in the index that is very slow:
EXPLAIN SELECT "tracks".* FROM "tracks" WHERE (to_tsvector('simple',
normalized_artist || ' ' || normalized_title) @@ to_tsquery('love'));
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tracks (cost=471.80..33026.91 rows=13908 width=129)
Recheck Cond: (to_tsvector('simple'::regconfig,
(((normalized_artist)::text || ' '::text) || (normalized_title)::text)) @@
to_tsquery('love'::text))
-> Bitmap Index Scan on tracks_gin_keywords (cost=0.00..468.32
rows=13908 width=0)
Index Cond: (to_tsvector('simple'::regconfig,
(((normalized_artist)::text || ' '::text) || (normalized_title)::text)) @@
to_tsquery('love'::text))
The documentation says that recheck are only required for weight:
http://www.postgresql.org/docs/current/static/textsearch-indexes.html
"GIN indexes store only the words (lexemes) of tsvector values, and not
their weight labels. Thus a table row recheck is needed when using a query
that involves weights.)"
I don't need weight because I order results on another column. How can I get
read of that recheck condition?
Cheers,
Alexis.
PS, table definition:
Table "public.tracks"
Column | Type |
Modifiers
-------------------+-----------------------------+-----------------------------------------------------
id | integer | not null default
nextval('tracks_id_seq'::regclass)
created_at | timestamp without time zone |
updated_at | timestamp without time zone |
title | character varying(255) | not null
artist | character varying(255) | not null
normalized_title | character varying(255) |
normalized_artist | character varying(255) |
released_on | date |
resolved_at | timestamp without time zone |
permalink | character varying(255) |
featuring | character varying(255) |
Indexes:
"tracks_pkey" PRIMARY KEY, btree (id)
"index_tracks_on_permalink" UNIQUE, btree (permalink)
"index_tracks_on_normalized_artist_and_normalized_title" btree
(normalized_artist, normalized_title)
"tracks_gin_keywords" gin (to_tsvector('simple'::regconfig,
(normalized_artist::text || ' '::text) || normalized_title::text))
--
View this message in context: http://postgresql.nabble.com/How-to-avoid-a-GIN-recheck-condition-tp5827995.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
