修改Redshift排序键未改变查询耗时问题排查

Let's break down why you're seeing identical query times even though one table is sorted on the column you're filtering by. First, the big clue from your execution plans: both tables are doing a sequential scan (Seq Scan). The cost estimates differ, but actual runtime is the same—here are the most likely reasons:

1. The contract_id you're filtering has an enormous number of rows #

Most database optimizers will pick a sequential scan over leveraging a sorted structure when the matching rows make up a huge chunk of the total table (usually 20-30% or more). Looking at the row estimates in your plans:

• For the table sorted on invoice_id: ~11.4 million rows estimated to match
• For the table sorted on contract_id: ~10.3 million rows estimated to match

If your total table size is in this same ballpark, that means contract_id = 104416 accounts for nearly all the data in the table. In this scenario, scanning the entire table is faster than trying to navigate the sorted layout to pick out matching rows—there's no efficiency gain from the sort order when you're reading almost every row anyway.

2. Your "sorted key" doesn't fix the distributed data problem (Greenplum-specific) #

Since your execution plans have the XN prefix, this is clearly Greenplum (a distributed PostgreSQL-based database). In Greenplum, just sorting a table on a column doesn't speed up filtering much if the distribution key (the column that determines which segment node stores each row) isn't aligned with your filter:

• If both tables use the same distribution key (e.g., invoice_id), rows with contract_id = 104416 are spread across every segment node. Even if one table is sorted on contract_id, each node still has to scan its entire local dataset to find matches—there's no way to skip segments or narrow down to a small subset of data on each node.
• To get a real performance boost, you'd need the table to be distributed on contract_id (so all matching rows live on a small number of segments) and sorted on it.

3. Query caching is hiding the true performance gap #

If you ran the two queries back-to-back, it's possible the first query loaded most of the table data into the operating system's page cache or Greenplum's buffer cache. The second query would then read from fast memory instead of slow disk, making runtime nearly identical regardless of the table's structure. To test the real difference, try running each query after clearing the cache (or waiting for cache to evict old data) and measure again.

4. Outdated statistics might be misleading the optimizer #

While less likely here (since both plans chose sequential scans), if Greenplum's table statistics are outdated, the optimizer might not have accurate data to decide whether using the sorted structure would help. Run ANALYZE table_name; on both tables to refresh statistics, then recheck the execution plans.

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