[postgis-users] Optimizing PostGIS/Geoserver schema for huge dataset

[Andy Colson]

On 03/31/2017 11:38 AM, Andrew Gaydos wrote:
> Hi,
>
>
>
> My questions are
>
>  1. It seems that for every session, there is a one-time penalty for the first query (several minutes) after which queries tend to run much quicker (about 10 seconds for all the tiles to be served). What is going on here?
>  2. Is there a way to optimize GeoServer's queries against this schema, or a more efficient query to try?
>  3. other postgres optimizations that might help?
>
> I'm pretty new to both GeoServer and PostGIS and have a sinking feeling that I could be structuring this dataset and queries more efficiently, but I've run out of ideas and don't have any postgres experts at work to ask, so I'm posting here.
>
> Thanks for any insight!
>
> -Andy

Andy's Unite!

err.. anyway, Here is the problem:

> data table: (10,000 partitions, each with 2.3 million rows)


Lots of partitions will kill planning time. Look at the very bottom of:
https://www.postgresql.org/docs/9.5/static/ddl-partitioning.html

Do you have your heart set on lots of partitions?  How'd you feel about 100? or maybe 1000?

-Andy