大数据集慢查询调优求助：图模型「看过还看过」推荐查询优化

Hey Rodrigo, let's tackle those slow "people who viewed this also viewed" queries—10 seconds for unfiltered and 60 for category-filtered is way too long given your dataset scale. Let's break down actionable steps to get these latencies down to something reasonable.

First, let's ground this: your 40M VIEW relationships are the backbone of this query, and the category-filtered version adds an extra join against 376k Product nodes and their BELONG edges to categories. The biggest culprits are likely:

• Unoptimized traversal order that pulls too much data early
• Indexes not being leveraged effectively for relationship lookups
• Late-stage filtering that forces the database to process unnecessary records

Let's start with fixing the query logic itself—small changes here can make huge differences.

Unfiltered Query (Baseline) #

Original Likely Query (Common Pitfall) #

MATCH (targetP:Product {id: $targetProduct})<-[:VIEW]-(u:User)-[:VIEW]->(otherP:Product)
WHERE otherP.id <> $targetProduct
RETURN otherP.id, COUNT(u) AS score
ORDER BY score DESC
LIMIT 20

This can slow down because it traverses all VIEW edges from users after pulling them, and doesn't deduplicate users who viewed the target product multiple times.

Optimized Version #

// First, get all unique users who viewed the target product (uses Product.id index)
MATCH (u:User)-[:VIEW]->(:Product {id: $targetProduct})
WITH DISTINCT u

// Then find other products these users viewed (uses User-VIEW-Product relationship indexing)
MATCH (u)-[:VIEW]->(otherP:Product)
WHERE otherP.id <> $targetProduct

// Aggregate and sort only the necessary data
WITH otherP, COUNT(DISTINCT u) AS score
ORDER BY score DESC
LIMIT 20
RETURN otherP.id, score

Key wins here:

• DISTINCT u removes duplicate user entries upfront, cutting down on redundant traversals
• We narrow down the user set first before pulling their other views, reducing the total data processed

Category-Filtered Query (Big Pain Point) #

Original Likely Query (Common Pitfall) #

MATCH (targetP:Product {id: $targetProduct})<-[:VIEW]-(u:User)-[:VIEW]->(otherP:Product)-[:BELONG]->(c:Category {id: $targetCategory})
WHERE otherP.id <> $targetProduct
RETURN otherP.id, COUNT(u) AS score
ORDER BY score DESC
LIMIT 20

This forces the database to traverse all other products viewed by users, then filter for those in the target category—wasting cycles on irrelevant products.

Optimized Version #

// Step 1: Get unique users who viewed the target product
MATCH (u:User)-[:VIEW]->(:Product {id: $targetProduct})
WITH DISTINCT u

// Step 2: Get all products in the target category FIRST (filters early)
MATCH (c:Category {id: $targetCategory})<-[:BELONG]-(otherP:Product)
WHERE otherP.id <> $targetProduct

// Step 3: Only find users who viewed both the target and category product
MATCH (u)-[:VIEW]->(otherP)

// Aggregate and sort
WITH otherP, COUNT(DISTINCT u) AS score
ORDER BY score DESC
LIMIT 20
RETURN otherP.id, score

Key wins here:

• We filter products to the target category before joining with user views, drastically reducing the number of nodes/relationships we need to process
• The traversal order prioritizes smaller datasets first, which Neo4j's query planner can optimize better

Even with rewritten queries, you need to make sure indexes are working for you. Double-check these:

• Node Property Indexes: Ensure you have unique indexes for Product.id, User.id, and Category.id (you mentioned you have indexes, but confirm uniqueness—this speeds up exact matches):

  CREATE CONSTRAINT FOR (p:Product) REQUIRE p.id IS UNIQUE;
  CREATE CONSTRAINT FOR (u:User) REQUIRE u.id IS UNIQUE;
  CREATE CONSTRAINT FOR (c:Category) REQUIRE c.id IS UNIQUE;
  

• Relationship Indexes: For VIEW relationships, create an index to speed up finding all views from a user:

  CREATE INDEX idx_user_view FOR (u:User)-[:VIEW]->();
  

• Category-Product Relationship Index: Speed up finding products in a category with:

  CREATE INDEX idx_product_belong FOR (p:Product)-[:BELONG]->();
  

If real-time querying still isn't fast enough (which is common with 40M relationships), precompute and store the recommendations upfront:

• Batch Precompute TopN Related Products: Use Neo4j's apoc.periodic.iterate to run the optimized query for every product daily (or hourly, if freshness allows) and store results as a property on the Product node:

  CALL apoc.periodic.iterate(
    "MATCH (p:Product) RETURN p",
    "MATCH (u:User)-[:VIEW]->(p)
     WITH DISTINCT u
     MATCH (u)-[:VIEW]->(otherP:Product)
     WHERE otherP.id <> p.id
     WITH otherP, COUNT(DISTINCT u) AS score
     ORDER BY score DESC LIMIT 20
     SET p.relatedProducts = COLLECT({id: otherP.id, score: score})",
    {batchSize: 100, parallel: true}
  )
  

• Category-Specific Precomputation: For category-filtered recommendations, add a categoryRelatedProducts property that maps category IDs to their top related products, using similar batch logic.

Always use PROFILE or EXPLAIN to check where the query is spending time:

• Run PROFILE before your query to see DB Hits, row counts, and which indexes are being used
• Look for AllNodesScan or AllRelationshipsScan—these mean indexes aren't being leveraged, and you need to fix your indexes or query logic
• Focus on the step with the highest DB Hits—this is your bottleneck

• Add Time Filtering: If your VIEW relationships have a date property, limit results to recent views (e.g., last 30 days) to reduce data volume and improve relevance:

  MATCH (u:User)-[v:VIEW]->(:Product {id: $targetProduct})
  WHERE v.date > datetime() - duration({days: 30})
  WITH DISTINCT u
  

• Keep LIMIT Small: Users rarely need more than 20-50 recommendations, so don't return more than necessary—sorting large datasets is expensive.

内容的提问来源于stack exchange，提问作者Rodrigo Lacerda