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

From: Bartosz Polnik <bartoszpolnik(at)gmail(dot)com>
To: Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>
Cc: David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>, PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Re: BUG #15577: Query returns different results when executed multiple times
Date: 2019-01-08 00:08:56
Message-ID: CAM37ZeuWGV_58O8MeH0TfqBiK0JUONPhHs2=YM2MR61K2E=v9Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Query:

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
);

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=236.640..281.711 rows=26 loops=1)
-> Gather (actual time=236.578..304.194 rows=26 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Nested Loop (actual time=154.947..244.097 rows=13 loops=2)
-> Hash Join (actual time=0.223..14.649 rows=945 loops=2)
Hash Cond: (tc.table_d_id = td.id)
-> Parallel Seq Scan on table_c tc (actual
time=0.083..14.102 rows=1751 loops=2)
Filter: (table_e_id = 4)
Rows Removed by Filter: 49117
-> Hash (actual time=0.044..0.044 rows=8 loops=2)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on table_d td (actual
time=0.028..0.033 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.239..0.242 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.020..0.020 rows=1 loops=26)
Index Cond: ((table_c_id = tc.id) AND (date = '2018-08-31'::date))
Planning Time: 5.047 ms
Execution Time: 304.960 ms

table_a_id | table_c_id | table_b_id
------------+------------+------------
16116185 | 328860 | 2936924
16116256 | 293541 | 2901938
16115788 | 348539 | 3039173
16115788 | 348539 | 2913874
16115788 | 348539 | 2913754
16114813 | 342353 | 3052371
16114789 | 292051 | 3038539
16116069 | 351585 | 3025941
16115861 | 350487 | 2933633
16114814 | 331329 | 2946332
16115106 | 350047 | 2902075
16116260 | 290583 | 2955483
16116066 | 351434 | 3010909
16114811 | 298605 | 2893809
16114811 | 298605 | 2987038
16114811 | 298605 | 3038877
16114811 | 298605 | 3010694
16114811 | 298605 | 2893188
16114811 | 298605 | 2893391
16114811 | 298605 | 2983360
16114811 | 298605 | 3038221
16114811 | 298605 | 3026078
16114811 | 298605 | 2998966
16116249 | 296708 | 3038888
16114811 | 298605 | 2998909
16116241 | 295971 | 3038921
(26 rows)

Here's an example with only 21 rows:

table_a_id | table_c_id | table_b_id
------------+------------+------------
16116185 | 328860 | 2936924
16115788 | 348539 | 3039173
16115788 | 348539 | 2913874
16115788 | 348539 | 2913754
16116256 | 293541 | 2901938
16116256 | 293541 | 2901933
16116256 | 293541 | 2997160
16116069 | 351585 | 3025941
16114789 | 292051 | 3038539
16114813 | 342353 | 3052371
16114814 | 331329 | 2946332
16115861 | 350487 | 2933633
16116066 | 351434 | 3010909
16114811 | 298605 | 2893809
16115106 | 350047 | 2902075
16116241 | 295971 | 3038921
16116260 | 290583 | 2955483
16116249 | 296708 | 3038888
16116260 | 290583 | 2921135
16116260 | 290583 | 2947914
16116260 | 290583 | 2901669
(21 rows)

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

> On Tue, Jan 8, 2019 at 12:04 PM Bartosz Polnik <bartoszpolnik(at)gmail(dot)com>
> wrote:
> > On Mon, Jan 7, 2019 at 11:23 PM Thomas Munro <
> thomas(dot)munro(at)enterprisedb(dot)com> wrote:
> >> 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.
> >
> > Rows returned by two queries are the same.
>
> Can we please also see the actual output in the broken case, where it
> runs in two processes and produces fewer than 31 rows?
>
> --
> Thomas Munro
> http://www.enterprisedb.com
>

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message David Rowley 2019-01-08 00:30:48 Re: BUG #15577: Query returns different results when executed multiple times
Previous Message David Rowley 2019-01-07 23:55:54 Re: BUG #15572: Misleading message reported by "Drop function operation" on DB with functions having same name