On Wed, Jul 30, 2025 at 9:07 PM Japin Li <[email protected]> wrote:
>
...
> 3.
> I've also found that the VCI index is not working. Is this the expected
> behavior?
>
> [local]:3209161 postgres=# \d+ t
> Table "public.t"
> Column | Type | Collation | Nullable | Default | Storage | Compression |
> Stats target | Description
> --------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
> id | integer | | | | plain | |
> |
> info | text | | | | extended | |
> |
> Indexes:
> "t_id_idx" vci (id)
> Access method: heap
>
> [local]:3209161 postgres=# SET enable_seqscan TO off;
> SET
> [local]:3209161 postgres=# EXPLAIN SELECT * FROM t WHERE id = 1000;
> QUERY PLAN
> -----------------------------------------------------
> Seq Scan on t (cost=0.00..2084.00 rows=1 width=37)
> Disabled: true
> Filter: (id = 1000)
> (3 rows)
>
Hi Japin. Yes, that's expected behaviour.
VCI is used only when the vci index is defined for all the columns of
your query. In your example there was a table with 2 columns ('id' and
'info') but you only have an index on the 'id' column. If you change
the query then you can see VCI getting used.
E.g.
postgres=# EXPLAIN SELECT id FROM t WHERE id = 1000;
QUERY PLAN
----------------------------------------------------------------------------
Custom Scan (VCI Scan) using tidx on t (cost=0.00..209.00 rows=1 width=4)
Filter: (id = 1000)
(2 rows)
postgres=# EXPLAIN SELECT * FROM t WHERE id = 1000;
QUERY PLAN
----------------------------------------------------
Seq Scan on t (cost=0.00..209.00 rows=1 width=37)
Filter: (id = 1000)
(2 rows)
postgres=# EXPLAIN SELECT id,info FROM t WHERE id = 1000;
QUERY PLAN
----------------------------------------------------
Seq Scan on t (cost=0.00..209.00 rows=1 width=37)
Filter: (id = 1000)
(2 rows)
~~~
You can see this also in the DEBUG logs, from
vci_can_rewrite_custom_scan(), where it checks to see if the attrs are
in the vci index or not.
e.g.
2025-08-01 16:58:20.939 AEST [26528] DEBUG: vci index: target table
"t"(oid=16384) tuples(rows=10000,extents=0)
2025-08-01 16:58:20.939 AEST [26528] DEBUG: vci index: don't match
index "tidx"(oid=16469)
2025-08-01 16:58:20.940 AEST [26528] DEBUG: attrnum = 1 x
2025-08-01 16:58:20.940 AEST [26528] DEBUG: attrnum = 2
======
Kind Regards,
Peter Smith.
Fujitsu Australia
