Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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; pgsql-general@xxxxxxxxxxxxxxxxxxxx
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

 

 


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux