On Fri, Aug 01, 2025 at 05:18:11PM +1000, Peter Smith wrote:
> On Wed, Jul 30, 2025 at 9:07 PM Japin Li <japi...@hotmail.com> 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
>
Thanks for your explantion! Got it.
Are there any plans to remove this restriction in the future?
--
Best regards,
Japin Li
ChengDu WenWu Information Technology Co., LTD.
