Re: wrong rows and cost estimation when generic plan

On Tue, 6 Dec 2022 at 18:28, James Pang (chaolpan) <chaolpan@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=0loops=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
genericplan, 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