Bug report #21517

Estimated Extent fails when rows exceed 32-bit signed integer max, issues slow ST_Extent query

Added by Jesse Ogle over 1 year ago. Updated over 1 year ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Data Provider/PostGIS
Affected QGIS version:3.7(master) Regression?:No
Operating System:Windows, Linux Easy fix?:Yes
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:29334

Description

When 'Use estimated table meta data' is selected in the PostGIS Connection dialog, QGis will still use ST_Extent() instead of ST_EstimatedExtent() if the table has more than int max rows. The ST_Extent() query is prohibitively slow and causes QGis to hang.

This appears to be the result of casting reltuples to int in the SQL query in this line:
https://github.com/qgis/QGIS/blob/2403c64a7c26f1646fcba6142265c7c953540f28/src/providers/postgres/qgspostgresprovider.cpp#L3252

This results in an error which causes the extent to be cleared and then ST_Extent() is used. Changing ::int to ::bigint resolves this issue (verified via psql query not through QGis).

The following line will also fail when reltuples is greater than int max:
https://github.com/qgis/QGIS/blob/2403c64a7c26f1646fcba6142265c7c953540f28/src/providers/postgres/qgspostgresprovider.cpp#L3193

Data source: PostgreSQL 10.5, compiled by Visual C++ build 1800, 64-bit

Associated revisions

Revision feb0c8bd
Added by Nyall Dawson over 1 year ago

[postgres] Fix estimated metadata queries fail for huge tables

Fixes #21517

Revision d15d1242
Added by Nyall Dawson over 1 year ago

[postgres] Fix estimated metadata queries fail for huge tables

Fixes #21517

Revision c1089238
Added by Nyall Dawson over 1 year ago

[postgres] Fix estimated metadata queries fail for huge tables

Fixes #21517

Revision dcc9f11c
Added by Nyall Dawson over 1 year ago

[postgres] Fix estimated metadata queries fail for huge tables

Fixes #21517

Revision da2064d0
Added by Nyall Dawson over 1 year ago

[postgres] Fix estimated metadata queries fail for huge tables

Fixes #21517

Revision aba1e308
Added by Nyall Dawson over 1 year ago

[postgres] Fix estimated metadata queries fail for huge tables

Fixes #21517

History

#1 Updated by Nyall Dawson over 1 year ago

  • Status changed from Open to In Progress

#2 Updated by Nyall Dawson over 1 year ago

  • % Done changed from 0 to 100
  • Status changed from In Progress to Closed

Also available in: Atom PDF