1

I have a few huge PostGIS tables, and despite setting 'use estimated metadata' when creating the underlying datasource, it STILL issues these incredibly frustrating 'queries of death' at both layer addition and loading saved projects causing full table scans:

SELECT count(distinct ("block_id"))=count(("block_id")) FROM "christest"."block_th_view"
SELECT st_extent("the_geom") FROM "christest"."block_th_view" 

Can't we just add a bounding box metadata tag on a layer definition, and trust that it is indeed using a unique primary key, and optionally have QGIS just blindly trust the project file on start-up?


Consider a well-indexed table with billions of GPS points with a free running counter as its unique key, that only allows display up to a zoom scale of 1:1000. Good GIST indexing and fast RAID disks make it very usable ... once given a chance... BUT..not one, but TWO (!!!) full table scans are required each time to load the layer, that yields the same obvious results: one billion unique keys and the entire world. It is an utter waste of both the user's and the database's precious resources to recalculate this, much as it would be to recalculate pi to a million places every time you wanted to use that function on your calculator.

feature request:

Add a new tag "boundingBox" or "extents" child XML node under a maplayer, within QGIS project files, that is blindly trusted when a global preference is set, rather than "figuring it out" on the fly by performing a full table scan with SELECT st_extent() during load, and optionally skip the unique key check. Furthermore, add an additional dialog box to optionally set this manually when the layer is first added, as is done in GRASS GIS.

This will make QGIS -far- more useable for very large database layers.

closed as unclear what you're asking by Matthias Kuhn, PolyGeo Nov 12 '16 at 9:14

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • What do you mean by 'queries of death'? They never finish, they crash the system, ...? – Simbamangu Nov 5 '16 at 4:55
  • this requires a full table scan just to start up! – Chris Nicholas Nov 7 '16 at 18:37
  • 1
    Equally frustrating/annoying is that it feels compelled to do "SELECT st_extent('the_geom") on VIEWs that join the geometry to attributes !! – Chris Nicholas Nov 7 '16 at 19:09
  • 1
    It's unclear what you need. Is this a feature request? Click edit and expand on your question if possible, explaining symptoms and expected outcomes. – Simbamangu Nov 8 '16 at 2:55
  • 2
    Hi Chris, this looks to me like a (valid) feature request that should be discussed in the QGIS issue tracker or the mailing list. (Hints: if/when writing there, 1. talk about ideas and proposals instead of solutions, there might be other approaches around and 2. hide your frustration) – Matthias Kuhn Nov 12 '16 at 8:08

Browse other questions tagged or ask your own question.