From: | "James Pang (chaolpan)" <chaolpan(at)cisco(dot)com> |
---|---|
To: | David Rowley <dgrowleyml(at)gmail(dot)com> |
Cc: | "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)org>, "jamespang886(at)gmail(dot)com" <jamespang886(at)gmail(dot)com> |
Subject: | RE: wrong rows and cost estimation when generic plan |
Date: | 2022-12-06 07:16:55 |
Message-ID: | PH0PR11MB519143BDA29D3F759F83E98FD61B9@PH0PR11MB5191.namprd11.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
No create statistics on starttime_endtime(distinct), that index on "starttime,endtime", use default analyze, I tested to increase statistics_targets but that no help. Could you provide the function name for generic plan selectivity estimation?
Thanks,
James
-----Original Message-----
From: David Rowley <dgrowleyml(at)gmail(dot)com>
Sent: Tuesday, December 6, 2022 1:59 PM
To: James Pang (chaolpan) <chaolpan(at)cisco(dot)com>
Cc: pgsql-performance(at)lists(dot)postgresql(dot)org; jamespang886(at)gmail(dot)com
Subject: Re: wrong rows and cost estimation when generic plan
On Tue, 6 Dec 2022 at 18:28, James Pang (chaolpan) <chaolpan(at)cisco(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2022-12-06 09:04:21 | Re: wrong rows and cost estimation when generic plan |
Previous Message | David Rowley | 2022-12-06 05:59:11 | Re: wrong rows and cost estimation when generic plan |