On Thu, Oct 8, 2015 at 9:39 PM, Robert Haas <robertmh...@gmail.com> wrote:
> On Tue, Oct 6, 2015 at 6:46 AM, Ashutosh Bapat
> <ashutosh.ba...@enterprisedb.com> wrote:
> > standard_qp_callback() sets root->query_pathkeys to pathkeys on which the
> > result of query_planner are expected be sorted upon (see the function for
> > more details). The patch checks if any prefix of these pathkeys can be
> > entirely evaluated using the foreign relation and at the foreign server
> > under consideration. If yes, it gets estimates of costs involved and adds
> > paths with those pathkeys. There can be multiple pathkeyless paths added
> > a given base relation. For every such path one path with pathkeys is
> > If there is an index matching on the foreign server, getting the data
> > from foreign server improves execution time as seen from the results. The
> > patch adds this functionality entirely in postgres_fdw.
> In the interest of full disclosure, I asked Ashutosh to work on this
> patch and have discussed the design with him several times. I believe
> that this is a good direction for PostgreSQL to be going. It's
> trivially easy right now to write a query against an FDW that performs
> needlessly easy, because a join, or a sort, or an aggregate is
> performed on the local server rather than the remote one. I, and
> EnterpriseDB, want that to get fixed. However, we also want it to get
> fixed in the best possible way, and not to do anything unless there is
> consensus on it. So, if anyone has opinions on this topic, please
> jump in.
> That having been said, here are some review comments on this patch:
> - You consider pushing down ORDER BY if any prefix of the query
> pathkeys satisfy is_foreign_expr(), but that doesn't seem right to me.
> If the user says SELECT * FROM remotetab ORDER BY a, unsafe(a),
> ordering the result set by a doesn't help us much. We've talked a few
> times about an incremental sort capability that would take a stream of
> tuples already ordered by one or more columns and sort each group by
> additional columns, but I don't think we have that currently. Without
> that capability, I don't think there's much benefit in sorting by a
> prefix of the pathkeys. I suspect that if we can't get them all, it's
> not worth doing.
I somehow thought, we are using output, which is ordered by prefix of
pathkeys in Sort nodes. But as you rightly pointed out that's not the case.
Only complete pathkeys are useful.
> - Right now, you have this code below the point where we bail out if
> use_remote_estimate is not set. If we keep it like that, the comment
> needs updating. But I suggest that we consider an ordered path even
> if we are not using remote estimates. Estimate the sorted path to
> cost somewhat more than the unsorted path, so that we only choose that
> path if the sort actually benefits us. I don't know exactly how to
> come up with a principled estimate given that we don't actually know
> whether the remote side will need an extra sort or not, but maybe a
> dumb estimate is still better than not trying a sorted path.
I like that idea, although there are two questions
1. How can we estimate cost of getting the data sorted? If there is an
appropriate index on foreign server we can get the data sorted at no extra
cost. If there isn't the cost of sorting is proportionate to NlogN where N
is the size of data. It seems unreasonable to arrive at the cost of sorting
by multiplying with some constant multiplier. Also, the constant multiplier
to the NlogN estimate depends heavily upon the properties of foreign server
e.g. size of memory available for sorting, disk and CPU speed etc. The last
two might have got factored into fdw_tuple_cost and fdw_startup_cost, so
that's probably taken care of. If the estimate we come up turns out to be
too pessimistic, we will not get sorted data even if that's the right thing
to do. If too optimistic, we will incur heavy cost at the time of
execution. Setting the cost estimate to some constant factor of NlogN would
be too pessimistic if there is an appropriate index on foreign server.
Otherway round if there isn't an appropriate index on foreign server.
Even if we leave these arguments aside for a while, the question remains as
to what should be the constant factor 10% or 20% or 50% or 100% or
something else on top of the estimate for simple foreign table scan
estimates (or NlogN of that)? I am unable to justify any of these factors
myself. What do you say?
Given that we save on the local resources required for sorting if we get
the data sorted from the foreign server, it might be better to always get
it sorted from the foreign server, but our costing model doesn't account
for that benefit today.
We might be able to do a good job there if we know more things about the
foreign server/table e.g. indexes on the foreign table, memory available
for sorting etc. that leads to your next comment.
> - In the long run, we should probably either add some configuration so
> that the local side can make better estimates even without
> use_remote_estimate, or maybe have a way for the FDW to keep a cache
> of data in the system catalogs that is updated by ANALYZE. Then,
> analyzing a foreign table could store information locally about
> indexes and so forth, instead of starting each query planning cycle
> with no knowledge about the remote side. That's not a matter for this
> patch, I don't think, but it seems like something we should do.
To an extent knowing which indexes are available on the tables on foreign
server will help. Now, I do understand that not every foreign server will
have indexes like PostgreSQL, but as long as whatever they have can be
translated into a language that PostgreSQL can understand it should be
fine. From that point of view, will it help if we have declarative indexes
on foreign tables similar to the declarative constraints? Obviously, we
will be burdening user with extra work of maintaining the declarative
indexes in sync like we do for constraints. But we might ease the burden
when we get to fetch that information automatically from the foreign server.
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
The Postgres Database Company