PrestoDB通过MongoDB连接器执行查询耗时过长求助

Hey there, let's break down how to speed up this query running through Presto's MongoDB connector. I've tackled similar slow cross-engine queries before, so here are actionable, practical steps:

1. Add Targeted Indexes in MongoDB #

The biggest win almost always comes from making sure MongoDB can quickly filter down the data before sending it to Presto. Your query uses two key filters:

• entryTime range
• contains(classId, '...') (string matching)

Create a compound index on these fields to let MongoDB prune most of the irrelevant documents upfront:

db.student.createIndex({ entryTime: 1, classId: 1 })

If your contains check is looking for a specific prefix, you could also use a prefix index, but the compound index above will still help narrow down results significantly even for arbitrary substring matches.

2. Ensure Filter Pushdown to MongoDB #

Presto can push some filter logic directly to MongoDB, but not all functions are supported. If contains(classId, '...') isn't being pushed down, Presto will pull all documents matching the entryTime range first, then filter on classId—that's a huge waste of time.

• Try replacing contains(classId, '...') with regexp_like(classId, '...') or classId LIKE '%...%' (depending on your matching needs). These are more likely to be translated to MongoDB's $regex and pushed down.
• Verify pushdown by checking Presto's query plan (run EXPLAIN on your query) or enabling connector logs. Look for mentions of "pushed filters" to confirm MongoDB is handling the heavy lifting.

3. Offload Aggregation to MongoDB Where Possible #

Right now, Presto is doing all the aggregation (sum + date calculation) after fetching data from MongoDB. Instead, let MongoDB handle as much of the work as possible, since it's optimized for its own data format.

You can run a pre-aggregation in MongoDB (either as a one-time job, scheduled task, or create a view) to reduce the data sent to Presto:

db.student.aggregate([
  // Filter exactly like your Presto WHERE clause
  { $match: {
      entryTime: { $gte: ISODate("2017-10-30T00:00:00Z"), $lte: ISODate("2018-05-15T23:59:59Z") },
      classId: { $regex: "..." } // Match your contains logic here
  }},
  // Calculate adjusted exit time just like your CASE statement
  { $addFields: {
      adjustedExitTime: { $cond: [
          { $lte: ["$exitTime", ISODate("2018-04-15T23:59:59Z")] },
          "$exitTime",
          ISODate("2018-04-15T23:59:59Z")
      ]}
  }},
  // Group and sum time spent
  { $group: {
      _id: { studentId: "$studentId", classId: "$classId" },
      timeSpent: { $sum: { $dateDiff: { startDate: "$entryTime", endDate: "$adjustedExitTime", unit: "day" } } }
  }}
])

Save this aggregation to a temporary collection or create a MongoDB view, then have Presto query that instead. You'll only fetch pre-aggregated results, which are way smaller in volume.

4. Tune Presto Connector Settings #

Tweak your Presto MongoDB connector config (usually in etc/catalog/mongodb.properties) to improve parallelism and connection handling:

• mongodb.split-size: Adjust this to split large MongoDB collections into more chunks, letting Presto read data in parallel. Start with 1GB or 512MB if your collection is massive.
• mongodb.max-connections: Increase this if you see connection wait times in logs—more connections mean Presto can pull data from MongoDB faster.
• Ensure mongodb.pushdown-filters=true (default, but double-check) to enable filter pushdown.

5. Fix Data Skew If Present #

If some studentId or classId groups have way more data than others, you'll hit data skew, where one Presto worker does most of the aggregation work. Fix this by adding a random grouping prefix for the initial aggregation:

SELECT studentId, classId, sum(timeSpent) as totalTimeSpent
FROM (
    SELECT 
        studentId, 
        classId, 
        sum(date_diff('DAY', entryTime, (CASE WHEN (exitTime <= TIMESTAMP '2018-04-15 23:59:59 UTC') THEN exitTime ELSE TIMESTAMP '2018-04-15 23:59:59 UTC' END))) as timeSpent,
        cast(rand() * 100 as int) as rand_group -- Split into 100 random subgroups
    FROM mongodb.school.student 
    WHERE entryTime BETWEEN TIMESTAMP '2017-10-30 00:00:00 UTC' AND TIMESTAMP '2018-05-15 23:59:59 UTC' 
      AND contains(classId, '...')
    GROUP BY studentId, classId, rand_group
) t
GROUP BY studentId, classId

This splits large groups into smaller chunks that can be processed in parallel, reducing worker bottlenecks.

内容的提问来源于stack exchange，提问作者Shubham A.