Re: BUG #15577: Query returns different results when executed multiple times

Sure.

*SET force_parallel_mode = off;*
*SET parallel_leader_participation = off;*

*alter table_c set (parallel_workers=2);*
explain (costs false, analyze true)
SELECT ta.id AS table_a_id,
tc.id as table_c_id,
tba.id AS table_b_id
FROM test.table_b_active tba
INNER JOIN test.table_c tc ON tba.target_id = tc.id
INNER JOIN test.table_d td ON tc.table_d_id = td.id
LEFT JOIN test.table_a ta ON ta.table_c_id = tc.id AND ta.date =
'2018-08-31' :: DATE
WHERE tba.date BETWEEN '2018-08-10' :: DATE AND '2018-09-01' :: DATE
AND td.group = 'A'
AND tc.table_e_id = 4
AND (
(tba.target_id = tc.id AND tba.group_type = 'A')
OR tba.source_id = tc.id
);

Output:

QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (actual time=203.910..346.758 rows=31 loops=1)
-> Gather (actual time=203.802..373.228 rows=31 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Nested Loop (actual time=157.358..253.888 rows=16 loops=2)
-> Hash Join (actual time=0.365..15.749 rows=945 loops=2)
Hash Cond: (tc.table_d_id = td.id)
-> Parallel Seq Scan on table_c tc (actual
time=0.113..15.043 rows=1751 loops=2)
Filter: (table_e_id = 4)
Rows Removed by Filter: 49117
-> Hash (actual time=0.078..0.078 rows=8 loops=2)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on table_d td (actual
time=0.058..0.065 rows=8 loops=2)
Filter: (group = 'A'::test.group)
Rows Removed by Filter: 55
-> Index Scan using table_b_idx_target_id on table_b
(actual time=0.248..0.252 rows=0 loops=1890)
Index Cond: (target_id = tc.id)
Filter: ((date >= '2018-08-10'::date) AND (date <=
'2018-09-01'::date) AND (((target_id = tc.id) AND (group_type =
'A'::test.group_type)) OR (source_id = tc.id)))
Rows Removed by Filter: 26
-> Index Scan using table_a_uq_001 on table_a ta (actual
time=0.030..0.030 rows=1 loops=31)
Index Cond: ((table_c_id = tc.id) AND (date = '2018-08-31'::date))
Planning Time: 4.923 ms
Execution Time: 374.408 ms

31 rows - correct result.

On Tue, Jan 8, 2019 at 2:30 AM Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>
wrote:

> On Tue, Jan 8, 2019 at 1:48 PM David Rowley
> <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> > On Tue, 8 Jan 2019 at 13:43, Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>
> wrote:
> > > It's as if it thought it was executing a semi-join.
> >
> > EXPLAIN VERBOSE would show the inner_unique flag. If that's set it
> > would act as a semi-join.
>
> Hmm, yes, node->js.single_match == true in one (but not both!) process
> would fit these results. But I don't see a mechanism for that. I
> *guess* it's the worker that is skipping duplicates, because the
> leader usually has time to emit a few tuples while the worker's
> warming up and we see some duplicates (348539) in first few results
> (at least it usually does on my machines for eager plans, though maybe
> on Windows it's different?)
>
> Bartosz, can we please try with force_parallel_mode = off, but also
> parallel_leader_participation = off? (I meant to write that in an
> earlier email but accidentally wrote "on". Gah.) Then we should get
> the same plan (the forced parallel plan is a bit different as you
> noted; maybe whatever is broken isn't triggered that way). You might
> need to set parallel_workers to 2 on the table for it to pick a
> parallel plan without leader participation.
>
> --
> Thomas Munro
> http://www.enterprisedb.com
>