Re: [WIP]Vertical Clustered Index (columnar store extension) - take2 - Mailing list pgsql-hackers

On Thu, Aug 14, 2025 at 12:04:12PM +1000, Peter Smith wrote:
> On Fri, Aug 1, 2025 at 5:43 PM Japin Li <japinli@hotmail.com> wrote:
> >
> > On Fri, Aug 01, 2025 at 05:18:11PM +1000, Peter Smith wrote:
> > > On Wed, Jul 30, 2025 at 9:07 PM Japin Li <japinli@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?
> >
> 
> No. There aren't any plans to remove this restriction because it is
> not considered to be a "restriction" in the first place;  e.g. VCI is
> intended more like an accelerator only for those *specified* columns
> for which you intend to do your analysis.
> 

Thanks for your explantion!

-- 
Best regards,
Japin Li
ChengDu WenWu Information Technology Co., LTD.