Thread

Unexpected seq scan on table with JSONB column

Jan Jongen <jan.jongen@valtech-mobility.com> — 2026-05-11T08:33:13Z

Hi,

We encountered the following unexpected situation when doing a query on a table containing a jsonb column with larg(ish) (64kb minified) JSONB payloads which has a predicate on the jsonb column. We created an expression index (see below) and expected that the query will use the index. The query might only filter out a small part of actual data but since it is a count we expected it will use the index to avoid costly rechecks on the actual jsonb data.

The table structure is:

                                              Table "state.state_a"
   Column   |     Type     | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
------------+--------------+-----------+----------+---------+----------+-------------+--------------+-------------
 vehicle_id | uuid         |           | not null |         | plain    |             |              |
 code       | character(1) |           | not null |         | extended |             |              |
 document   | jsonb        |           | not null |         | extended |             |              |
Indexes:
...
    "state_a_text_idx" btree ((document ->> 'country_composition_iso'::text))

With an expression index on the predicate. The query in question is simply:

explain (analyse, buffers) select count(vehicle_id) from state.state_a where document->>'country_composition_iso' = 'CH';
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=6447.74..6447.75 rows=1 width=8) (actual time=86404.994..86404.995 rows=1.00 loops=1)
   Buffers: shared hit=960991 read=537879
   ->  Seq Scan on state_a  (cost=0.00..5823.77 rows=249585 width=16) (actual time=2.513..86328.761 rows=249460.00 loops=1)
         Filter: ((document ->> 'country_composition_iso'::text) = 'CH'::text)
         Rows Removed by Filter: 5
         Buffers: shared hit=960991 read=537879
 Planning Time: 0.094 ms
 Execution Time: 86405.033 ms
(8 rows)

Which uses a seq scan and as you can see takes quite a long time. Disabling seq_scans actually uses the index as expected and is way faster:

SET enable_seqscan = OFF;

 explain (analyse, buffers) select count(vehicle_id) from state.state_a where document->>'country_composition_iso' = 'CH';
                                                                    QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=7501.12..7501.13 rows=1 width=8) (actual time=170.223..170.224 rows=1.00 loops=1)
   Buffers: shared hit=344 read=1952
   ->  Index Scan using state_a_text_idx on state_a  (cost=0.42..6877.16 rows=249585 width=16) (actual time=2.236..147.748 rows=249460.00 loops=1)
         Index Cond: ((document ->> 'country_composition_iso'::text) = 'CH'::text)
         Index Searches: 1
         Buffers: shared hit=344 read=1952
 Planning Time: 0.099 ms
 Execution Time: 170.266 ms
(8 rows)

From what I can see is that the cost estimate for the seq scan is very low and it seems that it does not include the fact that the filter must load the data from the associated toast table. Looking at the relpages:
 relname | relpages | reltuples
---------+----------+-----------
 state_a |     2079 |    249465
 pg_toast_439105 |   530117 | 2.245078e+06

Suggest that for the cost estimation cost estimation of the seq scan only the state_a table and its pages is considered.  My questions would be:


  *
Is the analysis correct that the root cause is in fact the misestimation of the costs due to not considering the toast table?
  *
If so, is it possible to fix this?
  *
Our workaround currently is to set the seq_page_cost higher to avoid this scenario. Any recommendations how to better workaround this?

Thanks for your help!


Related PG version:
                                   version
------------------------------------------------------------------------------
 PostgreSQL 18.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 13.2.0, 64-bit
(1 row)


Cheers,

Jan