Обсуждение: [BUGS] 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_
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_
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_
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_
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)
 | Alastair James Co-Founder and Chief Technology Officer |
We're recruiting new Ometrians!
Many thanks.
Lets hope AWS update RDS to 9.6.2 soon.
Cheers
Al
On Wed, Mar 15, 2017 at 3:33 PM, Euler Taveira <euler@timbira.com.br> wrote:
2017-03-15 5:27 GMT-03:00 Alastair James <al@ometria.com>:I was wondering if this is a known issue? Maybe fixed in 9.6.2? If so lets hope AWS RDS updates soon.Yes, it is (see similar bug report in the commit message) and was fixed in 9.6.2 [1]. The workaround is to disable parallel query for this query.
[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a= commit;h= 41e2b84ce1b89ae0125dd34318c56a a51386e2a2
--Euler Taveira Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
| Alastair James Co-Founder and Chief Technology Officer |
We're recruiting new Ometrians!