tomhughes left a comment (openstreetmap/openstreetmap-website#6347)
I've managed to avoid the need for multiple nested aggregations, which tests
suggest is quite a bit more efficient, by doing this:
```sql
WITH total AS (
SELECT
changesets.id AS changeset_id,
SUM(CASE WHEN nodes.version = 1 THEN 1 ELSE 0 END) AS num_created_nodes,
SUM(CASE WHEN nodes.version > 1 AND nodes.visible THEN 1 ELSE 0 END) AS
num_modified_nodes,
SUM(CASE WHEN nodes.version > 1 AND NOT nodes.visible THEN 1 ELSE 0 END) AS
num_deleted_nodes,
SUM(CASE WHEN ways.version = 1 THEN 1 ELSE 0 END) AS num_created_ways,
SUM(CASE WHEN ways.version > 1 AND ways.visible THEN 1 ELSE 0 END) AS
num_modified_ways,
SUM(CASE WHEN ways.version > 1 AND NOT ways.visible THEN 1 ELSE 0 END) AS
num_deleted_ways,
SUM(CASE WHEN relations.version = 1 THEN 1 ELSE 0 END) AS
num_created_relations,
SUM(CASE WHEN relations.version > 1 AND relations.visible THEN 1 ELSE 0
END) AS num_modified_relations,
SUM(CASE WHEN relations.version > 1 AND NOT relations.visible THEN 1 ELSE 0
END) AS num_deleted_relations
FROM changesets
LEFT OUTER JOIN nodes ON nodes.changeset_id = changesets.id
LEFT OUTER JOIN ways ON ways.changeset_id = changesets.id
LEFT OUTER JOIN relations ON relations.changeset_id = changesets.id
WHERE changesets.id = ANY($1::bigint[])
GROUP BY changesets.id
)
UPDATE changesets
SET num_created_nodes = total.num_created_nodes,
num_modified_nodes = total.num_modified_nodes,
num_deleted_nodes = total.num_deleted_nodes,
num_created_ways = total.num_created_ways,
num_modified_ways = total.num_modified_ways,
num_deleted_ways = total.num_deleted_ways,
num_created_relations = total.num_created_relations,
num_modified_relations = total.num_modified_relations,
num_deleted_relations = total.num_deleted_relations
FROM total
WHERE changesets.id = total.changeset_id;
```
I also got rid of the renaming of the CTE in the update but it didn't seem to
serve any purpose.
In principle the CTE shouldn't need to access `changesets` but when I tried to
replace that with an `unnest` of the array postgres decided to table scan
`relations` instead of using the index which was very bad.
--
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/openstreetmap-website/pull/6347#issuecomment-3225456094
You are receiving this because you are subscribed to this thread.
Message ID:
<openstreetmap/openstreetmap-website/pull/6347/c3225456...@github.com>_______________________________________________
rails-dev mailing list
rails-dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/rails-dev
