[openstreetmap/openstreetmap-website] performance reduced when updating/migrating (#2980)

[Tom Hughes]

Yes what you want to do now is to analyse that query on both databases, do in the postgres console run this:

```
explain analyze SELECT  "current_nodes".* FROM "current_nodes" WHERE (( tile BETWEEN 1334380527 AND 1334380543 OR tile BETWEEN 1334381903 AND 1334381919 OR tile BETWEEN 1334381935 AND 1334381951 OR tile BETWEEN 1334382032 AND 1334382033 OR tile BETWEEN 1334382036 AND 1334382037 OR tile BETWEEN 1334383264 AND 1334383295 OR tile BETWEEN 1334383328 AND 1334383359 OR tile BETWEEN 1334383520 AND 1334383535 OR tile BETWEEN 1334384640 AND 1334384769 OR tile BETWEEN 1334384772 AND 1334384773 OR tile BETWEEN 1334384784 AND 1334384785 OR tile BETWEEN 1334384788 AND 1334384789 OR tile BETWEEN 1334384832 AND 1334384833 OR tile BETWEEN 1334384836 AND 1334384837 OR tile BETWEEN 1334384848 AND 1334384849 OR tile BETWEEN 1334384852 AND 1334384853 OR tile BETWEEN 1334384896 AND 1334384911 OR tile BETWEEN 1334384928 AND 1334384943 OR tile BETWEEN 1334385024 AND 1334385025 OR tile BETWEEN 1334385028 AND 1334385029 OR tile IN (1334380517,1334380519,1334380525,1334381893,1334381895,1334381901,1334381925,1334381927,1334381933,1334382021) ) AND latitude BETWEEN 346183300.0 AND 346816700.00000006 AND longitude BETWEEN -982816700.0 AND -982183300.0) AND "current_nodes"."visible" = true limit 15501;
```

and then paster the output into something like https://explain.depesz.com/ to visualise it better, for example https://explain.depesz.com/s/GWGc is the plan for that query on our production database.

What you're looking for in the first instance is the scan types on the stats tab - you want to see index scans or bitmap heap scans and definitely not any sort of seq scan.

-- 
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/openstreetmap-website/issues/2980#issuecomment-733611290
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/rails-dev/attachments/20201125/f658a1bb/attachment.htm>