On Tue, 6 Dec 2022 at 18:28, James Pang (chaolpan) <chaol...@cisco.com> wrote: > -> 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
I wonder if you did: create statistics xxxxx_starttime_endtime_stats (ndistinct) on starttime,endtime from xxxxx; analyze xxxxx; if the planner would come up with a higher estimate than what it's getting for the above and cause it to use the other index instead. > 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) ? IIRC, generic plan estimates become based on distinct estimations rather than histograms or MCVs. David
