On Tue, 2019-11-26 at 13:37 +0200, Dmytro Zhluktenko wrote:
> Pgsql is unable to perform indexed full text search onto jsonb column containing an array when looking for the first
rowin the table.
>
> Any ideas why this is happening?
>
> CREATE OR REPLACE FUNCTION cp.make_tsvector(in_t cp."Repro")
> RETURNS tsvector
> LANGUAGE plpgsql
> IMMUTABLE
>
> [...]
>
> CREATE INDEX repro_fts_idx ON cp."Repro" USING gin (cp.make_tsvector(cp."Repro".*)) WITH (fastupdate=off,
gin_pending_list_limit='64');
>
> [...]
>
> -- explain analyze
> SELECT *
> FROM "cp"."Repro" x where cp.make_tsvector(x) @@ 'sup:*'::tsquery
One possibility is that there ar just too few rows in the table.
SET enable_seqscan = off;
and then try again.
If that is not the problm, please provide EXPLAIN (ANALYZE, BUFFERS) output for
the query.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com