QgsPostgresConn::retrieveLayerTypes scans full table by default

[pramsey]

Feature description

QGIS/src/providers/postgres/qgspostgresconn.cpp

Line 2152 in e7c0455

const QString tableScanLimit { useEstimatedMetadata ? QStringLiteral( " LIMIT %1" ).arg( GEOM_TYPE_SELECT_LIMIT ) : QString() };

A full scan of the tables is the default action when connecting to a PostGIS database, and when a database has some very large tables, or FDW tables (slow to query sometimes) that can result in a hung QGIS. Probably "full scan" is the wrong default, and maybe never the right answer.

• At a first go, just having "Use estimated table metadata" checked instead of unchecked would be an improvement.
• Instead of taking "first 100 rows", using the PostgreSQL TABLESAMPLE facility can get a more representative sample at a lower cost, which might make having "default is sample" more palatable in terms of outputs.

Additional context

No response

[bertday]

I came to report the same behavior and stumbled on this issue. When I connect to a Postgres DB containing tables with lots of rows, it takes quite a while to list the tables in the QGIS browser.

I took a look in pg_stat_activity and saw QGIS is making a query like this for every table in the database:

SELECT 0, array_agg(DISTINCT st_srid("geom"::geometry)::text || ':' || UPPER(geometrytype("geom"::geometry))  || ':' || ST_Zmflag("geom"::geometry))  FROM (SELECT "geom" FROM "public"."a_big_table_with_lots_of_rows") AS _unused;

I believe that's the same full scan @pramsey described above, but the underlying SQL. I can see how it makes sense for a complete understanding of the data, but would agree with Paul that the overhead may not be justified in most cases.