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

[Giuseppe Broccolo]

Hi Shira,

On Fri, 10 Jan 2020, 21:44 Shira Bezalel, <shira at sfei.org> wrote:

> Hi Giuseppe,
>
> Thank you for your reply. I provided the EXPLAIN (ANALYZE ON, BUFFERS ON)
> output in the links in my initial email. Is that not what you meant?
>

I was meaning the vanilla output returned by the EXPLAIN, not just the
(optimal, BTW) Depesz one.

Yes, the config between the two systems is very similar. I'm thinking the
> shared buffers count differs between the two systems because the row counts
> themselves are different. But why is that? I think I'll turn this question
> over to the general Postgres Performance list now.
>

If the tables are identical in the two system, a different count of rows
could be to a better statistical sampling, so the planner is more accurate
in planning the aggregation. And this could make sense cause since
PostgreSQL 10 statistics have been improved.

Also, consider that even slight differences between the systems can lead to
significant differences. Would be good here to understand what is different
in the configuration.

Anyway, please keep us updated from any insight if you find something :)

Giuseppe.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200111/1a4b98a2/attachment.html>