Qgis is slow with complex 'postgis query' layer. How to speed up?

Question

I have a postgis layer in Qgis that is a result of a complex query. Every screen update, Qgis runs the Query again and that's slow. Any suggestion on how to speed up?

EDIT - Here is the query:

 SELECT 
 parcela.id AS id, 
 st_union(areas.the_geom) AS geom,
  (((((('p'::text || round(avg(to_number(dado.valor -> 'pequena'::text, '99'::text)) * 10::numeric, 2)) || ',m'::text) || round(avg(to_number(dado.valor -> 'media'::text, '99'::text)) * 10::numeric, 2)) || ',g'::text) || round(avg(to_number(dado.valor -> 'grande'::text, '99'::text)) * 10::numeric, 2)) || ',mo'::text) || round(avg(to_number(dado.valor -> 'morta'::text, '99'::text)) * 10::numeric, 2) AS "legenda", 
  round(avg(to_number(dado.valor -> 'pequena'::text, '99'::text)) * 10::numeric, 2) AS pequenas, 
  round(avg(to_number(dado.valor -> 'media'::text, '99'::text)) * 10::numeric, 2) AS medias, 
  round(avg(to_number(dado.valor -> 'grande'::text, '99'::text)) * 10::numeric, 2) AS grandes, 
  round(avg(to_number(dado.valor -> 'morta'::text, '99'::text)) / 10::numeric, 2) AS mortas

  FROM petr.dado, petr.parcela, petr.parcela_rel, petr.areas
  WHERE dado.parcela_id = parcela.id AND parcela_rel.area_id = areas.gid AND parcela_rel.parcela_id = parcela.id
  GROUP BY parcela.id  

Answer 1

Hallo

Is it the QGIS rendering that makes things slow or the PostGIS query. Is the query in the definition of a view or why does it need to med evaluated more than once?

If it is the PostGIS query that is the problem, why can't you just make a table out of the query to make it static and add a spatial index to it?

If you need it to be a dynamic query you need to share more information about the query if it might be optimized in some way.

Regards Nicklas

Answer 2

Creating indices on the columns in your WHERE condition will speed things up considerably, if you haven't already done that.