On 8 November 2018 at 15:28, Paul Schaap <ps@xxxxxxxxxxxx> 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
