search

waymarkedtrails / waymarkedtrails-backend

Database backend and rendering styles for waymarkedtrails website.
GNU General Public License v3.0
15 stars 6 forks source link

Slow query in update of slopes view #6

Open lonvia opened 2 years ago

lonvia commented 2 years ago

The following query causes the occasional problems:

2021-12-28 11:04:44.629 CET [56297] wmt@planet LOG:  duration: 129114.189 ms  statement: INSERT INTO slopes.slopeways_view (id, tags, nodes) SELECT ways.id, ways.tags, ways.nodes 
        FROM ways 
        WHERE ways.id IN (SELECT way_changeset.id 
        FROM way_changeset 
        WHERE way_changeset.action != 'D') AND 
            tags ? 'piste:type'
            AND NOT (tags ? 'state' AND tags->>'state' = 'proposed')
            AND NOT (tags->>'piste:type' = 'downhill'
                     AND nodes[array_lower(nodes,1)] = nodes[array_upper(nodes,1)])
            AND NOT (tags->>'piste:type' = 'skitour') AND id NOT IN (SELECT slopes.way_relations.id 
        FROM slopes.way_relations) ON CONFLICT (id) DO UPDATE SET tags = EXCLUDED.tags, nodes = EXCLUDED.nodes RETURNING slopes.slopeways_view.id