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

Started by Dmytro Zhluktenkoover 6 years ago5 messagesgeneral

d1mnewz@gmail.com

over 6 years ago

<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style></style></head><body lang=UK><div class=WordSection1>Hey, <o:p></o:p><div style='margin-bottom:3.75pt;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch: inherit;box-sizing: inherit;overflow-wrap: break-word'>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?<o:p></o:p><p style='margin:0cm;margin-bottom:.0001pt;background:white;vertical-align:baseline;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch: inherit;line-height:inherit;box-sizing: inherit'>Table is nothing more than just <code>Id</code> and <code>Foo</code> which is <code>jsonb</code> column.<o:p></o:p><p style='margin:0cm;margin-bottom:.0001pt;background:white;vertical-align:baseline;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch: inherit;line-height:inherit;box-sizing: inherit'>The case is that I have an empty database with predefined gin index <code>repro_fts_idx</code> on <code>make_tsvector</code> function. <code>make_tsvector</code> creates <code>tsvector</code> from given <code>jsonb</code> column.<o:p></o:p><p style='margin:0cm;margin-bottom:.0001pt;background:white;vertical-align:baseline;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch: inherit;line-height:inherit;box-sizing: inherit'>When I add a new item into the table, I expect it to appear in <code>make_tsvector</code> function in a form of <code>tsvector</code>. It's there. Also, I expect that if I run full text search query onto it, it would appear in search results. However, this is not the case because it returns empty specifically for the first row. It simply does not take it into account. If I add one more row which is completely the same, the system is able to find it with the same query.<o:p></o:p><p style='mso-margin-top-alt:0cm;margin-right:0cm;margin-bottom:12.0pt;margin-left:0cm;background:white;vertical-align:baseline;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch: inherit;line-height:inherit;box-sizing: inherit'>here is a small repro case:<o:p></o:p><pre style='background:#EFF0F1;vertical-align:baseline;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch: inherit;line-height:inherit;box-sizing: inherit;max-height: 600px;border-radius: 3px;overflow-wrap: normal;overflow:auto'><o:p> </o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'>-- drop table cp."Repro" cascade<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'><o:p> </o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'>CREATE TABLE cp."Repro" (<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'> "Id" serial NOT NULL,<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'> "Foo" jsonb NULL<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'>);<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'><o:p> </o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'>CREATE OR REPLACE FUNCTION cp.make_tsvector(in_t cp."Repro")<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'> RETURNS tsvector<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'> LANGUAGE plpgsql<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'> IMMUTABLE<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'>AS $function$ begin <o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'> return to_tsvector(jsonb_agg(x.prop))<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'> from (SELECT CONCAT( jsonb_array_elements(in_t."Foo") ->> 'Name', ' ', jsonb_array_elements(in_t."Foo") ->> 'Address' ) as prop from cp."Repro" f) as x;<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'> END;<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'> $function$<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'>;<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'><o:p> </o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'><o:p> </o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'>CREATE INDEX repro_fts_idx ON cp."Repro" USING gin (cp.make_tsvector(cp."Repro".*)) WITH (fastupdate=off, gin_pending_list_limit='64');<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'><o:p> </o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'><o:p> </o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'>INSERT INTO cp."Repro"<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'>("Foo")<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'>VALUES('[{"Name": "Sup", "Address": "Adress", "IsCurrent": true}]');<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'><o:p> </o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'>-- just in case it's the indexing issue<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'>-- REINDEX INDEX cp.repro_fts_idx;<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'><o:p> </o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'>select * from cp."Repro"<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'><o:p> </o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'>select cp.make_tsvector(x) from cp."Repro" x<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'><o:p> </o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'>select * from ts_stat('select cp.make_tsvector(x) from cp."Repro" x')<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'><o:p> </o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'>-- explain analyze<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'>SELECT *<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'> FROM "cp"."Repro" x where cp.make_tsvector(x) @@ 'sup:*'::tsquery<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'><o:p> </o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'><o:p> </o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'> INSERT INTO cp."Repro"<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'>("Foo")<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'>VALUES('[{"Name": "Sup", "Address": "Adress", "IsCurrent": true}]');<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'><o:p> </o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'><o:p> </o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'>-- explain analyze<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'>SELECT *<o:p></o:p></pre><pre style='background:#EFF0F1;vertical-align:baseline'> FROM "cp"."Repro" x where cp.make_tsvector(x) @@ 'sup:*'::tsquery<o:p></o:p></pre></div><o:p> </o:p><o:p> </o:p>BR, Dmytro.<o:p></o:p><o:p> </o:p></div></body></html>

Laurenz Albe

laurenz.albe@cybertec.at

over 6 years ago

In reply to: Dmytro Zhluktenko (#1)

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

Dmytro Zhluktenko

d1mnewz@gmail.com

over 6 years ago

In reply to: Laurenz Albe (#2)

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

Import Notes

Resolved by subject fallback

Laurenz Albe

laurenz.albe@cybertec.at

over 6 years ago

In reply to: Dmytro Zhluktenko (#3)

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

On Wed, 2019-11-27 at 11:54 +0200, Dmytro Zhluktenko wrote:

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.

I cannot quite follow.

We have seen that the query can use the index by setting "enable_seqscan = off",
but that PostgreSQL prefers to use a sequential scan because the table is small.

If the table were bigger, PostgreSQL would prefer the index scan.

Are your concerns hypothetical or real?
If real, can you show EXPLAIN (ANALYZE, BUFFERS) output of a query
execution where PostgreSQL chooses a sequential scan, but you think
it shouldn't?

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Dmytro Zhluktenko

d1mnewz@gmail.com

over 6 years ago

In reply to: Laurenz Albe (#4)

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

Import Notes

Resolved by subject fallback