Not Useful for Large Tables

[pkkid]

Loading the table data by default when clicking on one of my larger tables (700MB) caused DBGlass to be in the loading state for about 20 minutes. It was stuck running the query SELECT COUNT(*) FROM <tablename>. There should be an option to not show the total number of rows or first page of results default.. or an option to cancel the request during long loading transactions.

[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]

https://github.com/web-pal/DBGlass/issues/46