0

I'm doing using an SQL query to simplify the geometries and transform them into GeoJSON, and later use it in Leaflet. However, I am facing a small problem: queries that return the geometries of cities, states and countries take about 1.5 seconds to complete, but when I consult the regions, it takes about 13 seconds. The query is the same for both, it just looks for different classifications. Viewing data in QGIS the information has the same configuration. What could be causing the delay? What should I consider?

This is the query:

SELECT 
    me.nome AS localizacao,
    public.ST_AsGeoJSON(public.ST_Transform((public.ST_Simplify(me.geom, 0.1)),4326),4) AS geojson 
FROM public.rebanho re 
    INNER JOIN indicador i ON re.indicador = i.id 
    INNER JOIN localizacao l ON re.localizacao = l.id 
    INNER JOIN regiao me ON l.id_regiao = me.id 
WHERE re.indicador = 43 
    AND re.data_coleta = '2019-01-01' 
GROUP BY me.nome, me.geom;

The output of explain:

"Group  (cost=2887342.93..2888138.86 rows=5 width=1101598) (actual time=7462.087..12532.189 rows=5 loops=1)"
"  Group Key: me.nome, me.geom"
"  ->  Nested Loop  (cost=2887342.93..2887975.63 rows=4897 width=1101566) (actual time=7461.305..9111.247 rows=5063 loops=1)"
"        ->  Gather Merge  (cost=2887342.93..2887913.26 rows=4897 width=1101570) (actual time=7461.266..9101.610 rows=5063 loops=1)"
"              Workers Planned: 2"
"              Workers Launched: 2"
"              ->  Sort  (cost=2886342.90..2886348.00 rows=2040 width=1101570) (actual time=2772.503..2772.614 rows=1688 loops=3)"
"                    Sort Key: me.nome, me.geom"
"                    Sort Method: quicksort  Memory: 25kB"
"                    Worker 0:  Sort Method: quicksort  Memory: 588kB"
"                    Worker 1:  Sort Method: quicksort  Memory: 25kB"
"                    ->  Nested Loop  (cost=352.60..5818.26 rows=2040 width=1101570) (actual time=392.026..402.816 rows=1688 loops=3)"
"                          ->  Hash Join  (cost=352.47..5512.27 rows=2040 width=8) (actual time=391.988..399.673 rows=1688 loops=3)"
"                                Hash Cond: (re.localizacao = l.id)"
"                                ->  Parallel Seq Scan on rebanho re  (cost=0.00..5154.45 rows=2040 width=8) (actual time=389.932..396.840 rows=1688 loops=3)"
"                                      Filter: ((indicador = 43) AND (data_coleta = '2019-01-01'::date))"
"                                      Rows Removed by Filter: 66710"
"                                ->  Hash  (cost=270.54..270.54 rows=6554 width=8) (actual time=2.953..2.954 rows=6554 loops=2)"
"                                      Buckets: 8192  Batches: 1  Memory Usage: 320kB"
"                                      ->  Seq Scan on localizacao l  (cost=0.00..270.54 rows=6554 width=8) (actual time=0.027..1.596 rows=6554 loops=2)"
"                          ->  Index Scan using regiao_pkey on regiao me  (cost=0.13..0.15 rows=1 width=1101574) (actual time=0.001..0.001 rows=1 loops=5063)"
"                                Index Cond: (id = l.id_regiao)"
"        ->  Materialize  (cost=0.00..1.15 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=5063)"
"              ->  Seq Scan on indicador i  (cost=0.00..1.15 rows=1 width=4) (actual time=0.023..0.024 rows=1 loops=1)"
"                    Filter: (id = 43)"
"                    Rows Removed by Filter: 11"
"Planning Time: 3.658 ms"
"JIT:"
"  Functions: 64"
"  Options: Inlining true, Optimization true, Expressions true, Deforming true"
"  Timing: Generation 6.859 ms, Inlining 187.602 ms, Optimization 633.779 ms, Emission 346.089 ms, Total 1174.330 ms"
"Execution Time: 12565.997 ms"

The output of explain using the other tables (is modified only the instruction: = INNER JOIN regiao me ON l.id_regiao = me.id):

