RE: Re: Postgres Full Text Search Jsonb Array column does not search for first row

Hello, thanks for helping!

explain (analyze, BUFFERS)

SELECT *

FROM "cp"."Repro" x where cp.make_tsvector(x) @@ 'fir:*'::tsquery

outputs this query plan:

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.

Also, if you add 100000 more entries, it will still fail to find the first one using index.

BR, Dmytro.

From: Laurenz Albe
Sent: 26 листопада 2019 р. 21:13
To: Dmytro Zhluktenko ; [email protected]
Subject: Re: Postgres Full Text Search Jsonb Array column does not search for first row

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