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=100000 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 <tgl@sss.pgh.pa.us> wrote:
Jeff Janes <jeff.janes@gmail.com> 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
В списке pgsql-performance по дате отправления:
Предыдущее
От: Jeff JanesДата:
Сообщение: Re: PostgreSQL optimizer use seq scan instead of pkey index only scan(in queries with postgres_fdw)
Следующее
От: Jeff JanesДата:
Сообщение: Re: PostgreSQL optimizer use seq scan instead of pkey index only scan(in queries with postgres_fdw)