Re: Problems with plan estimates in postgres_fdw
От | Etsuro Fujita |
---|---|
Тема | Re: Problems with plan estimates in postgres_fdw |
Дата | |
Msg-id | 5C861A2A.6030800@lab.ntt.co.jp обсуждение исходный текст |
Ответ на | Re: Problems with plan estimates in postgres_fdw (Antonin Houska <ah@cybertec.at>) |
Список | pgsql-hackers |
(2019/03/09 1:25), Antonin Houska wrote: > Etsuro Fujita<fujita.etsuro@lab.ntt.co.jp> wrote: >> (2019/03/01 20:16), Antonin Houska wrote: >>> Etsuro Fujita<fujita.etsuro@lab.ntt.co.jp> wrote: >>>> Conversely, it appears that add_foreign_ordered_paths() added by the patchset >>>> would generate such pre-sorted paths *redundantly* when the input_rel is the >>>> final scan/join relation. Will look into that. >>> >>> Currently I have no idea how to check the plan created by FDW at the >>> UPPERREL_ORDERED stage, except for removing the sort from the >>> UPPERREL_GROUP_AGG stage as I proposed here: >>> >>> https://www.postgresql.org/message-id/11807.1549564431%40localhost >> >> I don't understand your words "how to check the plan created by FDW". Could >> you elaborate on that a bit more? > > I meant that I don't know how to verify that the plan that sends the ORDER BY > clause to the remote server was created at the UPPERREL_ORDERED and not at > UPPERREL_GROUP_AGG. However if the ORDER BY clause really should not be added > at the UPPERREL_GROUP_AGG stage and if we ensure that it no longer happens, > then mere presence of ORDER BY in the (remote) plan means that the > UPPERREL_ORDERED stage works fine. I don't think we need to consider pushing down the query's ORDER BY to the remote in GetForeignUpperPaths() for each of upper relations below UPPERREL_ORDERED (ie, UPPERREL_GROUP_AGG, UPPERREL_WINDOW, and UPPERREL_DISTINCT); I think that's a job for GetForeignUpperPaths() for UPPERREL_ORDERED, though the division of labor would be arbitrary. However, I think it's a good thing that there is a room for considering remote sorts even in GetForeignUpperPaths() for UPPERREL_GROUP_AGG, because some remote sorts might be useful to process its upper relation. Consider this using postgres_fdw: postgres=# explain verbose select distinct count(a) from ft1 group by b; QUERY PLAN ------------------------------------------------------------------------ Unique (cost=121.47..121.52 rows=10 width=12) Output: (count(a)), b -> Sort (cost=121.47..121.49 rows=10 width=12) Output: (count(a)), b Sort Key: (count(ft1.a)) -> Foreign Scan (cost=105.00..121.30 rows=10 width=12) Output: (count(a)), b Relations: Aggregate on (public.ft1) Remote SQL: SELECT count(a), b FROM public.t1 GROUP BY 2 (9 rows) For this query it might be useful to push down the sort on top of the foreign scan. To allow that, we should allow GetForeignUpperPaths() for UPPERREL_GROUP_AGG to consider that sort pushdown. (We would soon implement the SELECT DISTINCT pushdown for postgres_fdw, and if so, we would no longer need to consider that sort pushdown in postgresGetForeignUpperPaths() for UPPERREL_GROUP_AGG, but I don't think all FDWs can have the ability to push down SELECT DISTINCT to the remote...) Best regards, Etsuro Fujita
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Dean RasheedДата:
Сообщение: Re: [HACKERS] PATCH: multivariate histograms and MCV lists