set column statistics to max does not help - Mailing list pgsql-general

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:

*** 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)