When dealing with several tables at once—especially large ones—tuning queries becomes a major consideration. The way you write your queries is also important. Two queries can return exactly the same data, but one can take a hundred times longer to finish. The complexity of spatial objects, memory allocation, parallelization, and even storage all affect performance.
Much of what you’ll learn in this chapter has more to do with PostgreSQL proper and SQL in general, but PostGIS piggybacks on PostgreSQL. Learning more about PostgreSQL and how it executes queries will not only make your non-spatial queries faster, but will also improve your spatial queries significantly.
PostgreSQL has a lot of configuration knobs you can turn to optimize your system, and the number of knobs often increases with each major version of PostgreSQL. To keep up to date, a useful site to check out is https://postgresqlco.nf/, which covers all the configuration options available in PostgreSQL, which versions they are available in, and how they’ve changed with each new major version.