Hi Paul, The links I provided go to EXPLAIN ANALYZE output. No parallelism showing up.
Thank you, Shira On Thu, Jan 9, 2020 at 1:13 PM Paul Ramsey <pram...@cleverelephant.ca> wrote: > EXPLAIN ANALYZE the query. > My data-free guess is that, since you’ve got an aggregate function in > there, is you’re getting a parallel plan under the aggregate, that’s > something you wouldn’t get in 9.6 but would in 12. I don’t think there’s > been any substantial change in the PostGIS raster code, so my guess is > parallelism in PostgreSQL is the “culprit”. > > P > > > On Jan 9, 2020, at 12:47 PM, Shira Bezalel <sh...@sfei.org> wrote: > > > > 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 > > 12.0 plan > > > > 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 > > > > > > > > > > _______________________________________________ > > postgis-users mailing list > > postgis-users@lists.osgeo.org > > https://lists.osgeo.org/mailman/listinfo/postgis-users > > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > https://lists.osgeo.org/mailman/listinfo/postgis-users -- Shira Bezalel Database Administrator & Desktop Support Manager San Francisco Estuary Institute www.sfei.org Ph: 510-746-7304
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
