Horton

Re: [WIP]Vertical Clustered Index (columnar store extension) - take2

Peter Smith <smithpb2250@gmail.com>

View thread
From: Peter Smith <smithpb2250@gmail.com>
To: Japin Li <japinli@hotmail.com>
Cc: "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>, Tomas Vondra <tomas@vondra.me>, "Aya Iwata (Fujitsu)" <iwata.aya@fujitsu.com>, Timur Magomedov <t.magomedov@postgrespro.ru>, shveta malik <shveta.malik@gmail.com>
Date: 2025-08-01T07:18:11Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Mark ItemPointer arguments as const throughout

  2. Reorganize GUC structs

  3. Eliminate XLOG_HEAP2_VISIBLE from vacuum phase III

  4. Update various forward declarations to use typedef

  5. Pathify RHS unique-ification for semijoin planning

  6. Revert "Don't lock partitions pruned by initial pruning"

  7. For inplace update, send nontransactional invalidations.

  8. Remove nearly-unused SizeOfIptrData macro.

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

======
Kind Regards,
Peter Smith.
Fujitsu Australia