Re: PostgreSQL optimizer use seq scan instead of pkey index only scan (in queries with postgres_fdw)
On Mon, May 6, 2019 at 11:53 AM Jeff Janes wrote: > On Mon, May 6, 2019 at 11:38 AM Vitaly Baranovsky < > barvetalfor...@gmail.com> wrote: > >> Thank you, Jeff! >> >> We'll be looking forward to the next version of Postgres in this case. >> >> As far as I understand, you've answered about sending filtering condition >> to a foreign server... Could you, please, clarify about another (the first) >> part of my question? Why the server choose seq scan instead of pk key index >> only scan for the local table? >> >> Thank you >> >> > Aren't those the same thing? The foreign server can't use the where > clause, if it doesn't get sent. > Nevermind. When you said local table, I had some tunnel vision and was thinking of the foreign table as viewed from the perspective of the foreign server (to which it is local), not the actual local table. That too is "fixed" in the same commit to the 12dev branch as the other issue is: commit 4be058fe9ec5e630239b656af21fc083371f30ed Date: Mon Jan 28 17:54:10 2019 -0500 In the planner, replace an empty FROM clause with a dummy RTE. My tests are all done with empty, unanalyzed tables as I just took you DDL without inventing my own DML, so may be different than what what you were seeing with your populated tables. Cheers, Jeff >
Re: PostgreSQL optimizer use seq scan instead of pkey index only scan (in queries with postgres_fdw)
Ough, I believed I had use_remote_estimate = true in my database, but it
was false :(
With use_remote_estimate = true everything works well!
Here is explain analyze with use_remote_estimate = true:
"Nested Loop (cost=100.45..108.97 rows=10 width=16) (actual
time=1.037..1.037 rows=0 loops=1)"
" Output: foreign_table.primary_uuid"
" -> HashAggregate (cost=0.02..0.03 rows=1 width=16) (actual
time=0.004..0.004 rows=1 loops=1)"
"Output: ('ef89a151-3eab-42af-8ecc-8850053aa1bb'::uuid)"
"Group Key: 'ef89a151-3eab-42af-8ecc-8850053aa1bb'::uuid"
"-> Result (cost=0.00..0.01 rows=1 width=16) (actual
time=0.001..0.001 rows=1 loops=1)"
" Output: 'ef89a151-3eab-42af-8ecc-8850053aa1bb'::uuid"
" -> Append (cost=100.43..108.92 rows=2 width=16) (actual
time=1.032..1.032 rows=0 loops=1)"
"-> Foreign Scan on foreign_server.foreign_table
(cost=100.43..104.47 rows=1 width=16) (actual time=0.994..0.994 rows=0
loops=1)"
" Output: foreign_table.primary_uuid"
" Remote SQL: SELECT primary_uuid FROM public.foreign_table
WHERE (($1::uuid = primary_uuid))"
"-> Index Only Scan using local_table_pkey on public.local_table
(cost=0.42..4.44 rows=1 width=16) (actual time=0.035..0.035 rows=0 loops=1)"
" Output: local_table.primary_uuid"
" Index Cond: (local_table.primary_uuid =
('ef89a151-3eab-42af-8ecc-8850053aa1bb'::uuid))"
" Heap Fetches: 0"
"Planning Time: 100.619 ms"
"Execution Time: 1.243 ms"
I tried this with use_remote_estimate = true for different real queries
with a lot of joins and everything works well!
ᐧ
On Mon, May 6, 2019 at 6:53 PM Tom Lane wrote:
> Jeff Janes writes:
> > It works the way you want in version 12, which is currently under
> > development and should be released in 5 months or so.
>
> Even in older versions, the OP would get a significantly smarter
> plan after setting use_remote_estimate = on. I think the core
> issue here is that we won't generate remote parameterized paths
> without that:
>
> /*
> * If we're not using remote estimates, stop here. We have no way
> to
> * estimate whether any join clauses would be worth sending
> across, so
> * don't bother building parameterized paths.
> */
> if (!fpinfo->use_remote_estimate)
> return;
>
> regards, tom lane
>
Re: PostgreSQL optimizer use seq scan instead of pkey index only scan (in queries with postgres_fdw)
On Mon, May 6, 2019 at 11:38 AM Vitaly Baranovsky wrote: > Thank you, Jeff! > > We'll be looking forward to the next version of Postgres in this case. > > As far as I understand, you've answered about sending filtering condition > to a foreign server... Could you, please, clarify about another (the first) > part of my question? Why the server choose seq scan instead of pk key index > only scan for the local table? > > Thank you > > Aren't those the same thing? The foreign server can't use the where clause, if it doesn't get sent. Cheers, Jeff
Re: PostgreSQL optimizer use seq scan instead of pkey index only scan (in queries with postgres_fdw)
Jeff Janes writes: > It works the way you want in version 12, which is currently under > development and should be released in 5 months or so. Even in older versions, the OP would get a significantly smarter plan after setting use_remote_estimate = on. I think the core issue here is that we won't generate remote parameterized paths without that: /* * If we're not using remote estimates, stop here. We have no way to * estimate whether any join clauses would be worth sending across, so * don't bother building parameterized paths. */ if (!fpinfo->use_remote_estimate) return; regards, tom lane
Re: PostgreSQL optimizer use seq scan instead of pkey index only scan (in queries with postgres_fdw)
Thank you, Jeff! We'll be looking forward to the next version of Postgres in this case. As far as I understand, you've answered about sending filtering condition to a foreign server... Could you, please, clarify about another (the first) part of my question? Why the server choose seq scan instead of pk key index only scan for the local table? Thank you ᐧ On Mon, May 6, 2019 at 6:32 PM Jeff Janes wrote: > On Mon, May 6, 2019 at 10:44 AM Vitaly Baranovsky < > barvetalfor...@gmail.com> wrote: > >> Hello all, >> >> I faced strange behavior of PostgreSQL during the query execution. >> > > ... > > >> Also, please, note, that SQL without WHERE clause has been set to the >> foreign server: >> " Remote SQL: SELECT primary_uuid FROM public.foreign_table" >> >> So, the optimizer doesn't select optimal plans for such executions :( >> > > It works the way you want in version 12, which is currently under > development and should be released in 5 months or so. > > Cheers, > > Jeff > >>
Re: PostgreSQL optimizer use seq scan instead of pkey index only scan (in queries with postgres_fdw)
On Mon, May 6, 2019 at 10:44 AM Vitaly Baranovsky wrote: > Hello all, > > I faced strange behavior of PostgreSQL during the query execution. > ... > Also, please, note, that SQL without WHERE clause has been set to the > foreign server: > " Remote SQL: SELECT primary_uuid FROM public.foreign_table" > > So, the optimizer doesn't select optimal plans for such executions :( > It works the way you want in version 12, which is currently under development and should be released in 5 months or so. Cheers, Jeff >
