Re: postgres_fdw cost estimation defaults and documentation

On Mon, Jun 5, 2017 at 2:37 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> In the documentation for fdw_startup_cost, it says "This represents the
> additional overhead of establishing a connection, parsing and planning the
> query on the remote side, etc.". I think that "establishing a connection"
> should be stricken. Either you need a connection or you don't, there is
> nothing the optimizer can do about this. And if do need one, you only
> establish one once (at most), not once per query sent to the remote side. I
> think the implementation correctly doesn't try to account for the overhead
> of establishing a connection, so the docs should remove that claim.

I don't really think there's anything wrong with having "establishing
a connection" in this paragraph. There's a difference between
estimating something in a simplistic way that doesn't necessarily
capture all the variables inherent in the real cost, and not intending
to estimate it. For example, seq_page_cost and random_page_cost
estimate the cost of reading a page, and they make no attempt to
distinguish between the cost of reading a page from the OS page cache
and reading a page from disk, even though those things take radically
different amounts of time. The fact that the physical I/O happens
only sometimes doesn't mean that these GUCs aren't trying to account
for that cost, and, similarly, the fact that a connection to the
foreign server needs to be established only sometimes does not mean
that fdw_startup_cost isn't trying to cover the cost of that. You can
adjust random_page_cost and seq_page_cost up or down depending on how
much caching you think you'll get (and the documentation recommends
this). And you can adjust fdw_startup_cost based on how often you
think you'll need to establish a new connection (but it's probably not
worth bothering with).

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company