[Qgis-user] QGis checks uniqueness of primary key on startup

[Florian Lohoff]

Hi,

On Tue, May 20, 2014 at 01:46:51PM +0200, Sergio Gollino wrote:
> Ok 20 milion of records is the problem not the unique key...
> 
> Create a table (not a view) and use indices and geometry constraints. Check
> the "Use estimated table metadata" option in the connection as Jurgen said.
> Use a field of the table to filter it before you load on qgis.
> And load it when you are a scale like 10.000 or less.
> If this is not enough you have to make tiles of you your table.

I enabled the "use estimaed table metadata" - I have a scale depenend
rendering - THIS is NOT the problem - I start at a scale of 1:8000 
which should display probably 2-300 Objects. Still QGis does 2 full table
scans on the views to a) find out if primary keys are unique, and b)
to do an st_extent on the geometry. So the database walks through
100Gig of data twice.

> Remember that if you have a shp file whit 20 milion of geometries probably
> you will wait for all your life to display it, so, 5 or 10 minutes, its a
> really good result.

Are you kidding me? I am displaying 300 out of 20 Million entrys - selecting
those on the database takes approx. 300ms due to an appropriate gist index.
I have written a web application using openlayers and i can interactively
pan through the map - Loading elements at lower scales takes just a few ms.

2014-05-20 12:13:56 UTC LOG:  duration: 147171.357 ms  statement: SELECT count(distinct "id")=count("id") FROM "public"."v_kvztallength_pos"
2014-05-20 12:16:23 UTC LOG:  duration: 147145.301 ms  statement: SELECT st_extent("position") FROM "public"."v_kvztallength_pos"

But THIS is not fetching objects for display - this is initialization of the layer.

As you can see its 5 Minutes just for THIS single layer although on this startup there was
no element/object in the viewport on startup at scale 1:8000.

flo
-- 
Florian Lohoff                                                 f at zz.de
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 828 bytes
Desc: Digital signature
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20140520/f208bf1c/attachment.sig>