Allow for a less expensive pgmacs--list-tables function

[akurth]

Hello Eric,

some of my production DBs are huge – I mean: HUGE. Running pgmacs-open fails of course, due to timeout (count(*)), and also puts way too much stress on the DB. It would be great to have an overview of the tables anyway, even without accurate data like number of rows. One option could be to allow passing a function into pgmacs--list-tables, so users would be free to show whatever they need and what their DB is capable of.

What do you think?

[emarsden]

This is a good point. I initially tried using a less expensive query

SELECT n_live_tup FROM pg_stat_user_tables

but this doesn't work for recently created tables that have not had a VACUUM. It would be better to fill in this row-count column lazily, once the table list has been displayed. Also, we should look for a heuristic that is both reasonably fast and reasonably accurate to obtain the information.

[akurth]

Even asynchronous lazy fetching wouldn’t help in many cases, since most users probably never want to place table scans on their production DBs. I’m sure you have read Erwin Brandstetter’s comprehensive explanations (e.g. https://stackoverflow.com/questions/7943233/fast-way-to-discover-the-row-count-of-a-table-in-postgresql); unfortunately there doesn’t seem to be a reliable way of getting the information fast and accurate.

[emarsden]

This is a useful perspective, thanks (I don't use large databases myself).

I will investigate whether there is a way of estimating the "productionness" of a database (perhaps based on total size or some activity statistic), to switch automatically between a cheap-but-inaccurate and expensive-but-accurate mode, which could also be overridden as a user preference. I think that there is value in keeping the simple-minded-but-accurate approach for people who are only playing around with little databases and would be confused by inaccurate information.

[emarsden]

I have now implemented something along these lines in HEAD. There is a user-customizable option pgmacs-large-database-threshold (expressed in octets) that determines whether PGmacs takes the cheap-but-inaccurate and expensive-but-accurate route to estimating row counts. This is currently set to 100MB which is perhaps too large a number; let me know. If set to zero all table scans for row count estimation should be prevented.

[ak-ionos]

Thanks, this might become a good solution.

Currently, there is another deadly table scan in the remaining SELECT in line 1660. Please remove the "FROM %s" clause in line 1663; that leads to a full sequential scan, too, and returns the same result for as many rows as there are in the table.

[akurth]

BTW, akurth and ak-ionos are the same guy. I sometimes forget to log out from my business account, sorry.