Re: PostgreSQL optimizer use seq scan instead of pkey index only scan (in queries with postgres_fdw)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxx> wrote:
Jeff Janes <jeff.janes@xxxxxxxxx> 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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux