Hi, How much partitioning affect query performance? So if we organise parquet files in S3 using column t1 as partition column and don‘t use t1 in where clause in queries, would this affect performance because Pinot then has to scan a lot more segments? Would indexing fix the issue? If I have two different types of queries with two different sets of filter columns in where clause, should I create two copies of my data and patition each as per the query filter columns and create two separate tables for the same data each optmized for a specific query? We have tables with size 300-500 GB with segment size 50-100MB and facing query timeouts.

if the predicate has the partition key, query routing will take it into account and prune only the matched partition.

so it makes sense to create two copies of same data partitioned with different columns and create two corresponding tables on pinot?

can you share the query types, I would say unless you need extremely low query latency otherwise let’s first try out stuffs like: partitioning/ bloomfilter/ sorted column/ inverted index/ startree index before create a new copy with different partition key

Lets take an example here. We are receiving new files every 15 minutes. There are 5 columns: 1. processing_time : TIMESTAMP 2. begin : TIMESTAMP 3. end : TIMESTAMP 4. bid : DOUBLE 5. ask : DOUBLE processing_time is the timestamp we attach to every row in the code when we create a parquet file and store it it S3. On S3, we partition the data by processing_time like this: year=2024/month=07/day=10/my_file.parquet where year, month and day are coming from processing_time For most of our tables, we are mostly interested in the latest data or the data from last week, so we partition the data with processing_time and use it in the where clause like this

SELECT * FROM my_table
WHERE processing_time > CAST(ago(”P7D") AS TIMESTAMP)

I assume this then queries only the files from last week and hence the query should take same time whether the data in the table is from last 6 months or last 6 years as it should not depend on table size. Now, for this table for example, I now want to make the query

SELECT * FROM my_table
WHERE begin BETWEEN "2022-01-01“ AND ”2022-01-08“

This query results in timeout where table size is 50GB. Here the data could be present across multiple files which were stored after 2022-01-01. I have tried to add a range index on begin column but it didn't improve things. Now we are thinking to repartition data using begin as partion key in S3 and create a separate table with this duplicate data. We will maintain one parquet file per day (of begin column). When we receive new data for that begin date again, we append to that file and regenerate segment for that file.