pá 17. 9. 2021 v 11:10 odesílatel Justin Pryzby <pry...@telsasoft.com> napsal:
> On Wed, Jul 14, 2021 at 07:42:33AM +0200, Laurenz Albe wrote: > > Besides, schemas are not physical, but logical containers. So I see a > point in > > measuring the storage used in a certain tablespace, but not so much by > all objects > > in a certain schema. It might be useful for accounting purposes, though. > > We use only a few schemas, 1) to hide child tables; 2) to exclude some > extended > stats from backups, and 1-2 other things. But it's useful to be able to > see > how storage is used by schema, and better to do it conveniently. > > I think it'd be even more useful for people who use schemas more widely > than we > do: > "Who's using all our space?" > \dn++ > "Oh, it's that one - let me clean that up..." > > Or, "what's the pg_toast stuff, and do I need to do something about it?" > > > But I don't expect it to be in frequent enough demand to add a psql > command. > > > > What about inventing a function pg_schema_size(regnamespace)? > > But for "physical" storage it's also possible to get the size from the OS, > much > more efficiently, using /bin/df or zfs list (assuming nothing else is using > those filesystems). The pg_*_size functions are inefficient, but psql > \db+ and > \l+ already call them anyway. > > For schemas, there's no way to get the size from the OS, so it's nice to > make > the size available from psql, conveniently. > > v3 patch: > - fixes an off by one in forkNum loop; > - removes an unnecessary subquery in describe.c; > - returns 0 rather than NULL if the schema is empty; > - adds pg_am_size; > > regression=# \dA++ > List of access methods > Name | Type | Handler | Description > | Size > > --------+-------+----------------------+----------------------------------------+--------- > brin | Index | brinhandler | block range index (BRIN) access > method | 744 kB > btree | Index | bthandler | b-tree index access method > | 21 MB > gin | Index | ginhandler | GIN index access method > | 2672 kB > gist | Index | gisthandler | GiST index access method > | 2800 kB > hash | Index | hashhandler | hash index access method > | 2112 kB > heap | Table | heap_tableam_handler | heap table access method > | 60 MB > heap2 | Table | heap_tableam_handler | > | 120 kB > spgist | Index | spghandler | SP-GiST index access method > | 5840 kB > (8 rows) > > regression=# \dn++ > List of schemas > Name | Owner | Access privileges | Description > | Size > > --------------------+---------+--------------------+------------------------+--------- > fkpart3 | pryzbyj | | > | 168 kB > fkpart4 | pryzbyj | | > | 104 kB > fkpart5 | pryzbyj | | > | 40 kB > fkpart6 | pryzbyj | | > | 48 kB > mvtest_mvschema | pryzbyj | | > | 16 kB > public | pryzbyj | pryzbyj=UC/pryzbyj+| standard public > schema | 69 MB > | | =UC/pryzbyj | > | > regress_indexing | pryzbyj | | > | 48 kB > regress_rls_schema | pryzbyj | | > | 0 bytes > regress_schema_2 | pryzbyj | | > | 0 bytes > testxmlschema | pryzbyj | | > | 24 kB > (10 rows) > > I tested this patch. It looks well. The performance is good enough. I got the result for a schema with 100K tables in 3 seconds. I am not sure if using \dt+ and \dP+ without change is a good idea. I can imagine \dt+ and \dt++. \dP can exist just only in ++ form or we can ignore it (like now, and support \dP+ and \dP++) with same result I can live with the proposed patch, and I understand why ++ was introduced. But I am still not sure it is really user friendly. I prefer to extend \dA and \dn with some columns (\dA has only two columns and \dn has two columns too), and then we don't need special ++ variants for sizes. Using three levels of detail looks not too practical (more when the basic reports \dA and \dn) are really very simple). Regards Pavel -- > Justin >
