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