[Qgis-developer] Why is scrolling in the attribute table so slow?

[Andreas Neumann]

 yes - good idea. "Materializing" this particular view would make a lot 
 of sense I think. The underlying tables are probably updated once per 
 week.

 I see that "materialized views" are top on the uservoice wishlist for 
 PostgreSQL ...

 Thanks!
 Andreas

 On Thu, 24 Nov 2011 08:59:39 -0500,  wrote:
>> >> Maybe my view definition is slowing things down that much. I have 
>> to
>> >> see if
>> >> I can improve it.
>> >
>> > It would be good to use 'EXPLAIN' to see what is going on in the
>> > database - then probably adding some indices on columns of joined
>> > tables will speed things up significantly.
>>
>>  yes - it is quite a complex view. Around 20 tables are involved and 
>> for
>>  the joins I am also using concatenations and regexp functions.
>>
>>  Although the view involves a lot of tables with quite a bit of 
>> data,
>>  the result is actually really small (350 records) so I wonder, if 
>> for
>>  situations like this, if there couldn't be a user-defined option 
>> telling
>
>>  QGIS to load all data at once and cache it.
>>
>
> If your view is complex to compute but evaluates to a small extent, 
> you
> should consider materializing it on the server side.  Materialization 
> makes
> a lot of sense for a view that is read much more often than the 
> underlying
> data is updated.  Materialization on the server side rather than in 
> an
> application-layer cache allows the cache to be shared both by 
> heterogenous
> applications and by concurrent user connections.
>
> Unfortunately, Posgres does not have built-in support for 
> materialized
> views.  However, it is possible to roll your own and keep it up to 
> date
> either eagerly (incremental maintenance via synchronous triggers) or 
> lazily
> (periodic recomputation).  See the two excellent tutorials linked 
> from this
> page: http://wiki.postgresql.org/wiki/Materialized_Views
>
>
> ----
> David E. DeHaan
> SQL Anywhere Query Processing team
> Sybase, an SAP Company

-- 
 --
 Andreas Neumann
 Böschacherstrasse 10A
 8624 Grüt (Gossau ZH)
 Switzerland