|
Hi, 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 as a workaround, I remove "starttime" and "endtime" stats tuple from pg_statistic, and optimizer use a DEFAULT value with NULL stats tuple so that index_path cost > the primary key index_path cost, following eqsel function logic, postgres/selfuncs.c
at REL_13_STABLE · postgres/postgres · GitHub 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 |
