Re: Getting sorted data from foreign server - Mailing list pgsql-hackers
| From | Ashutosh Bapat |
|---|---|
| Subject | Re: Getting sorted data from foreign server |
| Date | |
| Msg-id | CAFjFpRc4Uh+6RyUGJnzv6SORww841nTxf2cZS-Xp1+4y4earvw@mail.gmail.com Whole thread Raw |
| In response to | Re: Getting sorted data from foreign server (Robert Haas <robertmhaas@gmail.com>) |
| Responses |
Re: Getting sorted data from foreign server
(Jeevan Chalke <jeevan.chalke@enterprisedb.com>)
Re: Getting sorted data from foreign server (Robert Haas <robertmhaas@gmail.com>) |
| List | pgsql-hackers |
On Thu, Oct 8, 2015 at 9:39 PM, Robert Haas <robertmhaas@gmail.com> wrote:
On Tue, Oct 6, 2015 at 6:46 AM, Ashutosh Bapat
<ashutosh.bapat@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 for
> a given base relation. For every such path one path with pathkeys is added.
> If there is an index matching on the foreign server, getting the data sorted
> 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
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
pgsql-hackers by date: