1

Not sure if this is specific of GIS stuff or simply a SQL performance issue, but due to the nature of the issue and that I am working with H3 data types here it goes

I have a query that runs very slowly (about 40 seconds) it looks like this:

        SELECT
            h3data.h3index,
            sum(h3data.value)
        FROM locations l
        INNER JOIN records r ON r."locationsId" = l.id 
        INNER JOIN values v ON v."recordId" = sr.id,
        LATERAL (
            SELECT
                h3index,
                sum(v.value/nullif(v.scaler, 0)) * value as value
            FROM some_stored_procedure_that_returns_table(l."geomId", v."h3Id")
        ) h3data
        WHERE r.year=2015
        AND l."someCondition" IS NULL
        AND v."referenceId" = '633cf928-7c4f-41a3-99c5-e8c1bda0b323'
        GROUP by h3data.h3index

EXPLAIN ANALYZE shows that:

    HashAggregate  (cost=84339.65..84342.15 rows=200 width=24) (actual time=236588.655..248466.136 rows=1038113 loops=1)
  Group Key: some_stored_procedure_that_returns_table.h3index
  Batches: 69  Memory Usage: 4265kB  Disk Usage: 329856kB
  ->  Nested Loop  (cost=850.26..53564.65 rows=2462000 width=32) (actual time=182.565..177669.958 rows=6596332 loops=1)
        ->  Hash Join  (cost=850.01..4324.40 rows=2462 width=48) (actual time=153.539..835.516 rows=2516 loops=1)
              Hash Cond: (r."location" = l.id)
              ->  Hash Join  (cost=692.40..4160.31 rows=2462 width=48) (actual time=64.310..704.985 rows=2516 loops=1)
                    Hash Cond: (v."recordId" = r.id)
                    ->  Seq Scan on values v  (cost=0.00..3396.80 rows=27086 width=48) (actual time=0.035..401.328 rows=27676 loops=1)
                          Filter: ("referenceId" = '633cf928-7c4f-41a3-99c5-e8c1bda0b323'::uuid)
                .....MORE

So this is taking a lot of time, howeve, if I just remove the aggregation and the grouping by, like:

        SELECT
            h3data.h3index,
            h3data.value
        FROM locations l
        INNER JOIN records r ON r."locationsId" = l.id 
        INNER JOIN values v ON v."recordId" = sr.id,
        LATERAL (
            SELECT
                h3index,
                sum(v.value/nullif(v.scaler, 0)) * value as value
            FROM some_stored_procedure_that_returns_table(l."geomId", v."h3Id")
        ) h3data
        WHERE r.year=2015
        AND l."someCondition" IS NULL
        AND v."referenceId" = '633cf928-7c4f-41a3-99c5-e8c1bda0b323'
       

This is the result:

Nested Loop  (cost=850.26..78184.65 rows=2462000 width=16) (actual time=100.335..173085.479 rows=6596332 loops=1)
  ->  Hash Join  (cost=850.01..4324.40 rows=2462 width=48) (actual time=96.266..630.199 rows=2516 loops=1)
        Hash Cond: (r."locationId" = l.id)
        ->  Hash Join  (cost=692.40..4160.31 rows=2462 width=48) (actual time=55.604..548.316 rows=2516 loops=1)
              Hash Cond: (v."recordId" = r.id)
              ->  Seq Scan on values v  (cost=0.00..3396.80 rows=27086 width=48) (actual time=0.043..255.372 rows=27676 loops=1)
                    Filter: ("referenceId" = '633cf928-7c4f-41a3-99c5-e8c1bda0b323'::uuid)
              .....MORE

It decreases by a lot and it executes fast enough. This is the kind of data I am trying to aggregate:

> h3Index           values
> 862d84c27ffffff   6706189360729522000000000000000000000000000
> 862db112fffffff   24690280185829940000000000000000000000000000
> 862da2757ffffff   6363074936795764000000000000000000000000000
> 862db1c77ffffff   20955525424756833000000000000000000000000000
> 862db1ad7ffffff   2384501631174928000000000000000000000000000
> 862d84c1fffffff   7026257930089419000000000000000000000000000
> 862da249fffffff   1166966013803679400000000000000000000000000
> 862da274fffffff   9853446181273213000000000000000000000000000
> 862db1c6fffffff   15668891331171954000000000000000000000000000

These h3Index that can come from different tables are always indexed, and the amount of rows that I want to sum up and the group by h3Index is a bit more than 26 million

Can this amount make the performance decrease so much just for a aggregaton? I know that this is an expensive operation computational wise, but can be this significant? From 1 second to 40 approx.

I think that the main issue is there and not in the inners of some stored procedures that are in action within this query, and I think I'm hitting some basics here but can't figure it out

Any suggestions on what I can do or where should I look at?

I am running PostGIS/PostGIS:13-3.1 via Docker / Kubernetes

3
  • 1
    I think the main problem is your grouping variable that comes from a procedure in a lateral join, it may have difficulties to plan correctly with this kind of things. If it was me, I would try to do a MATERIALIZED CTE (see postgresql.org/docs/current/queries-with.html#id-1.5.6.12.7) with the second query, and do the grouping after, to see if this improve the performance. You can also put each step in its CTE, and try to materialize each or several to see if there is improvements, sometimes you can have surprising speed boost like that. Commented May 5, 2022 at 16:45
  • @robinloche Thanks for the answer! I agree where the main problem is. But in order to run the procedure, I need to pass some filter that are defined in the outside query, that's why the lateral join. Not sure how can I create a CTE with the subquery without previously joining the rest of the tables in outter query. I've tried to create a MCTW with the query with no grouping and with all the fields to later apply the filter and the aggregation and grouping, but I have see no improvement Commented May 6, 2022 at 6:36
  • Aggregating big data is always a hassle. Please take a look at this dba.stackexchange.com/a/197552/110861 It might help.
    – DavidP
    Commented May 13, 2022 at 8:16

0

Your Answer

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

Browse other questions tagged or ask your own question.