<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>The issue was solved with the approach suggested here.<o:p></o:p></span></p><p class=MsoNormal><a href="https://stackoverflow.com/questions/59049873/postgres-full-text-search-jsonb-array-column-does-not-show-first-row">https://stackoverflow.com/questions/59049873/postgres-full-text-search-jsonb-array-column-does-not-show-first-row</a></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>28 листопада 2019 р. 16:39<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: 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 Wed, 2019-11-27 at 11:54 +0200, Dmytro Zhluktenko wrote:</p><p class=MsoNormal>> explain (analyze, BUFFERS)</p><p class=MsoNormal>> SELECT *</p><p class=MsoNormal>> FROM "cp"."Repro" x where cp.make_tsvector(x) @@ 'fir:*'::tsquery</p><p class=MsoNormal>> </p><p class=MsoNormal>> outputs this query plan:</p><p class=MsoNormal>> 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)</p><p class=MsoNormal>> Recheck Cond: (cp.make_tsvector(x.*) @@ '''fir'':*'::tsquery)</p><p class=MsoNormal>> Buffers: shared hit=2</p><p class=MsoNormal>> -> 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)</p><p class=MsoNormal>> Index Cond: (cp.make_tsvector(x.*) @@ '''fir'':*'::tsquery)</p><p class=MsoNormal>> Buffers: shared hit=2</p><p class=MsoNormal>> Planning Time: 0.070 ms</p><p class=MsoNormal>> Execution Time: 0.040 ms</p><p class=MsoNormal>> </p><p class=MsoNormal>> 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.</p><p class=MsoNormal>> Obviously, if seq_scan is off, then query still does the same result.</p><p class=MsoNormal>> </p><p class=MsoNormal>> Also, if you add 100000 more entries, it will still fail to find the first one using index.</p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>I cannot quite follow.</p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>We have seen that the query can use the index by setting "enable_seqscan = off",</p><p class=MsoNormal>but that PostgreSQL prefers to use a sequential scan because the table is small.</p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>If the table were bigger, PostgreSQL would prefer the index scan.</p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Are your concerns hypothetical or real?</p><p class=MsoNormal>If real, can you show EXPLAIN (ANALYZE, BUFFERS) output of a query</p><p class=MsoNormal>execution where PostgreSQL chooses a sequential scan, but you think</p><p class=MsoNormal>it shouldn't?</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>