PostgreSQL列间差值求和高效实现及SIMD支持技术问询

Hey there! Let's break down your problem—optimizing that O(n²) user matching logic that's become your performance bottleneck—and cover PostgreSQL's capabilities for vectorized/SIMD operations, plus other practical optimizations.

PostgreSQL's SIMD & Vectorized Execution Support #

First off, you don't need those old, unmaintained extensions you found. PostgreSQL has built-in vectorized execution (which leverages SIMD under the hood) starting from version 12, and it's designed to accelerate exactly the kind of arithmetic operations and aggregations you're using:

• Automatic SIMD utilization: For functions like ABS() and aggregate functions like SUM(), PostgreSQL will automatically use SIMD instructions if your CPU supports them (most modern CPUs do) and if the query is structured to trigger vectorized execution. This happens behind the scenes—you don't need to write any special syntax.
• Check your setup: Make sure your PostgreSQL build was compiled with SIMD support (most official packages and major distro builds enable this by default). You can verify by checking the output of pg_config --configure for flags like --enable-simd.
• JIT Compilation: If you're on PostgreSQL 11+, enabling JIT (Just-In-Time) compilation can further speed up the arithmetic and aggregation steps. You can turn it on via the jit configuration parameter.

Algorithm Optimization: Replace O(n²) Program Logic with SQL Joins #

The biggest win here is moving the matching logic from your application code into PostgreSQL itself, using efficient joins and indexes instead of looping through every user pair. Here's how to structure the query for a target user (e.g., user 1):

WITH target_user_data AS (
  SELECT item, value
  FROM user_item_values
  WHERE user = 1 -- Replace with your target user ID
)
SELECT
  1 AS first_user,
  u2.user AS second_user,
  SUM(ABS(tuv.value - u2.value)) AS result
FROM target_user_data tuv
JOIN user_item_values u2
  ON tuv.item = u2.item AND u2.user != 1
GROUP BY u2.user;

Key Performance Tweaks for This Query: #

• Indexing: Create a composite index on (user, item) (or (item, user), depending on your query patterns) to let PostgreSQL quickly locate the target user's data and find matching items for other users:

  CREATE INDEX idx_user_item ON user_item_values (user, item);
  

• Avoid Full Table Scans: The index above ensures PostgreSQL only scans the relevant rows for the target user and their matching peers, instead of the entire table.
• Vectorized Execution: PostgreSQL's query planner will automatically use vectorized operations for the ABS() calculation and SUM() aggregation if possible, cutting down on per-row overhead.

Alternative: Smart Caching (Instead of Full N×N Cache) #

A full N×N cache table is impractical, but you can implement incremental caching to keep results fresh without the huge storage footprint:

• Cache Table: Create a table user_match_results with columns first_user, second_user, result, last_updated.
• Trigger-Based Updates: Add triggers to your user_item_values table that, when a row is inserted/updated/deleted, recalculate the match results only for the affected user and all other users who share items with them. This way, you only update relevant entries instead of the entire cache.
• Lazy Loading: For less frequently accessed user pairs, calculate the result on the fly and cache it, so you only store results that are actually used.

Bonus Optimization Tips #

• Partitioning: If your table is massive (millions/billions of rows), partition it by user or item to limit the amount of data PostgreSQL needs to scan for each query.
• Materialized Views: If real-time results aren't strictly required, use a materialized view to precompute match results for frequently queried users, and refresh it on a schedule (e.g., hourly).
• Work Mem Adjustment: Increase the work_mem configuration parameter temporarily for this query (or globally if your server has enough RAM) to let PostgreSQL perform aggregations in memory instead of writing to disk.

内容的提问来源于stack exchange，提问作者Ryan Peschel