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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Jeff Janes <jeff(dot)janes(at)gmail(dot)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
>