[postgis-users] Inefficient query with QGIS?

[Bruno Friedmann]

On 01/30/2011 09:43 PM, Aren Cambre wrote:
> Discussing with a developer, it turns out there's an option named *Use
> estimated table metadata* in the PostGIS connection properties. Using that
> solved it for me as it adds *LIMIT 100* to the query.
> 
> So the fix is there, it's just kind of buried.
> 
> And, again, this is for a view, and it appears there's no way to know what a
> kind of geometry is in a view's geometry column without inspecting it.
> 
> Aren

Aren, on recent postgis, >1.5x if you issue the select populate_geometry_columns() it take care of views too.

> 
> On Sun, Jan 30, 2011 at 2:03 PM, Ivan Mincik <ivan.mincik at gmail.com> wrote:
> 
>> On Sun, Jan 30, 2011 at 4:59 PM, Aren Cambre <aren at arencambre.com> wrote:
>>> I noticed this really inefficient query when QGIS was importing a Postgis
>>> layer:
>>> select distinct case when geometrytype("the_geom") IN
>> ('POINT','MULTIPOINT')
>>> THEN 'POINT' when geometrytype("the_geom") IN
>>> ('LINESTRING','MULTILINESTRING') THEN 'LINESTRING' when
>>> geometrytype("the_geom") IN ('POLYGON','MULTIPOLYGON') THEN 'POLYGON' end
>>> from [databaseGoesHere]
>>> That surprised me because QGIS should already know the geometrytype
>> through
>>> the geomerty_columns table.
>>> Is this really QGIS's fault, or did Postgis cause/require this? Want to
>> make
>>> sure before I file a bug report.
>>
>> Hi, which QGIS version do You use ?
>> Has all You tables and views valid records in 'geometry_columns' ? If
>> not, tables/views which are not present in 'geometry_colums' are
>> queried by query You are talking about. If just one of them is big, or
>> time expensive view, each database connect will take very long time.
>>
>> In current trunk (and I do not remember how many versions back), there
>> is option 'Only look in the geometry_columns tables' which can help
>> You.
>>
>> Ivan
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
> 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users


-- 

Bruno Friedmann
Ioda-Net Sàrl www.ioda-net.ch

openSUSE Member & Ambassador
GPG KEY : D5C9B751C4653227
irc: tigerfoot