Cannot see tables in Postgressql placed in Azure

Hi,

I'm new to HeidiSQL so maybe I am missing something, but I cannot see my tables anywhere. I'm connected to a PostgreSQL database located in Azure, and I can make queries. But "autocomplete" for tables shows an empty box, and also, I cannot see the tables anywhere in the GUI.

Shouldn't there be a ree view or something somewhere?

BR heidiNewbie

So you don't see such a tree on the left?

If I connect to a localhost PostgreSQL I can see all tables in the tree to the left. But "autocomplete" in query editor does not work on localhost either. It shows an empty box:

I don't see the tree for the Azure database (about 1300 tables) I get this:

SELECT *, pg_table_size(QUOTE_IDENT(t.TABLE_SCHEMA) || '.' || QUOTE_IDENT(t.TABLE_NAME))::bigint AS data_length, pg_relation_size(QUOTE_IDENT(t.TABLE_SCHEMA) || '.' || QUOTE_IDENT(t.TABLE_NAME))::bigint AS index_length, c.reltuples, obj_description(c.oid) AS comment FROM "information_schema"."tables" AS t LEFT JOIN "pg_namespace" n ON t.table_schema = n.nspname LEFT JOIN "pg_class" c ON n.oid = c.relnamespace AND c.relname=t.table_name WHERE t."table_schema"='public';
/* ERROR:  canceling statement due to statement timeout */

It seems like it is "pg_table_size(QUOTE_IDENT(t.TABLE_SCHEMA) || '.' || QUOTE_IDENT(t.TABLE_NAME))::bigint AS data_length" that takes all the time. Running this query:

SELECT *
  --, pg_table_size(QUOTE_IDENT(t.TABLE_SCHEMA) || '.' || QUOTE_IDENT(t.TABLE_NAME))::bigint AS data_length
  --, pg_relation_size(QUOTE_IDENT(t.TABLE_SCHEMA) || '.' || QUOTE_IDENT(t.TABLE_NAME))::bigint AS index_length
  , c.reltuples
  , obj_description(c.oid) AS comment 
FROM "information_schema"."tables" AS t 
  LEFT JOIN "pg_namespace" n ON t.table_schema = n.nspname 
  LEFT JOIN "pg_class" c ON n.oid = c.relnamespace AND c.relname=t.table_name 
WHERE t."table_schema"='public'
LIMIT 100;

cuts the execution time from approx 30 sec. to 50 msec! So pg_table_size(...) and pg_relation_size(...) is extremely expensive. Anyway to avoid including these functions in the query to get all table names for the tree view?

Setting the query timeout to 400 secs made the tree view appear. But waiting approx. 400 secs for HeidiSQL to start is a long time :-)

Yes, definitely :) Will try your query approach, whether it's not only working faster, but also does not optimize some feature away.

So, my just pushed change now adds support for the otherwise unused checkbox "Get full table status" for PostgreSQL. Find it in the session manager's "Advanced" tab:

The hint says it's for speeding up work with many InnoDB tables, which is of course MariaDB specific. On PostgreSQL, this enables/disables the use of the above mentioned two functions pg_table_size() and pg_relation_size().

Nice - thanx a lot for the quick response and fix

BR heidiNewbir