Re: Postgres 11.0 Partitioned Table Query Performance

On 8 November 2018 at 15:28, Paul Schaap <ps(at)ipggroup(dot)com> wrote:
> I have an issue, and a partial workaround, with a query outlined below. What
> I am hoping to get to is a Parallel Index Only Scan on my partition indexes
> as theoretically that should be the fastest, but can only get either a
> Parallel Seq Scan on each partition which is very slow, or a non parallel
> Index Only Scan which is faster.
>
> If I express my query this way:
>
> EXPLAIN SELECT trl.*, tr.trans_id
> FROM transactions_raw_load trl
> LEFT OUTER JOIN transactions_raw tr ON tr.trans_id = trl.trans_id;
>
> Note there is an index on tr.trans_id, and no indexes on
> transactions_raw_load.
>
> I get the following poor performing query plan (I got bored and gave up
> after an hour):

Parallel nodes cannot be on the inside of a nested loop join, and
you've mentioned that the other table has no indexes so I guess you
didn't mean on the outside.

You may find that a serial nested loop plan with a parameterised inner
index only scan to be faster than the hash join. If you're finding
that subquery scan is better, then you may want to consider dropping
random_page_cost a bit or increasing effective_cache_size. This will
lower the estimated cost of random IO for indexes scans.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services