Follow-up to #30, which fixes the Overview widgets and deliberately leaves this one alone.
What happens
TableResource::getEloquentQuery() adds pg_total_relation_size(pg_stat_user_tables.relid) AS total_bytes to every row, and the list opens with ->defaultSort('total_bytes', 'desc'). Ordering by a computed expression means PostgreSQL has to compute it for every table before it can return the first page: one stat of every heap, TOAST and index file in the database, on each load, sort click, filter change and page turn. The query goes through Eloquent rather than ReadOnlyExecutor, so no statement timeout bounds it.
Measured
Same 160 GB database (~500 tables, PostgreSQL 17 on Heroku), the list query with LIMIT 10:
| Ordered by |
Time |
total_bytes desc (the default), database idle |
0.6 s |
total_bytes desc, database under load (earlier the same day) |
4.9 s |
n_live_tup desc, same select list |
55 ms |
n_live_tup desc, without the size expression |
46 ms |
The third row is the useful one: when the list is not ordered by size, PostgreSQL only evaluates pg_total_relation_size() for the ten rows on the page, so the column itself is almost free. What costs is using it as the sort key, which is also the default. Under sustained load the equivalent query behind LargestTables averaged 62 s a call in pg_stat_statements, which is the scale this one reaches too.
Possible directions
Listed rather than chosen, since each one trades something:
- Open the list on a cheap default sort (
relname, or n_live_tup desc) and keep total_bytes sortable. Every default load drops to ~50 ms, and "biggest first" stays one click away — paying the full cost only when someone asks for it. The smallest change, but it moves the answer the list currently leads with.
- Sort by an estimate, show the exact figure. Order by
pg_class.relpages (heap + TOAST + indexes) and keep pg_total_relation_size() for display only. Cheap, but in our data relpages is stale enough to misorder real tables: a 15 GB table never re-analyzed dropped out of the top 8 entirely. Not safe as the only key.
- Cache the per-table sizes for a few minutes and sort in SQL against the cached values (e.g. a
VALUES list or a temp relation). Exact and fast, but it brings a cache store into a package that currently has none, and a value that is minutes old.
- Bound it with a statement timeout (as
ReadOnlyExecutor does for the raw queries) regardless of which of the above you pick, so a loaded server gets an error on the list instead of a statement that runs for a minute.
(1) plus (4) looks like the most proportionate to me, but it is your call on what the list should lead with. I'm happy to send the PR for whichever you prefer.
Follow-up to #30, which fixes the Overview widgets and deliberately leaves this one alone.
What happens
TableResource::getEloquentQuery()addspg_total_relation_size(pg_stat_user_tables.relid) AS total_bytesto every row, and the list opens with->defaultSort('total_bytes', 'desc'). Ordering by a computed expression means PostgreSQL has to compute it for every table before it can return the first page: one stat of every heap, TOAST and index file in the database, on each load, sort click, filter change and page turn. The query goes through Eloquent rather thanReadOnlyExecutor, so no statement timeout bounds it.Measured
Same 160 GB database (~500 tables, PostgreSQL 17 on Heroku), the list query with
LIMIT 10:total_bytes desc(the default), database idletotal_bytes desc, database under load (earlier the same day)n_live_tup desc, same select listn_live_tup desc, without the size expressionThe third row is the useful one: when the list is not ordered by size, PostgreSQL only evaluates
pg_total_relation_size()for the ten rows on the page, so the column itself is almost free. What costs is using it as the sort key, which is also the default. Under sustained load the equivalent query behindLargestTablesaveraged 62 s a call inpg_stat_statements, which is the scale this one reaches too.Possible directions
Listed rather than chosen, since each one trades something:
relname, orn_live_tup desc) and keeptotal_bytessortable. Every default load drops to ~50 ms, and "biggest first" stays one click away — paying the full cost only when someone asks for it. The smallest change, but it moves the answer the list currently leads with.pg_class.relpages(heap + TOAST + indexes) and keeppg_total_relation_size()for display only. Cheap, but in our datarelpagesis stale enough to misorder real tables: a 15 GB table never re-analyzed dropped out of the top 8 entirely. Not safe as the only key.VALUESlist or a temp relation). Exact and fast, but it brings a cache store into a package that currently has none, and a value that is minutes old.ReadOnlyExecutordoes for the raw queries) regardless of which of the above you pick, so a loaded server gets an error on the list instead of a statement that runs for a minute.(1) plus (4) looks like the most proportionate to me, but it is your call on what the list should lead with. I'm happy to send the PR for whichever you prefer.