<html><head><meta http-equiv="Content-Type" content="text/html charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class=""><h1 class=""></h1>
<b class="">Chris Nicholas</b>
<a href="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" title="[Qgis-developer] how to avoid full-table scans to determine primary key, extents?" class="">cnicholas at housecanary.com
</a><br class="">
<i class="">Tue Jan 31 15:24:11 PST 2017</i><div class=""><br class="webkit-block-placeholder"></div><ul class="">
<li class="">Previous message (by thread): <a href="https://lists.osgeo.org/pipermail/qgis-developer/2017-January/046855.html" class="">[Qgis-developer] Building QGIS2.99 with CMAKE using Qt 5.8 and g++ 6.3 Ubuntu 16.04 64-bit
</a></li>
<li class=""> <b class="">Messages sorted by:</b>
<a href="https://lists.osgeo.org/pipermail/qgis-developer/2017-January/date.html#46853" class="">[ date ]</a>
<a href="https://lists.osgeo.org/pipermail/qgis-developer/2017-January/thread.html#46853" class="">[ thread ]</a>
<a href="https://lists.osgeo.org/pipermail/qgis-developer/2017-January/subject.html#46853" class="">[ subject ]</a>
<a href="https://lists.osgeo.org/pipermail/qgis-developer/2017-January/author.html#46853" class="">[ author ]</a>
</li>
</ul>
<hr class="">
<pre class="">even AFTER (!) I’ve loaded a project, I’m hitting these darned full table scans with this darned query:</pre><pre class="">"SELECT st_extent("the_geom") FROM "geo_search"."block_view" WHERE ("property_type" = 'SFD')"</pre><pre class="">when the connect string in the project file clearly states:</pre><pre class=""><div style="margin: 0px; font-size: 11px; line-height: normal; font-family: Menlo;" class=""><span style="font-variant-ligatures: no-common-ligatures" class=""> <datasource>dbname='geo' host=<a href="http://blah.blah.amazonaws.com" class="">blah.blah.amazonaws.com</a> 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></span></div></pre><pre class="">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. </pre><pre class="">looking for a contractor to do some brain surgery to:</pre><pre class=""><span class="pl-en">QgsPostgresProvider::uniqueData() and </span>QgsRectangle <span class="pl-en">QgsPostgresProvider::extent</span>()</pre><pre class=""><span class="pl-en">in: <a href="https://github.com/qgis/QGIS/blob/master/src/providers/postgres/qgspostgresprovider.cpp" class="">https://github.com/qgis/QGIS/blob/master/src/providers/postgres/qgspostgresprovider.cpp</a></span></pre><pre class=""><br class=""></pre><pre class="">thanks!</pre><pre class="">Chris</pre><pre class=""><br class=""></pre><pre class="">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
><i class=""> this is my observation on 2.14.8:
</i>><i class="">
</i>><i class=""> if the postgis layer is added (via the "add postgis layer" dialog)
</i>><i class=""> with the "select at id" checkbox then when opening the attribute table
</i>><i class=""> this is iterated one time. If the table is large browsing it is not
</i>><i class=""> very snappy.
</i>><i class="">
</i>><i class=""> If the layer is added without the "select at id" thing, then the table
</i>><i class=""> is always iterated twice before opening. If the "show features visible
</i>><i class=""> on map" option is selected, the table is still iterated twice (the
</i>><i class=""> entire table, not only the records of visible features) before
</i>><i class=""> opening. Browsing of large tables (once opened) is fast.
</i>><i class="">
</i>><i class=""> One ticket I found that may be related
</i>><i class="">
</i>><i class=""> <a href="http://hub.qgis.org/issues/10619" class="">http://hub.qgis.org/issues/10619</a>
</i>><i class="">
</i>><i class="">
</i>><i class=""> cheers
</i>><i class="">
</i>><i class=""> -- G --
</i>><i class=""> _______________________________________________
</i>><i class=""> Qgis-developer mailing list
</i>><i class=""> <a href="https://lists.osgeo.org/mailman/listinfo/qgis-developer" class="">Qgis-developer at lists.osgeo.org</a>
</i>><i class=""> List info: <a href="http://lists.osgeo.org/mailman/listinfo/qgis-developer" class="">http://lists.osgeo.org/mailman/listinfo/qgis-developer</a>
</i>><i class=""> Unsubscribe: <a href="http://lists.osgeo.org/mailman/listinfo/qgis-developer" class="">http://lists.osgeo.org/mailman/listinfo/qgis-developer</a>
</i>
-------------- 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: <<a href="http://lists.osgeo.org/pipermail/qgis-developer/attachments/20170131/76b6e214/attachment.sig" class="">http://lists.osgeo.org/pipermail/qgis-developer/attachments/20170131/76b6e214/attachment.sig</a>>
</pre>
<hr class=""><div class=""><br class="webkit-block-placeholder"></div><ul class="">
<li class=""></li></ul><div class=""><br class=""></div></body></html>