Hello, thanks for helping! explain (analyze, BUFFERS) SELECT * FROM "cp"."Repro" x where cp.make_tsvector(x) @@ 'fir:*'::tsquery
Bitmap Heap Scan on "Repro" x (cost=12.00..16.26 rows=1 width=72) (actual time=0.007..0.007 rows=0 loops=1) Recheck Cond: (cp.make_tsvector(x.*) @@ '''fir'':*'::tsquery) Buffers: shared hit=2 -> Bitmap Index Scan on repro_fts_idx (cost=0.00..12.00 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=1) Index Cond: (cp.make_tsvector(x.*) @@ '''fir'':*'::tsquery) Buffers: shared hit=2 Planning Time: 0.070 ms Execution Time: 0.040 ms Query runs fine if uses seq scan. Seq Scan is not desired here since this is the query that should run on huge amounts of data and it should find the first element. Obviously, if seq_scan is off, then query still does the same result.
BR, Dmytro. From: Laurenz Albe 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 row in 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 |
