query in POSTGIS much slower then model in QGIS

Question

I have a materialized view in postgis with linear referenced roads and a table with boundaries.

I want to sum all lines per boundary based on 2 categories. For that: I add a column (tw) which defines the categorie (0 or 1).

I made following query:

select
g.naam,
case when t.tw = 1 then sum(st_length(t.geom)) end as traag,
case when t.tw = 0 then sum(st_length(t.geom)) end as snel

from gemeentegrenzen g

join (select geom, case when morf in (113, 114, 125) or (morf = 103 and type = 2) then 1 else 0 end as tw

from wegenregister.mv_wegsegment_wegverharding_lrs) t on st_intersects(st_buffer(g.geom, 0), t.geom)

group by g.naam, t.tw
;

This takes me a few hours.

In QGIS I made a model that does exact the same things based on the same layers. This process takes about 30-40 minutes.

What is wrong with the sql-query?

when running explain:

"GroupAggregate  (cost=243596190.39..245430994.97 rows=22176 width=283)"
"  Group Key: g.naam, (CASE WHEN ((mv_wegsegment_wegverharding_lrs.morf = ANY ('{113,114,125}'::integer[])) OR ((mv_wegsegment_wegverharding_lrs.morf = 103) AND (mv_wegsegment_wegverharding_lrs.type = 2::numeric))) THEN 1 ELSE 0 END)"
"  ->  Sort  (cost=243596190.39..243858174.65 rows=104793704 width=283)"
"        Sort Key: g.naam, (CASE WHEN ((mv_wegsegment_wegverharding_lrs.morf = ANY ('{113,114,125}'::integer[])) OR ((mv_wegsegment_wegverharding_lrs.morf = 103) AND (mv_wegsegment_wegverharding_lrs.type = 2::numeric))) THEN 1 ELSE 0 END)"
"        ->  Nested Loop  (cost=0.00..173759771.26 rows=104793704 width=283)"
"              Join Filter: st_intersects(st_buffer(g.geom, 0::double precision), mv_wegsegment_wegverharding_lrs.geom)"
"              ->  Seq Scan on gemeentegrenzen g  (cost=0.00..62.08 rows=308 width=20402)"
"              ->  Materialize  (cost=0.00..96126.77 rows=1020718 width=274)"
"                    ->  Seq Scan on mv_wegsegment_wegverharding_lrs  (cost=0.00..53144.18 rows=1020718 width=274)"

Answer 1

select
    g.naam,
    t.tw,
    sum(st_length(t.geom))
from gemeentegrenzen g
join (
    select 
        geom, 
        case when morf in (113, 114, 125) or (morf = 103 and type = 2) then 1 else 0 end as tw
        from wegenregister.mv_wegsegment_wegverharding_lrs
    ) t on st_intersects(g.geom, t.geom)
group by g.naam, t.tw
;

I'll go with these two tweaks:

• don't use CASE with SUM just yet
• don't use ST_Buffer inside ST_Intersects (the index is not being used)

How many rows do the tables have? I'll try adding primary key/index to g.naam and maybe to morf (depending on values in that column).

Could you try with this query (or send the EXPLAIN result at least)?