Indexing Support in DataFusion?

[alamb]

@westonpace and I had a good conversation about indexes that I wanted to document for anyone else who might be thinking of something similiar.

The high level observation we have is that both lancedb and InfluxDB have special TableProviders with special knowledge of how data is stored, and each effectively implement some form of custom "indexing".

Indexing in this case means using the predicate from a query to rule out files / ranges of files before execution.

DataFusion itself also has a form of this type of index in the ListingTable (code link) where it prunes files based on statistics, and then inside the ParquetExec itself (link) where it again prunes row groups and data pages based on metadata,

Some things we are thinking about in InfluxDB arepassing row selections into ParquetExec from an outside source (aka a special index) with an API like #9929

Here are some diagrams from Weston showing LanceDB's indexing

Here are some options we discussed

Option 1: Each system that needs this type of indexing (continue)s to use a custom TableProvider

Pros:

• Can be done today, and is what both InfluxDB and LanceDB do
• General: makes no assumptions about how indexing is implemented

Cons:

• Can't leverage all the machinery of DataFusion likely resulting in duplication (e.g. if you have multiple indexes / predicates)
• No good way to serialize / pass any required indedxing state between nodes without serializing them as arrow arrays (InfluxDB uses a __order and LanceDB uses Row Masks)

A specific example of duplication is that InfluxDB does not use ListingProvider when reading parquet files, but has something very similar internally (but augmented with our own catalog information, etc)

Option 2: Add APIs to pass additional knowledge about indexes / indexed reads into DataFusion somehow

Pros:

• Reduce the implementation effort for using custom indexes by sharing code

Cons:

• Unclear what those APIs could be

We discussed some options:

• IndexSearch node
• TakeExec (index lookup) -- really like an indexed scan somehow>
• Standard way to represent row ids (Row Masks, Row Indexes, etc)?
• Representation of the indexes themselves (e.g. serialization / deserializtion). Maybe DDL (CREATE INDEX ....) 🤔

Thoughts?

[matthewmturner]

I love that we're starting to have conversation about this. Adding some color from our experience with indexing.

We have a bitmap index to address some of the performance issues with using ListingTable at scale (lots of files) and we are starting to build additional functionality into it that could work with a custom table provider. We aren't 100% set on the API / needed structures yet, and it may change as we continue to work on it (right now we use a ListingTable with a custom FileFormat that provides the indexing capabilities), but I think we're pretty happy with where it's going.

Below are the core pieces that we have. As noted above, some of it may not be relevant in future as this current design is made to work with FileFormat / ListingTable

The general idea is we have an Index struct that is part of a table and holds information like the columns that are indexed (columns), how to access the index (provider, i.e from disk or if it is in memory), and the strategy for using the index(index_strategy). A single index could have different information such as bitmaps, file metadata / statistics, row group / page info, etc and they could each have their own execution strategy. With all of this, and the filter expr, the index is responsible for producing file_groups that can be used by FileScanConfig instead of the ListingTable having to get them with with a list call.

We have a separate process that is responsible for keeping the index up to date with the latest information. I dont have a strong opinion currently on how this (keeping the index up to date) should be handled by datafusion.

#[derive(Debug)]
pub struct Index {
    columns: HashSet<String>,
    provider: Arc<dyn IndexProvider>,
    index_strategy: Arc<dyn IndexStrategy>,
}

impl Index {
    pub async fn evaluate(
        &self,
        state: &SessionState,
        conf: &FileScanConfig,
        predicate: &Arc<dyn PhysicalExpr>,
    ) -> Result<Vec<Vec<PartitionedFile>>>
}

pub trait IndexStrategy: Debug + Send + Sync {
    fn execute(
        &self,
        mask: FileMask,
        files: Arc<Vec<Arc<String>>>,
        bitmaps: HashMap<BitmapKey, Arc<Bitmap>>,
        conf: &FileScanConfig,
    ) -> Vec<Vec<PartitionedFile>>;
}

#[async_trait]
pub trait IndexProvider: Debug + Send + Sync {
    async fn get_bitmaps(
        &self,
        context: &SessionContext,
        keys: Option<&[BitmapKey]>,
    ) -> Result<HashMap<BitmapKey, Arc<Bitmap>>>;
    async fn get_files(&self, context: &SessionContext) -> Result<Arc<Vec<Arc<String>>>>;

    async fn get_last_modified(&self) -> Result<DateTime<Utc>>;

