PostgreSQL查询性能优化咨询：2900万行Firms表复杂查询耗时近9秒的优化方案求助

Great question—let's dig into your execution plan and query to slash that runtime. First, here's what's dragging things down in the current plan:

• Wasted I/O from late filtering: The Bitmap Heap Scan pulls 56k rows matching CityId=4365 and RegionId=7, but only 322 of those end up having Properties that include 126 or 128. You're loading tons of rows just to discard them after unnesting the array.
• Filter conditions not covered by indexes: CountryId=1 and Deleted=FALSE are applied as post-scan filters, not part of the index lookup. That means the database is fetching rows that don't even meet these basic criteria.
• Unnecessary unnest + group by: Unnesting every Properties array and then grouping back by Id adds unnecessary overhead when we can calculate the matching count directly with array functions.

Here are actionable steps to fix this:

1. Build a composite index to pre-filter rows #

Create an index that covers all your core filtering conditions, plus the columns needed for the rest of the query. This lets PostgreSQL eliminate non-qualifying rows at the index level, avoiding expensive heap scans:

CREATE INDEX IX_Firms_Country_Region_City_Deleted ON public."Firms" 
("CountryId", "RegionId", "CityId", "Deleted") 
INCLUDE ("Id", "Name", "Url", "CountryModel", "RegionModel", "CityModel", 
        "Street", "Phone", "PostCode", "Images", "Rank", "CommentCount", 
        "PageRank", "Description", "Properties", "IsVerify");

The INCLUDE clause adds columns needed for the select list so PostgreSQL can do an index-only scan (no need to hit the table data at all if possible).

2. Rewrite the query to avoid unnest + group by #

Instead of unnesting the Properties array and counting matches, use PostgreSQL's array functions to calculate the match count directly. This eliminates the nested loop and group by entirely:

SELECT 
    F."Id", F."Name", F."Url", F."CountryModel", F."RegionModel", F."CityModel", 
    F."Street", F."Phone", F."PostCode", F."Images", F."Rank", F."CommentCount", 
    F."PageRank", F."Description", F."Properties", F."IsVerify",
    cardinality(array_intersect(F."Properties", ARRAY[126, 128])) AS Counter 
FROM public."Firms" F
WHERE 
    F."CountryId" = 1 
    AND F."RegionId" = 7 
    AND F."CityId" = 4365 
    AND F."Properties" && ARRAY[126, 128]::integer[] -- Check if array contains any target value
    AND F."Deleted" = FALSE 
ORDER BY Counter DESC, F."IsVerify" DESC, F."PageRank" DESC 
OFFSET 10 ROWS FETCH FIRST 20 ROW ONLY

The && operator checks if the array has any overlap with [126,128], and array_intersect + cardinality counts how many matches there are—no unnesting required.

3. Add a GIN index for array filtering #

To speed up the && array overlap check, create a GIN index on the Properties column:

CREATE INDEX IX_Firms_Properties_GIN ON public."Firms" USING GIN ("Properties");

GIN indexes are optimized for array containment checks, so this will let PostgreSQL quickly find rows where Properties includes 126 or 128.

4. Refresh table statistics #

Outdated statistics can lead the query planner to make bad decisions. Update them with:

ANALYZE public."Firms";

Why this works #

By combining the composite index (to filter rows by location and Deleted status) and the GIN index (to filter by Properties), PostgreSQL will only fetch the exact rows that meet all criteria—likely just a few hundred instead of 56k. Removing the unnest and group by cuts down on memory usage and CPU overhead, and the index-only scan (if possible) eliminates costly heap access.

You should see a massive drop in runtime after implementing these changes—probably from ~9 seconds to well under a second.

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