Re: BUG #15577: Query returns different results when executedmultiple times - Mailing list pgsql-bugs
| From | Bartosz Polnik |
|---|---|
| Subject | Re: BUG #15577: Query returns different results when executedmultiple times |
| Date | |
| Msg-id | CAM37ZeuWGV_58O8MeH0TfqBiK0JUONPhHs2=YM2MR61K2E=v9Q@mail.gmail.com Whole thread Raw |
| In response to | Re: BUG #15577: Query returns different results when executedmultiple times (Thomas Munro <thomas.munro@enterprisedb.com>) |
| Responses |
Re: BUG #15577: Query returns different results when executedmultiple times
(David Rowley <david.rowley@2ndquadrant.com>)
|
| List | 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.munro@enterprisedb.com> wrote:
On Tue, Jan 8, 2019 at 12:04 PM Bartosz Polnik <bartoszpolnik@gmail.com> wrote:
> On Mon, Jan 7, 2019 at 11:23 PM Thomas Munro <thomas.munro@enterprisedb.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
pgsql-bugs by date: