Hi, On 2025-02-21 16:24:38 -0500, Tom Lane wrote: > Oy. Those are outright horrid, even without any consideration of > pre-preparing them. We know the OID of the table we want to dump, > we should be doing "FROM pg_class WHERE oid = whatever" and lose > the join to pg_namespace altogether. The explicit casts to regclass > are quite expensive too to fetch information that pg_dump already > has. It already knows the server version, too.
> Moreover, the first of these shouldn't be a separate query at all. > I objected to fetching pg_statistic content for all tables at once, > but relpages/reltuples/relallvisible is a pretty small amount of > new info. We should just collect those fields as part of getTables' > main query of pg_class (which, indeed, is already fetching relpages). > On the second one, if we want to go through the pg_stats view then > we can't rely on table OID, but I don't see why we need the joins > to anything else. "WHERE s.schemaname = 'x' AND s.tablename = 'y'" > seems sufficient. Agreed on all those. > I wonder whether we ought to issue different queries depending on > whether we're superuser. The pg_stats view is rather expensive > because of its security restrictions, and if we're superuser we > could just look directly at pg_statistic. Maybe those checks are > fast enough not to matter, but ... It doesn't seem to make much of a difference, from what I can tell. At execution time most of the time is is in a) the joins to pg_attribute and pg_class (the ones in pg_stats) b) array_out(). The times get way worse if you dump stats for catalog tables, because there some of arrays are regproc and regprocout calls FuncnameGetCandidates(), which then ends up iterating over a long cached list... I think that's basically O(N^2)? Of course that's nothing we should encounter frequently, but ugh. Greetings, Andres Freund
