Hello all,
We're stuck at a wrong plan that the planner insists on.(pg 14.2)
It's an aggregation over a single table.
The planner always goes for an extra scan over a partial index.
We have set statistics on the relevant columns to the max of 10000 and
could not get correct row estimates. None of the cost* settings helped too.
Disabling bitmapscan below brings up the correct plan. Do we have another
option here ?
Maybe it is a more general question of what to do when the statistics
samples cannot get the right numbers?
Thank you in advance for your help,
Rado
Here are the details:
See the partial index that matches one of the query where clauses :
"transaction_events_0100_0200_merchant_id_id_idx" btree (merchant_id, id)
WHERE (event_type::text = ANY (ARRAY['REFUND'::character varying::text,
'CHARGE_BACK'::character varying::text])) AND (current_status_id <> ALL
(ARRAY[24, 10]))
the distribution of event_type is
count | event_type
----------+-------------
14908 | CHARGE_BACK
134007 | REFUND
99846581 | PAYOUT
(3 rows)
*** The table:
test=# \d+ transaction_events_0100_0200
Table
"public.transaction_events_0100_0200"
Column | Type | Collation |
Nullable | Default | Storage | Compression | Stats target | Description
----------------------------+-----------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
id | bigint | | not
null | | plain | | |
transaction_id | bigint | |
| | plain | | 10000 |
event_type | character varying(255) | |
| | extended | | 10000 |
event_amount | numeric(12,2) | |
| | main | | |
current_status_id | integer | |
| | plain | | 10000 |
payout_due_date | date | |
| | plain | | |
actual_payout_date | date | |
| | plain | | |
current_payout_event_id | integer | |
| | plain | | |
created_at | timestamp without time zone | | not
null | | plain | | |
updated_at | timestamp without time zone | | not
null | | plain | | |
installment_number | integer | |
| | plain | | |
installments_count | integer | |
| | plain | | |
fixed_fee | numeric(12,2) | |
| | main | | |
acceleration_fee | numeric(12,2) | |
| | main | | |
processing_fee | numeric(12,2) | |
| | main | | |
origin_event_id | bigint | |
| | plain | | |
destination_event_id | bigint | |
| | plain | | |
payout_deduct_status | character varying | |
| | extended | | |
merchant_id | integer | | not
null | | plain | | 1000 |
current_merchant_payout_id | bigint | |
| | plain | | |
Indexes:
"transaction_events_0100_0200_pkey" PRIMARY KEY, btree (id)
"transaction_events_0100_0200_current_status_id_transaction__idx" btree
(current_status_id, transaction_id) WHERE current_status_id <> ALL
(ARRAY[24, 10])
"transaction_events_0100_0200_merchant_id_id_idx" btree (merchant_id,
id) WHERE (event_type::text = ANY (ARRAY['REFUND'::character varying::text,
'CHARGE_BACK'::character varying::text])) AND (current_status_id <> ALL
(ARRAY[24, 10]))
"transaction_events_0100_0200_merchant_id_transaction_id_idx" btree
(merchant_id, transaction_id) WHERE current_status_id <> ALL (ARRAY[24, 10])
"transaction_events_0100_0200_transaction_id_idx" btree
(transaction_id) WITH (fillfactor='100')
"transaction_events_0100_0200_transaction_id_idx1" btree
(transaction_id) WHERE event_type::text = 'CHARGE_BACK'::text AND
payout_deduct_status::text = 'PENDING'::text
*** The query:
SELECT public.transaction_events_0100_0200.transaction_id,
SUM(public.transaction_events_0100_0200.event_amount) AS amount
FROM public.transaction_events_0100_0200
WHERE public.transaction_events_0100_0200.transaction_id =
ANY('{2735975647,...,2697582948}')
AND public.transaction_events_0100_0200.event_type IN ('REFUND',
'CHARGE_BACK')
AND public.transaction_events_0100_0200.current_status_id IN (11,15,67)
GROUP BY public.transaction_events_0100_0200.transaction_id;
*** The executions:
GroupAggregate (cost=202.67..202.69 rows=1 width=40) (actual
time=56.197..56.198 rows=0 loops=1)
Group Key: transaction_id
-> Sort (cost=202.67..202.67 rows=1 width=14) (actual
time=56.194..56.196 rows=0 loops=1)
Sort Key: transaction_id
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on transaction_events_0100_0200
(cost=198.64..202.66 rows=1 width=14) (actual time=56.186..56.187 rows=0
loops=1)
Recheck Cond: (((event_type)::text = ANY
('{REFUND,CHARGE_BACK}'::text[])) AND (current_status_id <> ALL
('{24,10}'::integer[])) AND (transaction_id = ANY
('{2735975647,...,2697582948}'::bigint[])))
Filter: (current_status_id = ANY ('{11,15,67}'::integer[]))
-> BitmapAnd (cost=198.54..198.54 rows=1 width=0) (actual
time=55.345..55.346 rows=0 loops=1)
* -> Bitmap Index Scan on
transaction_events_0100_0200_merchant_id_id_idx (cost=0.00..10.71 rows=458
width=0) (actual time=50.530..50.531 rows=148279 loops=1)*
-> Bitmap Index Scan on transaction_events_0100_0200_transaction_id_idx
(cost=0.00..187.58 rows=44 width=0) (actual time=0.071..0.071 rows=0
loops=1)
Index Cond: (transaction_id = ANY
('{2735975647,...,2697582948}'::bigint[]))
Planning Time: 1.517 ms
Execution Time: 56.298 ms
(14 rows)
Time: 58.636 ms
test=# set enable_bitmapscan to off;
SET
Time: 0.504 ms
test=# \i q221.sql
QUERY PLAN
----------------------------------------------------------------------------------------------------
GroupAggregate (cost=0.57..228.43 rows=1 width=40) (actual
time=0.238..0.240 rows=0 loops=1)
Group Key: transaction_id
-> Index Scan using transaction_events_0100_0200_transaction_id_idx on
transaction_events_0100_0200 (cost=0.57..228.41 rows=1 width=14) (actual
time=0.235..0.236 rows=0 loops=1)
Index Cond: (transaction_id = ANY
('{2735975647,...,2697582948}'::bigint[]))
Filter: (((event_type)::text = ANY
('{REFUND,CHARGE_BACK}'::text[])) AND (current_status_id = ANY
('{11,15,67}'::integer[])))
Settings: enable_bitmapscan = 'off'
Planning Time: 1.204 ms
Execution Time: 0.312 ms
(8 rows)
