1

Greenplum database supports B-tree index on append-optimized columnar tables which allows UPDATE operations as well . Even though it's not a recommended practice to have index on such tables(probably because they are intended for append-only and do fast sequential scans) for update operation, an index on distributing column reduces execution time drastically.

While traditionally a B-tree index on rowstore table holds the pointer to heap with offset value, how this will be implemented on a columnar table? If the table has N columns does each entry in index contain total N-1 pointers to each column blocks?

goodfella
  • 589
  • 4
  • 14

1 Answers1

1

This answer is for PostgreSQL specifically.

A B-tree index on a table that uses columnar storage will look just like any other B-tree index. The leaf nodes reference a table row. In PostgreSQL, the APIs for a custom table access method and a custom index access method are independent. The link is in the "tuple identifier".

The question is how the column store implements a tuple ID. One approach is to have a separate column for that. Needless to say, collecting an individual table row from a column store is quite inefficient.

Laurenz Albe
  • 61,070
  • 4
  • 55
  • 90