Consider parallel for lateral subqueries with limit - Mailing list pgsql-hackers

From James Coleman
Subject Consider parallel for lateral subqueries with limit
Date
Msg-id CAAaqYe_HEkmLwf_1iEHxXwQOWiRyiFd=uOu6kwj3sWYdVd1-zA@mail.gmail.com
Whole thread Raw
Responses Re: Consider parallel for lateral subqueries with limit
List pgsql-hackers
I've been investigating parallelizing certain correlated subqueries,
and during that work stumbled across the fact that
set_rel_consider_parallel disallows parallel query on what seems like
a fairly simple case.

Consider this query:

select t.unique1
from tenk1 t
join lateral (select t.unique1 from tenk1 offset 0) l on true;

Current set_rel_consider_parallel sets consider_parallel=false on the
subquery rel because it has a limit/offset. That restriction makes a
lot of sense when we have a subquery whose results conceptually need
to be "shared" (or at least be the same) across multiple workers
(indeed the relevant comment in that function notes that cases where
we could prove a unique ordering would also qualify, but punts on
implementing that due to complexity). But if the subquery is LATERAL,
then no such conceptual restriction.

If we change the code slightly to allow considering parallel query
even in the face of LIMIT/OFFSET for LATERAL subqueries, then our
query above changes from the following plan:

 Nested Loop
   Output: t.unique1
   ->  Gather
         Output: t.unique1
         Workers Planned: 2
         ->  Parallel Index Only Scan using tenk1_unique1 on public.tenk1 t
               Output: t.unique1
   ->  Gather
         Output: NULL::integer
         Workers Planned: 2
         ->  Parallel Index Only Scan using tenk1_hundred on public.tenk1
               Output: NULL::integer

to this plan:

 Gather
   Output: t.unique1
   Workers Planned: 2
   ->  Nested Loop
         Output: t.unique1
         ->  Parallel Index Only Scan using tenk1_unique1 on public.tenk1 t
               Output: t.unique1
         ->  Index Only Scan using tenk1_hundred on public.tenk1
               Output: NULL::integer

The code change itself is quite simple (1 line). As far as I can tell
we don't need to expressly check parallel safety of the limit/offset
expressions; that appears to happen elsewhere (and that makes sense
since the RTE_RELATION case doesn't check those clauses either).

If I'm missing something about the safety of this (or any other
issue), I'd appreciate the feedback.

James

Attachment

pgsql-hackers by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: error_severity of brin work item
Next
From: James Coleman
Date:
Subject: Re: enable_incremental_sort changes query behavior