Same index, same query, different performance: what heap layout reveals in a PostgreSQL migration to the cloud

A case reported by the Azure Database for PostgreSQL support team shows that the execution plan (Index Scan) can be identical in two environments and still hide a brutal difference: how many heap pages the query needs to visit.

The symptom: same plan, very different performance

An engineer who provides support to Azure Database for PostgreSQL customers reported on Planet PostgreSQL (also published on Microsoft's blog for PostgreSQL) a case of migration from on-premises to the cloud. The query took a few dozen milliseconds in the source environment. In the comparison environment on Azure, the same query, using the same index, took 1-2 seconds on a cold cache. Running it again was fast, because the pages entered cache. As soon as the cache was flushed, the high time returned.

The first suspicion fell on storage: the difference in IOPS consumption between the two servers (10% to 20% higher in the environment that would be production) seemed to point to that. The second suspicion was the index: bloat, fragmentation, leaf density. The customer ran VACUUM FULL and saw no improvement at all. It is exactly the kind of shallow diagnosis any DBA has seen before: looking at the wrong symptom because the execution plan does not change, but the work behind it changes a lot.

Ruling out the obvious suspects

The team used pg_stattuple and pg_statindex to inspect leaf density and index fragmentation. Nothing was out of the ordinary. Storage telemetry also did not indicate an infrastructure problem. With the two easiest hypotheses ruled out, the team went back to the basic principle of how an Index Scan works: the index locates the matching tuples, but afterwards it is necessary to visit the table's heap pages to fetch the data, because the heap is not ordered like the index.

In summary: two queries can show Index Scan in EXPLAIN and still do completely different amounts of work. If 200 returned rows are concentrated in five heap pages, I/O cost is low. If the same 200 rows are spread across 100 pages, Postgres needs to bring a much larger working set into memory, even using the right index and with an apparently identical execution plan.

Correlation: the clue that doesn't close the case by itself

The team compared EXPLAIN (ANALYZE, BUFFERS) in both environments, looking beyond the scan type at buffer hits and reads. The technical warning made in the report is worth noting: a shared-buffer read only means the page was not in PostgreSQL's shared buffers, not necessarily that it came from physical disk, since the operating system can keep it cached. Enabling track_io_timing helps distinguish cheap reads from expensive reads in this scenario.

A relevant clue appeared in pg_stats:

SELECT correlation
FROM pg_stats
WHERE schemaname = 'public'
AND tablename = 'order_items'
AND attname = 'order_id';

correlation measures how much the logical order of a column follows the physical order of the table. Values close to 1 or -1 indicate strong ordering; close to 0, little relation. The planner uses this statistic to estimate the cost of an index scan. In the slow environment, correlation was around 0.037; in the comparison environment, around 0.571. The difference was too large to be noise.

But correlation is a sampled statistic, global for the entire column. It does not say how many heap pages contain the rows for ONE specific search. It is perfectly possible to have groups of related rows physically packed together without that pattern showing up in a global ordering. What was needed was to measure exactly what the query needed, not a table-wide average.

The definitive diagnosis: counting heap pages via ctid

PostgreSQL exposes the system column ctid, which identifies the physical location of a tuple as (block_number, tuple_number). By extracting the block number, you can count how many distinct heap pages contain the rows for a specific search:

SELECT
 count(*) AS rows_found,
 count(DISTINCT split_part(
 trim(both '()' FROM ctid::text), ',', 1
 )) AS distinct_heap_pages
FROM public.order_items
WHERE order_id = 12345;

For a partitioned table, the adjustment is to count distinct pairs of (tableoid, block_number), since different partitions can reuse the same block numbers. It is worth remembering that ctid is a point-in-time diagnostic tool: updates and table rewrites change the value, so it does not serve as a permanent row identifier.

The numbers for two representative search values made the problem evident:

For search B, the slow environment needed to visit about 20 times more heap pages to retrieve the same set of rows. These numbers do not tell the whole I/O story (index pages and other accesses also matter), but they precisely expose the physical footprint of that query. The index was not failing to find the right rows. The rows were simply spread across many more pages.

How the data ended up spread out

The customer had loaded the data into the Azure environment in parallel, a common practice to gain load throughput. The problem is that concurrent loading interleaved rows from different search values in the destination heap. Rows that were physically grouped together in the source no longer stayed together.

This is not an automatic rule: parallel loading does not by itself guarantee a spread-out table, the result depends on how the work is divided and how the inserts arrive at the heap. In this specific case, however, the loading method fully explained the layout difference observed between the two environments.

A detail every DBA should internalize: recreating the index solved nothing. A B-tree orders its own entries; building or rebuilding the index does not reorder the underlying table. The index was correct, the problem was physical, in the heap.

This also explains the cache behavior observed at the beginning: repeated executions benefited from pages already cached, masking the real cost of the larger footprint. When those pages were evicted, the query had to fetch them again, and a more spread-out layout means more pages competing for cache space.

VACUUM FULL doesn't fix it, CLUSTER and pg_repack require caution

VACUUM FULL rewrites the table to reclaim space from dead tuples and compact the heap, but it does not order the surviving rows by the search key. It may even reduce page count if there is bloat, but related rows remain interleaved with unrelated rows after the operation. The problem here was not fragmentation or bloat: it was about which tuples end up arranged together on disk.

The next step is to reorganize the heap around the search key. Two tools address this, with distinct trade-offs:

• CLUSTER: rewrites the table in index order, but requires an ACCESS EXCLUSIVE lock during the operation, which forces scheduling a careful maintenance window in production.
• pg_repack with --order-by: was the option used by the customer, reorganizing the data on a restored test instance, with reported performance improvement and without the exclusive lock of CLUSTER.

Neither of the two is a maintenance-free operation: before choosing, check extension support, available free space and the specific environment's locking requirements. After running either one, update statistics with ANALYZE, repeat the distinct-pages query via ctid, and compare plans and times under comparable cache conditions. A practical warning from the report deserves repeating: the diagnostic query itself warms pages, so do not treat the immediately following execution as a cold-cache test.

It is also necessary to understand that neither CLUSTER nor pg_repack maintain the ordering forever. Future writes gradually erode physical locality, so both the loading strategy of a migration and the application's ongoing write pattern deserve permanent attention, not a one-time fix.

What this changes for those migrating to the cloud

The central point of the case is not "Azure needs bigger disks" nor "don't load data in parallel." It is that migrating the same rows and the same indexes does not guarantee migrating the same physical layout. An identical EXPLAIN, with the same scan type and the same index, can hide completely different amounts of I/O depending on how the rows ended up arranged in the destination heap.

For those planning a PostgreSQL migration from on-premises to any cloud provider, the case suggests a minimal checklist before blaming storage or the index: look at correlation in pg_stats as a first signal, confirm with a distinct-pages count via ctid for the application's actual search keys, and only then decide between CLUSTER and pg_repack to fix the layout. Skipping this step and switching instances or raising IOPS is treating the wrong symptom with the wrong tool.