[Qgis-developer] Why is scrolling in the attribute table so slow?
Andreas Neumann
a.neumann at carto.net
Thu Nov 24 09:42:43 EST 2011
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
More information about the Qgis-developer
mailing list