[postgis-users] Performance boost with ST_ValueCount() - wondering why

[Shira Bezalel]

Hi List,

This is a "yeah, but why?" type of question.

I'm testing an upgrade from

Postgres 9.6 and PostGIS 2.3

to

Postgres 12.1 and PostGIS 3.0

One of our queries has gone from about 80 seconds to 30 seconds on the new
releases, which is great, but I'm just trying to figure out why. I realize
there are a host of different reasons why performance changes may result
across different versions and servers, but just wondering if it's related
to a specific performance enhancement in Postgres or PostGIS. Can't seem to
find anything in the release notes that would explain it.

I've narrowed down the improvement to the part of the larger query that
issues an ST_ValueCount() against a large raster and then sums the results.
I can replicate the improvement with this simpler query subset:

SELECT pvc.value, SUM(pvc.count) AS sum
FROM
(SELECT (ST_ValueCount(cv.rast, 1)).*
FROM calveg_whrtype_20m AS cv) AS pvc
GROUP BY pvc.value

9.6 plan <https://explain.depesz.com/s/W8HN>
12.0 plan <https://explain.depesz.com/s/lIRS>

Anything jump out as the reason for the improved plan in terms of changes
to Postgres or PostGIS? Something to do with the HashAggregate it seems,
but not sure why. And yes, this may have nothing to do with PostGIS per se,
so feel free to point me over to the Postgres Performance list, if so. Just
thought I'd start here since the raster function is involved.

Thanks much!
Shira
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200109/4fb50128/attachment.html>