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 <[email protected]> wrote:

> Jeff Janes <[email protected]> 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
>