PostgreSQL含函数复合索引的大表分组查询性能过慢问题求助

Alright, let's tackle this slow PostgreSQL query issue head-on. You've got a 94M-row Log table, a functional index that's being ignored, and a query that takes 4 minutes in PostgreSQL vs. just 10 seconds in SQL Server—let's break down what's going wrong and fix it.

First, let's look at the disconnect here:

• You created an index on ("UserId", ("DateStamp"::date), "Result")
• Your query groups by UserId, date("DateStamp"), and Result

While date(DateStamp) and DateStamp::date are logically equivalent, PostgreSQL's query planner matches expressions exactly when looking for index candidates. Even a tiny syntax difference can throw it off. Additionally, outdated stats or misconfigured cost parameters might make the planner think a full table scan + sort is cheaper than using your index.

The execution plan confirms this: it's doing a full sequential scan, then an external disk sort (which is why it's taking so long—disk I/O is way slower than in-memory operations or index scans).

a. Update Statistics First #

Outdated table statistics are one of the most common reasons the planner makes bad decisions. Refresh them with:

ANALYZE "Log";

This gives PostgreSQL accurate data about your table's contents, so it can properly evaluate whether using the index is worth it.

b. Align Query and Index Expressions #

Make sure the expression in your query matches exactly what's in the index. You have two options here:

Option 1: Rewrite the query to match the index #

Change date("Log"."DateStamp") to ("Log"."DateStamp"::date) in both the SELECT and GROUP BY clauses:

SELECT "Log"."UserId", ("Log"."DateStamp"::date) AS "Date", count(*) AS "Conversions", "Log"."Result"
FROM "Log"
GROUP BY "Log"."UserId", ("Log"."DateStamp"::date), "Log"."Result";

Option 2: Rewrite the index to match the query #

If you prefer keeping the query as-is, recreate the index using the date() function syntax:

DROP INDEX IF EXISTS "IX_Log_UserId_DateStampDate_Result";
CREATE INDEX "IX_Log_UserId_DateStampDate_Result" ON "Log" ("UserId", date("DateStamp"), "Result");

Either way, exact expression matching is key for the planner to pick up the index.

c. Tune PostgreSQL Configuration for Your Hardware #

Your current settings are underutilizing your 16GB server:

• shared_buffers: Set this to ~25% of your total RAM (4GB for 16GB systems). This lets PostgreSQL cache more data and indexes in memory.

  shared_buffers = 4GB
  

• random_page_cost: Since you're using a fast SSD, random reads are much cheaper than PostgreSQL's default assumption. Lower this to 1.1 or 1.2 to encourage the planner to choose index scans:

  random_page_cost = 1.1
  

• work_mem: Your sort is using 1.7GB of temporary disk space. Bump this to 64MB (or even 128MB) to let smaller sorts happen in memory (though fixing the index scan should eliminate this sort entirely):

  work_mem = 64MB
  

After changing these settings, restart PostgreSQL to apply them.

d. Test with Forced Index Scan (Temporary) #

To confirm the index would help, temporarily disable sequential scans and run your query:

SET enable_seqscan = off;
-- Run your original query here
SELECT "Log"."UserId", date("Log"."DateStamp") AS "Date", count(*) AS "Conversions", "Log"."Result"
FROM "Log"
GROUP BY "Log"."UserId", date("Log"."DateStamp"), "Log"."Result";
SET enable_seqscan = on; -- Don't leave this off permanently!

If this runs significantly faster, it confirms the planner's cost model was off, and the above config tweaks should fix it permanently.

e. Consider a Generated Column (Long-Term Fix) #

If you frequently query/group by the date part of DateStamp, add a stored generated column to avoid repeated expression calculations and make indexing even more reliable:

ALTER TABLE "Log" ADD COLUMN "DateStampDate" date GENERATED ALWAYS AS ("DateStamp"::date) STORED;
CREATE INDEX "IX_Log_UserId_DateStampDate_Result" ON "Log" ("UserId", "DateStampDate", "Result");

Then update your query to use this column:

SELECT "Log"."UserId", "Log"."DateStampDate" AS "Date", count(*) AS "Conversions", "Log"."Result"
FROM "Log"
GROUP BY "Log"."UserId", "Log"."DateStampDate", "Log"."Result";

Generated columns store the date value directly in the table, so no runtime conversion is needed, and the planner will have no trouble matching the index.

SQL Server's query planner has different cost models and may be more aggressive about using functional indexes for grouping operations. It also has optimized memory management and sorting algorithms that can handle large datasets more efficiently out of the box. But with the right tweaks, PostgreSQL should be able to match (or even exceed) that performance.

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