set column statistics to max does not help

[Radoslav Nedyalkov <rnedyalkov@xxxxxxxxx>]

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)