"Group  (cost=609051.06..610480.38 rows=27 width=333623) (actual time=1786.670..3460.739 rows=25 loops=1)"
"  Group Key: me.nome, me.geom"
"  ->  Nested Loop  (cost=609051.06..609705.79 rows=5068 width=333591) (actual time=1784.808..2379.411 rows=5063 loops=1)"
"        ->  Gather Merge  (cost=609051.06..609641.31 rows=5068 width=333595) (actual time=1784.781..2372.924 rows=5063 loops=1)"
"              Workers Planned: 2"
"              Workers Launched: 2"
"              ->  Sort  (cost=608051.03..608056.31 rows=2112 width=333595) (actual time=1271.757..1271.894 rows=1688 loops=3)"
"                    Sort Key: me.nome, me.geom"
"                    Sort Method: quicksort  Memory: 25kB"
"                    Worker 0:  Sort Method: quicksort  Memory: 323kB"
"                    Worker 1:  Sort Method: quicksort  Memory: 266kB"
"                    ->  Nested Loop  (cost=352.53..5843.41 rows=2112 width=333595) (actual time=523.300..533.983 rows=1688 loops=3)"
"                          ->  Hash Join  (cost=352.40..5512.22 rows=2112 width=8) (actual time=523.281..531.005 rows=1688 loops=3)"
"                                Hash Cond: (re.localizacao = l.id)"
"                                ->  Parallel Seq Scan on rebanho re  (cost=0.00..5154.28 rows=2112 width=8) (actual time=521.347..528.382 rows=1688 loops=3)"
"                                      Filter: ((indicador = 43) AND (data_coleta = '2019-01-01'::date))"
"                                      Rows Removed by Filter: 66710"
"                                ->  Hash  (cost=270.51..270.51 rows=6551 width=8) (actual time=2.765..2.766 rows=6554 loops=2)"
"                                      Buckets: 8192  Batches: 1  Memory Usage: 319kB"
"                                      ->  Seq Scan on localizacao l  (cost=0.00..270.51 rows=6551 width=8) (actual time=0.029..1.440 rows=6554 loops=2)"
"                          ->  Index Scan using estado_pkey on estado me  (cost=0.14..0.16 rows=1 width=333595) (actual time=0.001..0.001 rows=1 loops=5063)"
"                                Index Cond: (id = l.id_estado)"
"        ->  Materialize  (cost=0.00..1.13 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=5063)"
"              ->  Seq Scan on indicador i  (cost=0.00..1.12 rows=1 width=4) (actual time=0.022..0.024 rows=1 loops=1)"
"                    Filter: (id = 43)"
"                    Rows Removed by Filter: 11"
"Planning Time: 0.427 ms"
"JIT:"
"  Functions: 61"
"  Options: Inlining true, Optimization true, Expressions true, Deforming true"
"  Timing: Generation 7.978 ms, Inlining 254.772 ms, Optimization 861.528 ms, Emission 443.524 ms, Total 1567.802 ms"
"Execution Time: 3463.410 ms"
1
  • Welcome to Geographic Information Systems! Welcome to GIS SE! We're a little different from other sites; this isn't a discussion forum but a Q&A site. Your questions should as much as possible describe not just what you want to do, but precisely what you have tried and where you are stuck trying that. Please check out our short tour for more about how the site works
    – Ian Turton
    Nov 21, 2021 at 11:14

1 Answer 1

0

I managed to solve by adding the id of the territorial divisions in GROUP BY. All my queries have reduced in time (to milliseconds).

Code with resolution:

   SELECT 
        me.nome AS localizacao,
        public.ST_AsGeoJSON(public.ST_Transform((public.ST_Simplify(me.geom, 0.1)),4326),4) AS geojson 
    FROM public.rebanho re 
        INNER JOIN indicador i ON re.indicador = i.id 
        INNER JOIN localizacao l ON re.localizacao = l.id 
        INNER JOIN regiao me ON l.id_regiao = me.id 
    WHERE re.indicador = 43 
        AND re.data_coleta = '2019-01-01' 
    GROUP BY me.id, me.nome, me.geom;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.