another question related to importing data: we have imported our last 2 years worth of data into pinot using the standalone job in Dev however we are observing things between our 2 different behavior for the same query in prod/dev (prod doesn’t have this historical data yet but it does have data for this particular time range). Performance in dev is way slower. this query:

SELECT product_id, SUM(impression_count) as impression_count, SUM(click_count) as click_count, SUM(cost) as spent_total FROM metrics WHERE user_id = xxx AND serve_time BETWEEN 1651363200 AND 1654012799 GROUP BY product_id LIMIT 6000

production metadata response:

"numServersQueried": 4,
  "numServersResponded": 4,
  "numSegmentsQueried": 97,
  "numSegmentsProcessed": 31,
  "numSegmentsMatched": 31,
  "numConsumingSegmentsQueried": 1,
  "numDocsScanned": 15109,
  "numEntriesScannedInFilter": 0,
  "numEntriesScannedPostFilter": 60436,
  "numGroupsLimitReached": false,
  "totalDocs": 493642793,
  "timeUsedMs": 32,
  "offlineThreadCpuTimeNs": 0,
  "realtimeThreadCpuTimeNs": 0,
  "offlineSystemActivitiesCpuTimeNs": 0,
  "realtimeSystemActivitiesCpuTimeNs": 0,
  "offlineResponseSerializationCpuTimeNs": 0,
  "realtimeResponseSerializationCpuTimeNs": 0,
  "offlineTotalCpuTimeNs": 0,
  "realtimeTotalCpuTimeNs": 0,
  "segmentStatistics": [],
  "traceInfo": {},
  "minConsumingFreshnessTimeMs": 1654715649414,
  "numRowsResultSet": 9708

dev metadata response:

"exceptions": [],
  "numServersQueried": 4,
  "numServersResponded": 4,
  "numSegmentsQueried": 11703,
  "numSegmentsProcessed": 31,
  "numSegmentsMatched": 31,
  "numConsumingSegmentsQueried": 1,
  "numDocsScanned": 15117,
  "numEntriesScannedInFilter": 0,
  "numEntriesScannedPostFilter": 60468,
  "numGroupsLimitReached": false,
  "totalDocs": 51283295726,
  "timeUsedMs": 580,
  "offlineThreadCpuTimeNs": 0,
  "realtimeThreadCpuTimeNs": 0,
  "offlineSystemActivitiesCpuTimeNs": 0,
  "realtimeSystemActivitiesCpuTimeNs": 0,
  "offlineResponseSerializationCpuTimeNs": 0,
  "realtimeResponseSerializationCpuTimeNs": 0,
  "offlineTotalCpuTimeNs": 0,
  "realtimeTotalCpuTimeNs": 0,
  "segmentStatistics": [],
  "traceInfo": {},
  "minConsumingFreshnessTimeMs": 1654716958681,
  "numRowsResultSet": 9708

amount of segments in prod: 1600 amount of segments in dev: 13000 I guess my question is that I see segments queried be way higher in dev and I’m wondering why and if that’s the reason why the query is just performing slower in dev it’s almost equal to the amount of segments that exist in the cluster while prod is only querying a tiny portion. Do you have an idea as to what may be happening?

Probably your dev data is not partitioned.

i thought partitioning was only for QPS

Is your question on

"numSegmentsQueried": 11703,

?

yes

Partitioning improves QPS by reducing

numSegmentsQueried

dev is a replica of prod in terms of specs

Num nodes, jvm configs, all other variables match exactly?

yes everything is the same

Check segment metadata in dev to see if it shows 1 partition or multiple

dev however has way more data because we haven’t run that import

It is not partitioned ^^

that means it’s not partitioned

Yes

would that explain that bottleneck we are seeing?

It might, depending on main memory vs total segment size on disk, whether you are running other load at the time, and other factors.

all the queries in general are way slower in dev that they are on prod

And that’s what I am explaining above.

i have this config on offline

"segmentPartitionConfig": {
        "columnPartitionMap": {
          "user_id": {
            "functionName": "Murmur",
            "numPartitions": 8
          }
        }
      },
"routing": {
      "segmentPrunerTypes": [
        "partition"
      ]
    },