[Qgis-developer] how to avoid full-table scans to determine primary key, extents?

[Chris Nicholas]

Chris Nicholas cnicholas at housecanary.com  <mailto:qgis-developer%40lists.osgeo.org?Subject=Re%3A%20%5BQgis-developer%5D%20how%20to%20avoid%20full-table%20scans%20to%20determine%0A%09primary%20key%2C%20extents%3F&In-Reply-To=%3C39D753F2-C8D1-45C1-9556-C1E72C5AB99B%40housecanary.com%3E>
Tue Jan 31 15:24:11 PST 2017

Previous message (by thread): [Qgis-developer] Building QGIS2.99 with CMAKE using Qt 5.8 and g++ 6.3 Ubuntu 16.04 64-bit <https://lists.osgeo.org/pipermail/qgis-developer/2017-January/046855.html>
Messages sorted by: [ date ] <https://lists.osgeo.org/pipermail/qgis-developer/2017-January/date.html#46853> [ thread ] <https://lists.osgeo.org/pipermail/qgis-developer/2017-January/thread.html#46853> [ subject ] <https://lists.osgeo.org/pipermail/qgis-developer/2017-January/subject.html#46853> [ author ] <https://lists.osgeo.org/pipermail/qgis-developer/2017-January/author.html#46853>
even AFTER (!) I’ve loaded a project, I’m hitting these darned full table scans with this darned query:
"SELECT st_extent("the_geom") FROM "geo_search"."block_view" WHERE ("property_type" = 'SFD')"
when the connect string in the project file clearly states:
 <datasource>dbname='geo' host=blah.blah.amazonaws.com port=5432 user=‘joeschmo' password=‘like_im_going_to_tell_you?' sslmode=disable key='id' estimatedmetadata=true srid=4269 type=MultiPolygon table="geo_search"."block_view" (the_geom) sql= </datasource>
maybe I’m doing something wrong; I hope so. If not, folks: Users need to connect to well-authored QGIS project, files backed by a large number (millions, or hundreds of millions) of rows in SECONDS, not tens (or even hundreds) of MINUTES, and not step on unknown/unnecessary "land-mines" just turning layers on and off that make the app useless, if QGIS is to be relevant for this increasingly common class of problem.
looking for a contractor to do some brain surgery to:
QgsPostgresProvider::uniqueData() and QgsRectangle QgsPostgresProvider::extent()
in: https://github.com/qgis/QGIS/blob/master/src/providers/postgres/qgspostgresprovider.cpp <https://github.com/qgis/QGIS/blob/master/src/providers/postgres/qgspostgresprovider.cpp>

thanks!
Chris

revisiting this problem; it is really making QGIS totally unusable for the large tables we are manipulating.

UNECESSARY FULL TABLE SCANS == VERY BAD USER EXPERIENCE!!

If funds could be made available for this fix, is there anyone able/available to add this functionality, and avoid a full table scan on adding a layer, and on existing project start-up?

(i.e. add a new pop-up dialog when one adds a new vector layer to optionally/manually enter the layer extents)

this would make QGIS *-far-* more usable for “big data”

thanks!
Chris


> this is my observation on 2.14.8:
>
> if the postgis layer is added (via the "add postgis layer" dialog)
> with the "select at id" checkbox then when opening the attribute table
> this is iterated one time. If the table is large browsing it is not
> very snappy.
>
> If the layer is added without the "select at id" thing, then the table
> is always iterated twice before opening. If the "show features visible
> on map" option is selected, the table is still iterated  twice (the
> entire table, not only the records of visible features) before
> opening. Browsing of large tables (once opened) is fast.
>
> One ticket I found that may be related
>
> http://hub.qgis.org/issues/10619 <http://hub.qgis.org/issues/10619>
>
>
> cheers
>
> -- G --
> _______________________________________________
> Qgis-developer mailing list
> Qgis-developer at lists.osgeo.org <https://lists.osgeo.org/mailman/listinfo/qgis-developer>
> List info: http://lists.osgeo.org/mailman/listinfo/qgis-developer <http://lists.osgeo.org/mailman/listinfo/qgis-developer>
> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-developer <http://lists.osgeo.org/mailman/listinfo/qgis-developer>

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 842 bytes
Desc: Message signed with OpenPGP using GPGMail
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20170131/76b6e214/attachment.sig <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20170131/76b6e214/attachment.sig>>


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20170201/bed2dfb1/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 842 bytes
Desc: Message signed with OpenPGP using GPGMail
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20170201/bed2dfb1/attachment.sig>