PostGIS中GIST索引查询慢于无索引的问题咨询

1. Why does PostgreSQL use both Index Cond and ST_Intersects filter? #

Looking at your execution plan, you’ll see two layers of filtering:

• Index Cond: (point && '...'::geometry)
• Filter: st_intersects(point, '...'::geometry)

These are not redundant checks—they work together to balance speed and accuracy:

• The GIST spatial index relies on the && operator, which checks if the bounding box of your point overlaps with the bounding box of the target envelope. This is a fast, low-cost check that quickly eliminates rows that can’t possibly match, drastically reducing the number of rows that need further processing.
• The ST_Intersects function performs a precise geometric validation. Bounding box overlap doesn’t guarantee the point is actually inside the envelope (e.g., a point near the edge of the envelope’s bounding box might lie outside the actual polygon). This filter ensures only rows with points truly inside the envelope are kept.

In short: the index condition is a quick preliminary filter to narrow down candidates, while ST_Intersects provides the exact match needed for correct results.

2. Will index query outperform parallel sequential scan as data grows? #

Yes, but it depends on how the proportion of matching rows changes relative to total table size. Here’s the breakdown:

Why index scan is slower today #

Your query returns a large portion of your table (the execution plan estimates ~1.6 million rows from a 1 million-row table—likely an overestimate, but still a significant chunk). For large result sets:

• Parallel sequential scans are efficient because they read data in contiguous blocks (sequential I/O), which is much faster than the random I/O required to jump between the index and table for each matching row in an index scan.
• The parallel scan uses multiple worker processes to split the workload, further speeding up the operation.

When index scan will become faster #

As your table grows:

• If the number of rows matching your envelope stays roughly the same (or grows more slowly than the total table), the percentage of matching rows will shrink. At a certain threshold, the cost of scanning the entire table (even in parallel) will exceed the cost of using the index to locate only the relevant rows.
• Additionally, sorting only the matched subset (instead of the entire table) will become more efficient, as the index reduces the number of rows that need to be sorted for your ORDER BY delivery_timestamp clause.

That said, if your data remains concentrated in the 10x10km area and your query continues to return a large fraction of the table, sequential scan might still be faster. But for most real-world scenarios where data expands beyond the target envelope, the index will eventually outperform the sequential scan.

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