Re: BUG #15577: Query returns different results when executedmultiple times - Mailing list pgsql-bugs

On Tue, Jan 8, 2019 at 10:39 AM Bartosz Polnik <bartoszpolnik@gmail.com> wrote:
>  Gather (actual time=263.880..507.668 rows=31 loops=1)
>    Workers Planned: 1
>    Workers Launched: 1
>    Single Copy: true
>    ->  Nested Loop Left Join (actual time=193.246..415.020 rows=31 loops=1)
>          ->  Nested Loop (actual time=192.997..414.336 rows=31 loops=1)
>                ->  Hash Join (actual time=0.287..23.958 rows=1890 loops=1)
>                      Hash Cond: (tc.table_d_id = td.id)
>                      ->  Seq Scan on table_c tc (actual time=0.128..23.044 rows=3501 loops=1)
>                            Filter: (table_e_id = 4)
>                            Rows Removed by Filter: 98233
>                      ->  Hash (actual time=0.064..0.069 rows=8 loops=1)
>                            Buckets: 1024  Batches: 1  Memory Usage: 9kB
>                            ->  Seq Scan on table_d td (actual time=0.045..0.052 rows=8 loops=1)
>                                  Filter: (group = 'A'::test.group)
>                                  Rows Removed by Filter: 55
>                ->  Index Scan using table_b_idx_target_id on table_b (actual time=0.203..0.206 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.021..0.021 rows=1 loops=31)
>                Index Cond: ((table_c_id = tc.id) AND (date = '2018-08-31'::date))

Hmm.  So if the plan runs entirely in a worker, we get 31 rows.  If
the plan runs entirely in the leader, we get 31 rows.  But if it runs
in both, we get fewer.  In all cases the Hash Join produces 1890 rows,
and in all cases the Nested Loop probes table_b_idx_target_id for all
of them: "loops=1890".  But somehow the output of the Nested Loop is
not 31 rows (or 31 / 2) when divided over two processes.

As a sanity check, can you please see if the run-only-in-leader case
(max_parallel_workers = 0, so that we see "Workers Launched: 0")
produces the *same* 31 rows as the run-only-in-worker case
(force_parallel_mode = on, so that we see "Single Copy: true")?  That
is, the actual values of those 31 rows, in particular the columns
coming from table_b.

--
Thomas Munro
http://www.enterprisedb.com