[BUGS] Differences in COUNT result when enabling parallel query

Поиск

Список

Период

Сортировка

От	Alastair James
Тема	[BUGS] Differences in COUNT result when enabling parallel query
Дата	15 марта 2017 г. 14:27:58
Msg-id	CAB2JvMKjVn41SiE3uDAWsYb0m1BSshZ4SiADVGXi3NU3KMCxkg@mail.gmail.com обсуждение исходный текст
Ответы	Re: [BUGS] Differences in COUNT result when enabling parallel query
Список	pgsql-bugs

Дерево обсуждения

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;

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)