Fix postgres_index_bloat_estimates view to support efficient single-index lookup

Summary

The postgres_index_bloat_estimates view is used by the reindexing pipeline to retrieve bloat estimates on a per-index basis. On some instances (with a lot of bloat on catalag tables), a single-index lookup via this view can take 7+ minutes instead of milliseconds - https://gitlab.com/gitlab-com/request-for-help/-/work_items/4641#note_3333863463. The fix requires a change to the view definition and to the Ruby model that queries it.

Problem

The view is queried by PostgresIndex#bloat_estimate through this ActiveRecord association:

SELECT "postgres_index_bloat_estimates".*
FROM "postgres_index_bloat_estimates"
WHERE "postgres_index_bloat_estimates"."identifier" = 'public.index_ci_builds_on_commit_id_and_type_and_name_and_ref'
LIMIT 1;
-- Execution Time: 441,438 ms

There are two structural problems in the view that compound into this:

1. generate_series in a SELECT list creates a ProjectSet barrier

The innermost subquery of the view expands index column positions like this:

SELECT idx_data.idxname, ..., generate_series(1, idx_data.indnatts::integer) AS attpos
FROM ( SELECT ... FROM pg_index JOIN pg_class ... ) idx_data

A set-returning function in the SELECT list is executed by PostgreSQL as a ProjectSet node — a hard barrier through which the planner cannot push predicates. This forces the planner to expand generate_series for all indexes before any useful filter can be applied. On a large GitLab instance with ~12,500 btree indexes this produces ~370 million intermediate rows.

2. identifier is a computed expression the planner cannot decompose

The outermost SELECT computes:

(nspname::text || '.'::text) || idxname::text AS identifier

When the query filters WHERE identifier = 'public.index_foo', the planner has no way to infer nspname = 'public' AND idxname = 'index_foo' from a string concatenation. So the filter is applied at the outermost layer after all intermediate rows have been generated.

Execution plan evidence

On the bad instance the plan shows:

Nested Loop  (actual time=441198 ms loops=1)
  Join Filter: ((n.nspname || '.' || ci.relname) = 'public.index_ci_builds_...')
  Rows Removed by Join Filter: 370,400,238
    ProjectSet (generate_series, loops=15742)

The planner put the pg_stats subquery (estimated 159 rows, actual 16,213 rows — a 100× misestimate) on the outer side of a nested loop with the ProjectSet on the inner side, causing generate_series to run 15,742 times.

This misestimate is caused by stale statistics on catalog tables (pg_statistic, pg_attribute, pg_class). Running VACUUM ANALYZE on those tables resolves the problem temporarily, but it recurs because when bloat accumulates.

The view's plan should not depend on catalog statistics being fresh. A correctly structured view would make the fast plan robust regardless of statistics quality.

Proposed Fix

1. Change the view: move generate_series to a LATERAL join and expose nspname/idxname separately

Replace the ic subquery (which holds the ProjectSet-causing SRF) by flattening it into its parent with a CROSS JOIN LATERAL. Also add nspname and idxname as separate columns to the outermost SELECT.

Key change in the innermost subquery — from:

SELECT idx_data.idxname, ..., generate_series(1, idx_data.indnatts::integer) AS attpos
FROM ( SELECT ci.relname AS idxname, ... FROM pg_index i_1 JOIN pg_class ci ... ) idx_data

To:

SELECT idx_data.idxname, ..., gs.attpos
FROM ( SELECT ci.relname AS idxname, ... FROM pg_index i_1 JOIN pg_class ci ... ) idx_data
CROSS JOIN LATERAL generate_series(1, idx_data.indnatts::integer) AS gs(attpos)

Outermost SELECT — from:

SELECT (nspname::text || '.'::text) || idxname::text AS identifier,
       bloat_size_bytes

To:

SELECT nspname,
       idxname,
       (nspname::text || '.'::text) || idxname::text AS identifier,
       bloat_size_bytes

2. Change the Ruby model to query by nspname + idxname

With nspname and idxname as separate columns, the planner can push equality predicates on them through the GROUP BY (both are GROUP BY keys in the view) all the way into the pg_class ci index scan.

In PostgresIndex:

def bloat_estimate
  schema, name = identifier.split('.', 2)
  PostgresIndexBloatEstimate.find_by(nspname: schema, idxname: name)
end

Execution plans

While the new view returns the same results as the existing one it's worth noting that it is slower for queries that return multiple rows, but this is not how we use it so it's not a problem:

gitlabhq_dblab=# select * from postgres_index_bloat_estimates order by bloat_size_bytes desc limit 10;
                               identifier                               | bloat_size_bytes 
------------------------------------------------------------------------+------------------
 public.merge_request_diff_commits_pkey                                 |     125251395584
 public.merge_request_diff_files_pkey                                   |      99174498304
 public.index_on_events_to_improve_contribution_analytics_performance   |      64688021504
 public.index_events_author_id_project_id_action_target_type_created_at |      52072054784
 public.index_events_author_id_group_id_action_target_type_created_at   |      49923121152
 public.index_events_for_followed_users                                 |      44118228992
 public.index_events_on_target_type_and_target_id_and_fingerprint       |      37574008832
 public.index_events_on_project_id_and_created_at                       |      36555636736
 public.index_events_on_author_id_and_created_at                        |      35334864896
 public.index_events_on_author_id_and_id                                |      34750504960
(10 rows)

Time: 1102.655 ms (00:01.103)
gitlabhq_dblab=# select * from postgres_index_bloat_estimates_new order by bloat_size_bytes desc limit 10;
 nspname |                             idxname                             |                               identifier                               | bloat_size_bytes 
---------+-----------------------------------------------------------------+------------------------------------------------------------------------+------------------
 public  | merge_request_diff_commits_pkey                                 | public.merge_request_diff_commits_pkey                                 |     125251395584
 public  | merge_request_diff_files_pkey                                   | public.merge_request_diff_files_pkey                                   |      99174498304
 public  | index_on_events_to_improve_contribution_analytics_performance   | public.index_on_events_to_improve_contribution_analytics_performance   |      64688021504
 public  | index_events_author_id_project_id_action_target_type_created_at | public.index_events_author_id_project_id_action_target_type_created_at |      52072054784
 public  | index_events_author_id_group_id_action_target_type_created_at   | public.index_events_author_id_group_id_action_target_type_created_at   |      49923121152
 public  | index_events_for_followed_users                                 | public.index_events_for_followed_users                                 |      44118228992
 public  | index_events_on_target_type_and_target_id_and_fingerprint       | public.index_events_on_target_type_and_target_id_and_fingerprint       |      37574008832
 public  | index_events_on_project_id_and_created_at                       | public.index_events_on_project_id_and_created_at                       |      36555636736
 public  | index_events_on_author_id_and_created_at                        | public.index_events_on_author_id_and_created_at                        |      35334864896
 public  | index_events_on_author_id_and_id                                | public.index_events_on_author_id_and_id                                |      34750504960
(10 rows)

Time: 386805.843 ms (06:26.806)

bloat-view-improved.sql

Edited by Krasimir Angelov