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?

2
  • "Even though it's not a recommended practice to have index on such tables" - Who is not-recommending this, exactly? Please provide a source or citation, thanks.
    – Dai
    Commented Jul 8 at 11:45
  • docs.vmware.com/en/VMware-Greenplum/7/greenplum-database/… i have verified this, columnar table plans always prefer seq scan over index scan while the same data on the normal tables performing index scan.
    – mediocre
    Commented Jul 9 at 1:28

1 Answer 1

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.

2
  • Oh thanks for pointing out about TID. So TID is where the implementation changes while B-tree holds TID irrespective of underlying table orientation. "One approach is to have a separate column for that" How this works? I have read that columnar table itself implement an index like structure internally and hence there is no need to explicitly define an index.
    – mediocre
    Commented 2 days ago
  • Right, a tuple ID identifies a table row. I don't know how column stores organize their columns internally - I guess there might be different approaches. Commented 2 days ago

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.