according to the explain the seqscan at the bottom of the plan is scanning and returning only 5x more rows in 9.6. are the tables really the same size?
> 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
