CoveringIndex with Partition

[ashokblend]

When we create covering index from source table which has partition based on some dimension say date.
Created index doesn't have partition, instead it has partition as an included columns.
in case where we are running query for selected day partition, source data gives better performance than hyperspace, may be because hyperspace ends up scanning all data.

Is it possible to partition the index ?

[sezruby]

For partitioned data, bucketed index will create (distinct number of partition values * number of buckets) of files, which may cause regressions. (many small files problem). For non-bucketed data, like sorted or zorder covering index, it's possible to support it.
But due to priority, it's not planed yet.

[ashokblend]

@sezruby. I was trying zorder covering index as per #495. I had given partition column as one of index column.

During query, does hyperspace always do full scan of index eventhough query have filter on index column and hyperspace completly depends on parquet dataskipping feature based on min/max ?

[sezruby]

Yes we depend on parquet min/max pruning feature. However we can apply data skipping index (#441) for covering index data.
It's not implemented / tested yet so we rely on parquet pruning feature for now.
Fyi, I'm currently revising and improving zorder feature and not pushed the latest version yet; I'll update the PR soon.

[ashokblend]

As i understand from (#441) , Hyperspace will have another type of index i.e DataSkippingIndex which keeps the stats of source file and it doesn't duplicate data. But I don't see mentioned request discussing about having DataSkippingIndex for covering index data. May be i am missing it, may you please point me that section.

[sezruby]

I meant we can utilize data skipping feature for covering index especially zorder index.
It's not specified in the description but it's possible.

[ashokblend]

got it. Another thought, instead of creating dataskipping index for zorder covering index can we add stats of each generated file, by zorder covering index. This also can help us in file pruning ?