It's a prepared sql statement on a non-partitioned table , 16millions tuples and multiple indexes on this table. pk_xxxxx primary key (aid,bid,btype) all 3 cols are bigint datatype, there is another index idx_xxxxx(starttime,endtime) , both cols are "timestamp(0) without time zone".
the data distribution is skewed, not even. with first 5 times execution custom_plan, optimizer choose primary key, but when it start building generic plan and choose another index idx_xxxx, obviously generic plan make significant different rows and cost estimation.
below is the sql , sensitive info got masked here (tablename, columnname) .
--with custom_plan
Update on xxxxx (cost=0.56..8.60 rows=1 width=2923) (actual time=0.030..0.031 rows=0 loops=1)
Buffers: shared hit=4
-> Index Scan using pk_xxxxx on xxxxxxx (cost=0.56..8.60 rows=1 width=2923) (actual time=0.028..0.028 rows=0 loops=1)
Index Cond: ((aid = '14654072'::bigint) AND (bid = '243379969878556159'::bigint) AND (btype = '0'::bigint))
Filter: ((password IS NULL) AND ...) AND (starttime = '2071-12-31 00:00:00'::timestamp without time zone) AND (endtime = '2072-01-01 00:00:00'::timestamp without time zone) AND (opentime = '2022-11-07 09:
40:26'::timestamp without time zone)
Buffers: shared hit=4
Planning Time: 1.575 ms
Execution Time: 0.123 ms
--after 5 times execution, it start to build generic plan and thought generic plan cost=0.44..8.48 that less than the customer plan ,so it choose generic plan for following sql executions,
Update on xxxxx (cost=0.44..8.48 rows=1 width=2923) (actual time=8136.243..8136.245 rows=0 loops=1)
Buffers: shared hit=1284549
-> Index Scan using idx_xxxxx_time on xxxxx (cost=0.44..8.48 rows=1 width=2923) (actual time=8136.242..8136.242 rows=0 loops=1)
Index Cond: ((starttime = $7) AND (endtime = $8))
Filter: ((password IS NULL) AND ...(aid = $4) AND (bid = $5) AND (btype = $6) AND...
Rows Removed by Filter: 5534630
Buffers: shared hit=1284549
Planning Time: 0.754 ms
Execution Time: 8136.302 ms
optimzer is very complicated, could you direct me how optimizer to do selectivity estimation when building generic plan, for this case? for custom_plan, optimizer knows boundparams values, but when generic_plan, planner() use boundparams=NULL, it try to calculate average value based on mcv list of the index attributes (starttime,endtime) ?
please check attached about sql details and pg_stats tuple for the index attributes.
Thanks,
James