generic plan slowness

[smellman]

When we use "PREPARE" AND "EXECUTE" (​https://www.postgresql.org/docs/9.6/sql-prepare.html), sometime generic plan will be slowness.

I find this issue with openmaptiles project(​https://github.com/openmaptiles/postgis/pull/13). I put my report in my gist(​https://gist.github.com/smellman/060ee2693589e070deacc1715f4642f3#file-openmaptiles_postserve_postgis2-5-1_research-txt). First 5times queries are fast but after queries are slow in same session. The problem happen in 2.4.0, 2.4.7, 2.5.1, 2.5.2 and trunk.

Also this issue come from fixes #3731 . I built and ran with my revert commit(​https://github.com/smellman/postgis-1/commit/7e2cbaf8e56f15bbdb1f7b49511669c91fbb8d25), the slowness issue seem fixed. But it will raise #3731 problem again.

[pramsey]

In 17384:

Guard against zero'd histo edge, but also avoid arbitrarily inflating edge
References #4362

[smellman]

Replying to pramsey:

In 17384:

Guard against zero'd histo edge, but also avoid arbitrarily inflating edge
References #4362

This fix still slow but 2x faster than old version. I put new report in my gist(​https://gist.github.com/smellman/d486a047386390b818da492941b587de#file-openmaptiles_postgis-svn-trunk_with_postgresql11-txt).

[pramsey]

Question one: does it still display the fast-fast-fast-fast-fast-slow pattern? (Good until execution number six?) Question two: if number one is "yes", then for each table in the query, can you generate the planned selectivity relative to your query box using _postgis_selectivity() and also the *actual* selectivity, by comparing the number of rows returned when you query with that box to the number of rows in the table?

The five-fast-then-slow is a symptom of the selectivity estimate for some query boxes being overly pessimistic, and overestimating how many rows a given box will return.

I talked about this with your project colleague here at foss4g-na and while this is an interesting nit, for your particular usage you will be better off just dropping the prepare/execute pattern: your queries are not nearly fast enough to reap measurable benefit from stripping out the planning overhead.

That said, I'd still like to confirm if there's some bad selectivity estimates being generated for particular combinations of data and query key and see if we can rectify that.