Re: use_remote_estimate usage for join pushdown in postgres_fdw

On Wed, Dec 16, 2015 at 11:41 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > I like option #2. I don't really have a strong reason for that, but
> > it feels intuitive to me that we err on the side of using remote
> > estimates when in doubt.
>
> If we believe that, why isn't the default value of use_remote_estimate
> true?
> (Maybe it should be.)
>
> Another option that should be considered is that joins should pay
> attention only to the server-level setting and not to the individual
> tables' settings. This would surely be cheaper to implement, and
> it avoids any questions about whether to OR or AND the individual
> settings.
>
>
To implement server-level setting, we will need to pull it out of
ForeignServer structure like
442 foreach(lc, fpinfo->server->options)
443 {
444 DefElem *def = (DefElem *) lfirst(lc);
445
446 if (strcmp(def->defname, "use_remote_estimate") == 0)
447 fpinfo->use_remote_estimate = defGetBoolean(def);
...
455 }

whereas use_remote_estimate setting for joining relations is readily
available in PgFdwRelationInfo

58 /* Options extracted from catalogs. */
59 bool use_remote_estimate;
60 Cost fdw_startup_cost;
61 Cost fdw_tuple_cost;
62 List *shippable_extensions; /* OIDs of whitelisted
extensions */
...
76 } ;

This use_remote_estimate is true if server level option is true or table
level option is true.

ANDing or ORing use_remote_estimate from the joining relations'
PgFdwRelationInfo looks cheaper than pulling it out of ForeignServer
structure.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company