PostgreSQL 9.6中基于JOIN语句优化TS-vector查询的问题求助

Hey there! Let's tackle your PostgreSQL 9.6 issues step by step—both the duplicate lexems problem and the slow JOIN query performance. Here's a practical, actionable plan:

Duplicate texts (and thus duplicate tsvector lexems) aren't just a nuisance—they force your query to process redundant data, which kills performance when you use DISTINCT later. Here's how to fix this:

Long-Term Solution: Clean Up Duplicates & Prevent Future Ones #

First, eliminate existing duplicate text records. We'll keep the oldest record (by id) for each unique text and delete the rest:

WITH duplicate_texts AS (
    SELECT id, text,
           ROW_NUMBER() OVER (PARTITION BY text ORDER BY id) AS row_num
    FROM public.app_consumer_complaint_narrative
)
DELETE FROM public.app_consumer_complaint_narrative
WHERE id IN (SELECT id FROM duplicate_texts WHERE row_num > 1);

Next, add a unique constraint to stop duplicates from being inserted in the first place:

ALTER TABLE public.app_consumer_complaint_narrative 
ADD CONSTRAINT unique_complaint_text UNIQUE(text);

If you can't enforce a strict unique constraint (e.g., minor whitespace differences), consider normalizing the text first (trim whitespace, lowercase, etc.) before storing it.

Short-Term Workaround (If You Can't Delete Data) #

If you need to keep duplicates for now, avoid applying DISTINCT during aggregation (which is expensive). Instead, deduplicate the matching narrative IDs early in the query:

WITH unique_matching_narratives AS (
    SELECT DISTINCT id 
    FROM public.app_consumer_complaint_narrative
    WHERE fts_document @@ to_tsquery('english','fish')
)
-- Use this CTE for your joins instead of the raw table

This cuts down on redundant joins before you even get to grouping and aggregating.

Looking at your execution plan, the biggest bottlenecks are:

• The ARRAY_AGG(DISTINCT ...) forcing extra work during aggregation
• The Hash Join on app_complaint_main.company_id (no index here, so it's doing a full hash of the company table)
• Redundant rows being processed due to duplicate narratives

Here's an optimized query rewrite that addresses all these:

WITH matching_narratives AS (
    -- Step 1: Get only the narrative IDs that match the full-text search (deduplicated)
    SELECT DISTINCT id 
    FROM public.app_consumer_complaint_narrative
    WHERE fts_document @@ to_tsquery('english','fish')
),
complaint_company_mappings AS (
    -- Step 2: Join only necessary tables, with minimal data
    SELECT 
        ac.company_name,
        acm.complaint_id
    FROM public.app_complaint_main acm
    -- Join with the pre-filtered, deduplicated narratives first
    JOIN matching_narratives mn ON acm.consumer_complaint_narrative_id = mn.id
    -- Join with companies (add an index here to speed this up!)
    JOIN public.app_company ac ON acm.company_id = ac.id
    -- Optional: Add DISTINCT here ONLY if you have duplicate (company_name, complaint_id) pairs
    -- DISTINCT ac.company_name, acm.complaint_id
)
-- Step 3: Aggregate without DISTINCT (we already deduplicated earlier)
SELECT 
    company_name,
    ARRAY_AGG(complaint_id) AS ids
FROM complaint_company_mappings
GROUP BY company_name;

Key Optimizations in This Query: #

1. Early Filtering: We narrow down to matching narratives first, before joining to larger tables—this reduces the total number of rows processed in subsequent steps.
2. Remove Aggregation-Time DISTINCT: By deduplicating narratives early, we avoid the expensive DISTINCT inside ARRAY_AGG, which was forcing PostgreSQL to sort and compare values repeatedly.
3. Index Boost: Add a B-tree index on app_complaint_main.company_id to replace the Hash Join with a faster Merge Join or Nested Loop:

   CREATE INDEX idx_app_complaint_main_company_id 
   ON public.app_complaint_main(company_id);
   

   This will drastically speed up the join between app_complaint_main and app_company.

Quick Execution Plan Check #

After making these changes, your execution plan should show:

• A smaller number of rows flowing through each join step
• No DISTINCT in the Aggregate node
• An Index Scan (instead of Seq Scan/Hash) for the company_id join

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