<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><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:42.5pt 42.5pt 42.5pt 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style></head><body lang=UK link=blue vlink="#954F72"><div class=WordSection1><p class=MsoNormal><span lang=EN-US>Hello, thanks for helping!<br><br><br><o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>explain (analyze, BUFFERS)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>SELECT *<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US> FROM "cp"."Repro" x where cp.make_tsvector(x) @@ 'fir:*'::tsquery<o:p></o:p></span></p><p class=MsoNormal><br><br><span lang=EN-US>outputs this query plan:<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>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)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US> Recheck Cond: (cp.make_tsvector(x.*) @@ '''fir'':*'::tsquery)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US> Buffers: shared hit=2<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US> -> 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)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US> Index Cond: (cp.make_tsvector(x.*) @@ '''fir'':*'::tsquery)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US> Buffers: shared hit=2<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>Planning Time: 0.070 ms<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>Execution Time: 0.040 ms<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>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.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>Obviously, if seq_scan is off, then query still does the same result.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><br>Also, if you add 100000 more entries, it will still fail to find the first one using index.</span></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><span lang=EN-US>BR, Dmytro.</span><o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><div style='mso-element:para-border-div;border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal style='border:none;padding:0cm'><b>From: </b><a href="mailto:laurenz(dot)albe(at)cybertec(dot)at">Laurenz Albe</a><br><b>Sent: </b>26 листопада 2019 р. 21:13<br><b>To: </b><a href="mailto:d1mnewz(at)gmail(dot)com">Dmytro Zhluktenko</a>; <a href="mailto:pgsql-general(at)lists(dot)postgresql(dot)org">pgsql-general(at)lists(dot)postgresql(dot)org</a><br><b>Subject: </b>Re: Postgres Full Text Search Jsonb Array column does not search for first row</p></div><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>On Tue, 2019-11-26 at 13:37 +0200, Dmytro Zhluktenko wrote:</p><p class=MsoNormal>> Pgsql is unable to perform indexed full text search onto jsonb column containing an array when looking for the first row in the table.</p><p class=MsoNormal>> </p><p class=MsoNormal>> Any ideas why this is happening?</p><p class=MsoNormal>> </p><p class=MsoNormal>> CREATE OR REPLACE FUNCTION cp.make_tsvector(in_t cp."Repro")</p><p class=MsoNormal>> RETURNS tsvector</p><p class=MsoNormal>> LANGUAGE plpgsql</p><p class=MsoNormal>> IMMUTABLE</p><p class=MsoNormal>> </p><p class=MsoNormal>> [...] </p><p class=MsoNormal>> </p><p class=MsoNormal>> CREATE INDEX repro_fts_idx ON cp."Repro" USING gin (cp.make_tsvector(cp."Repro".*)) WITH (fastupdate=off, gin_pending_list_limit='64');</p><p class=MsoNormal>> </p><p class=MsoNormal>> [...]</p><p class=MsoNormal>><o:p> </o:p></p><p class=MsoNormal>> -- explain analyze</p><p class=MsoNormal>> SELECT *</p><p class=MsoNormal>> FROM "cp"."Repro" x where cp.make_tsvector(x) @@ 'sup:*'::tsquery</p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>One possibility is that there ar just too few rows in the table.</p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>SET enable_seqscan = off;</p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>and then try again.</p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>If that is not the problm, please provide EXPLAIN (ANALYZE, BUFFERS) output for</p><p class=MsoNormal>the query.</p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Yours,</p><p class=MsoNormal>Laurenz Albe</p><p class=MsoNormal>-- </p><p class=MsoNormal>Cybertec | https://www.cybertec-postgresql.com</p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><o:p> </o:p></p></div></body></html>