    fn get_name(&self) -> Arc<String>;
}

Similar to other areas of datafusion i think it would be very cool if a simple index implementation could be included out of the box, acting as a reference implementation, while still providing the relevant traits to allow users to customize as needed.

Finally, I really like the idea of having index specific nodes so that we can see the index's impact when analyzing logical / physical plans.

[alamb]

With all of this, and the filter expr, the index is responsible for producing file_groups that can be used by FileScanConfig instead of the ListingTable having to get them with with a list call.

We have some version of this in InfluxDB 3.0 too

One slight variation we are considering is somehow encoding in file_groups not just which files but also what ranges within those files (e.g. #9929)

[alamb]

I think @cisaacson is also using a custom index structure with DataFusion so maybe he has some insights to share

[matthewmturner]

@alamb indeed i read up on the row selection api and it looks promising. are there any existing benchmarks that show the performance improvements that can be achieved using that?

[alamb]

Not yet -- but I hope to make some (though likely not until next week)

[matthewmturner]

I was thinking about this some more and I wonder if this could be made in a generic enough way that things like deltalake or apache iceberg could be registered as index providers so that custom table providers arent needed to use those.

[alamb]

Something else I have been thinking about is if it would make sense to pull ListingTable out of the core datafusion crate. If we did that then I think the boundaries of what could be built with a table provider (ListingTable being a good example) and what were built in might be more clear 🤔

[alamb]

Thank you @phillipleblanc --

https://github.com/spiceai/spiceai/tree/trunk/crates/data_components look basically like adapters for various databases like duckdb, sqlite etc as a TableProvider

I think @devinjdangelo and @backkem are working on some similar ideas in datafusion-federation: https://github.com/datafusion-contrib/datafusion-federation/tree/main/sources

If you would like, I would be happy to make a repo in datafusion-contrib (datafusion-contrib/datafusion-connector) for this code? Or maybe we could make different repos for the different connectors

• datafusion-contrib/datafusion-connector-duckdb
• datafusion-contrib/datafusion-connector-sqlite
• datafusion-contrib/datafusion-connector-fligthsql
• ...

🤔

[backkem]

Quick note on datafusion-federation: The project is focused specifically on federating query sub-plans. Rather then federating single tables, the goal is to federate the largest possible sub-plan that can be computed by a remote query engine. The sources are therefore slightly different TableProviders: instead of a single table scan, they send a larger query (SQL, Substrate or other) to a remote engine.
That being said, we're happy to support a datafusion-table-providers crate where possible.

[phillipleblanc]

https://github.com/spiceai/spiceai/tree/trunk/crates/data_components look basically like adapters for various databases like duckdb, sqlite etc as a TableProvider

Yes, exactly.

If you would like, I would be happy to make a repo in datafusion-contrib (datafusion-contrib/datafusion-connector) for this code? Or maybe we could make different repos for the different connectors

Perfect, I think having a single repo would make the most sense, since there is a lot of common code between different SQL providers. And as @matthewmturner mentioned, we could feature gate the different connectors.

Rather then federating single tables, the goal is to federate the largest possible sub-plan that can be computed by a remote query engine.

Yeah, we could make these complementary - the TableProviders/sources with the connection/query logic could live in the datafusion-connector crate. And if you want to federate them, then the datafusion-federation crate would adapt them to work with the modified federation query planner.

[alamb]

Hi @phillipleblanc -- I made https://github.com/datafusion-contrib/datafusion-table-providers and added you as an admin in case you want to start your project there / move any of the table providers.

[phillipleblanc]

Thank you, I'll start moving over PostgreSQL, MySQL and SQLite table providers soon. I'm at the OSS Summit in Seattle this week, so I might not get to it until next week.

[cisaacson]

Matthew, you do have a lot of flexibility and control with this capability. Cory

…

-- Cory Isaacson http://www.coryisaacson.com

On Apr 8, 2024 at 8:46 PM -0400, Matthew Turner ***@***.***>, wrote: That's an interesting idea. Something like a datafusion-table-providers crate and different implementations could live there that are feature gated? — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you were mentioned.Message ID: ***@***.***>

[matthewmturner]

I just came across sum_tree from Zeds dev log which is basically a concurrent B+ tree with customizable summary logic for each node.

They basically have SumTree<Chunks> and then ChunkSummary which wraps TextSummary.

I wonder if this could work for us as an index for parquet files with something like SumTree<Files> and FileSummary which wraps RowGroupSummary where the summaries are from file and row group statistics.

I haven't had the chance to dig into sum_tree (just read that article on a long drive) but thought I would bring it up here.

[matthewmturner]

I looked at the sum_tree crate and it actually doesnt appear to be a published crate although the code is open source. I plan to inquire with them to see if there are plans for publishing.

[PierreZ]

Hi 👋
I was about to start a discussion about how I could leverage Datafusion to handle my custom indexes in a different way than Option 1, glad to see there is a global discussion about this already 😄

My current software stack is something really close to Apple's Record-Layer, meaning that I'm using FoundationDB to store "LogicalDBs". A LogicalDB is simply a subspace of key-values organized into different sub-subpaces, including one where I have indexes.

Each index entry point to a "primay-key" item, allowing me to target the right data subspace.

For example, if I have a Rust structure with a value-field indexed, my key-values will roughly looks like this:

Key (represented as an ordered tuple)	Value
(ldb-1, data, pk_1)	Some data encoded
(ldb-1, idx, my_index, 42, pk_1)

Then, in my current implementation, I'm scanning the "my_index" subspace with the right range, and for each primary-keys found, asking for a new scan in the required pk subspace. This is actually using the Option 1 and it has the listed in the first post.

It is also quite tiedous code to maintain, and we would like to leverage Datafusion for this. This would allow us to use Datafusion expressions to query and manipulate multiple indexes, which is something I will need to write complex queries.

I feel like my need would be fullfilled by having something described as TakeExec (index lookup) -- really like an indexed scan somehow>, so that Datafusion handles the deduplication when multiple indexes are involved, before performing the actual data scan.

Similar to other areas of datafusion i think it would be very cool if a simple index implementation could be included out of the box, acting as a reference implementation, while still providing the relevant traits to allow users to customize as needed.

I deeply agree with @matthewmturner, and I would love to contribute to this feature 😄

[alamb]

https://github.com/datafusion-contrib/datafusion-index-provider created 🚀

[PierreZ]

https://github.com/datafusion-contrib/datafusion-index-provider created 🚀

Awesome, thanks 😊 I'm on holiday this week, but I will start working on this next week. I need to dive a bit into the Record-Layer to see how they are handling indexes, that could give some context 🤔

[PierreZ]

I'm not giving a lot of updates, but I'm having fun reading through datafusion 😄 I am close to have something working.

[alamb]

BTW if you want to see a peek of where we are headed with parquet indexing, check this out: #10701 (I don't think this is a general purpose indexing story, but it is pretty neat)

[PierreZ]

Thank you, that's really helpful to have more OLAP-oriented index usage

[Max-Meldrum]

This feature sounds interesting!

I recently made a post about a potential integration of µWheel into DataFusion as an index to speed up temporal aggregation queries significantly.

Example query:

SELECT SUM(fare_amount) FROM yellow_tripdata
WHERE tpep_dropoff_datetime >= '?' and < '?'

I'm Interested in helping out to make it supported natively by DataFusion!

[alamb]

I am working on an example of how one might use an external index for parquet files in DataFusion -- #10546

Let me get a little farther with that one then maybe we can do something similar for uWheel

[ozankabak]

We at Synnada are very much interested in this type of a thing as well, will be happy to collaborate on an integration along the lines of @alamb's suggestion.

[Max-Meldrum]

A prototype of a WheelManagercan be seen here: https://github.com/Max-Meldrum/wheel_manager/blob/main/examples/nyc_taxi.rs

Right, now it only builds a COUNTwheel for the table and MinMax wheels for specified columns that can be used for temporal pruning of queries.

For instance, a MinMax wheel on the fare_amountcolumn can be used to check whether the following query can be skipped:

SELECT * FROM yellow_tripdata
WHERE tpep_dropoff_datetime >= '?' and < '?'
AND fare_amount > 1000

The next step is to be able to build wheels based on a given DataFusion expr and decide how an API between DataFusion and the manager could look like.

If this combination of filters is requested frequently, then a wheel should be built for it:

SELECT SUM(fare_amount) FROM yellow_tripdata
WHERE tpep_dropoff_datetime >= '?' and < '?'
AND PULocationID = 120

[alamb]

This sounds awesome -- thank you @Max-Meldrum -- I hope to be able to check out your example in the next few days

[alamb]

I still have this on my radar @Max-Meldrum