Not Useful for Large Tables

[pkkid]

Not Useful for Large Tables

[VladimirPal]

@mjs7231 Honestly we didn't test it on large tables. We'll do it.

[emrul]

You can try:
SELECT reltuples AS approximate_row_count FROM pg_class WHERE relname = 'tbl';
from https://wiki.postgresql.org/wiki/Count_estimate

This is much faster than SELECT COUNT(*) on large tables and on tables that have a lot of dead rows.

[VladimirPal]

https://github.com/web-pal/DBGlass/blob/master/app/actions/tables.js#L62
Here we getting list of tables, using 3 queries. We have to try to get all needed information using one query.

SELECT rel.relname, rel.relkind, rel.reltuples, coalesce(rel.relpages,0)
+ coalesce(toast.relpages,0) AS num_total_pages, SUM(ind.relpages) AS index_pages,
pg_roles.rolname AS owner FROM pg_class rel left join pg_class toast
on (toast.oid = rel.reltoastrelid) left join pg_index on (indrelid=rel.oid)
left join pg_class ind on (ind.oid = indexrelid) join pg_namespace
on (rel.relnamespace =pg_namespace.oid ) left join pg_roles
on ( rel.relowner = pg_roles.oid ) WHERE rel.relkind IN ('r','v','m','f')
AND nspname = 'public'GROUP BY rel.relname, rel.relkind, rel.reltuples,
coalesce(rel.relpages,0) + coalesce(toast.relpages,0), pg_roles.rolname

Some kind of this.
Using this query we also get very quickly approx count of rows on each table and if we have extremely large table then do not use COUT query.

[VladimirPal]

@mjs7231 Can you give us more details, how many rows you had on stucked table? Can you provide us schema of this table? I tested dbglass on 10millions rows and it worked pretty fast.
Want to fix issue with large tables.

[VladimirPal]

#46