Differences in COUNT result when enabling parallel query

Hi there.

Running postgres 9.6.1 (on AWS RDS) we have the following issue:

Without parallel query:

SET max_parallel_workers_per_gather =0;
SELECT count(*) FROM customers WHERE customers.account_id = 402 AND
customers.id IN (SELECT transactions.customer_id FROM transactions WHERE
transactions.account_id = 402 AND transactions.dt_placed BETWEEN
'2016-07-01' AND '2016-10-02' AND transactions.is_valid );

count
--------
273440
(1 row)

With parallel query:

SET max_parallel_workers_per_gather =4;
SELECT count(*) FROM customers WHERE customers.account_id = 402 AND
customers.id IN (SELECT transactions.customer_id FROM transactions WHERE
transactions.account_id = 402 AND transactions.dt_placed BETWEEN
'2016-07-01' AND '2016-10-02' AND transactions.is_valid );
count
--------
355526
(1 row)

The COUNT query is by its nature DISTINCT as its using an IN query. If we
explicitly make it DISTINCT it works, however this should not be necessary.

This returns the correct result with and without parallel query:

SELECT count(DISTINCT id) FROM customers WHERE customers.account_id = 402
AND EXISTS (SELECT transactions.customer_id FROM transactions WHERE
transactions.account_id = 402 AND transactions.dt_placed BETWEEN
'2016-07-01' AND '2016-10-02'AND transactions.is_valid AND customer_id=
customers.id);
count
--------
273440
(1 row)

The schema is rather large, and this only appears to occur when a parallel
scan is selected (in this case the query represents a large proportion of
the data on that database (shard) - hence its using parallel scan not index
scans).

I was wondering if this is a known issue? Maybe fixed in 9.6.2? If so lets
hope AWS RDS updates soon.

If its not a known issue, I can create a better self contained test case.

Query plans:

SET max_parallel_workers_per_gather =0;
Aggregate (cost=312813.62..312813.63 rows=1 width=8)
-> Hash Join (cost=181214.44..311936.55 rows=350828 width=0)
Hash Cond: (customers.id = transactions.customer_id)
-> Seq Scan on customers (cost=0.00..106630.56 rows=1739965
width=4)
Filter: (account_id = 402)
-> Hash (cost=176527.59..176527.59 rows=285668 width=4)
-> Unique (cost=174773.45..176527.59 rows=285668 width=4)
-> Sort (cost=174773.45..175650.52 rows=350828
width=4)
Sort Key: transactions.customer_id
-> Bitmap Heap Scan on transactions
(cost=17953.49..137662.98 rows=350828 width=4)
Recheck Cond: ((account_id = 402) AND
(dt_placed >= '2016-07-01 00:00:00+00'::timestamp with time zone) AND
(dt_placed <= '2016-10-02 00:00:00+00'::timestamp with
time zone))
Filter: is_valid
-> Bitmap Index Scan on
idx_transactions_account_dt_placed (cost=0.00..17865.78 rows=350828
width=0)
Index Cond: ((account_id = 402) AND
(dt_placed >= '2016-07-01 00:00:00+00'::timestamp with time zone) AND
(dt_placed <= '2016-10-02 00:00:00+00'::timestamp w
ith time zone))
(14 rows)

SET max_parallel_workers_per_gather =4;
Finalize Aggregate (cost=274596.57..274596.58 rows=1 width=8)
-> Gather (cost=274596.15..274596.56 rows=4 width=8)
Workers Planned: 4
-> Partial Aggregate (cost=273596.15..273596.16 rows=1 width=8)
-> Hash Join (cost=135177.12..272719.08 rows=350828
width=0)
Hash Cond: (transactions.customer_id = customers.id)
-> Parallel Seq Scan on transactions
(cost=0.00..128743.35 rows=87707 width=4)
Filter: (is_valid AND (dt_placed >= '2016-07-01
00:00:00+00'::timestamp with time zone) AND (dt_placed <= '2016-10-02
00:00:00+00'::timestamp with time zone) AND (accoun
t_id = 402))
-> Hash (cost=106630.56..106630.56 rows=1739965
width=4)
-> Seq Scan on customers (cost=0.00..106630.56
rows=1739965 width=4)
Filter: (account_id = 402)
(11 rows)

--
<http://www.ometria.com/>

Alastair James

Co-Founder and Chief Technology Officer

Tel +44 (0) 20 7016 8408

We're recruiting new Ometrians <https://www.ometria.com/jobs>!