Re: Bug in query planer ?
От | Clifford Wolf |
---|---|
Тема | Re: Bug in query planer ? |
Дата | |
Msg-id | 200602021043.00027.clifford.wolf@linbit.com обсуждение исходный текст |
Ответ на | Re: Bug in query planer ? (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: Bug in query planer ?
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-bugs |
Hi, On Wednesday 01 February 2006 18:19, you wrote: > Clifford Wolf <clifford.wolf@linbit.com> writes: > > However, it would be great to have get_restriction_variable() and > > get_attstatsslot() extended so they can pass the most common values > > from the statistics cache thru expressions, as described in my earlier > > mail. > > This would only be helpful if the most-common-values list describes > practically all of the column population, which isn't typically the case. Not more than it is the case already for the simple 'variable =3D const'. (Or am I looking at the wrong eqsel() function?) Even when there is no match in the most-common-values list, the list can be used to determine a more realistic selectivity than DEFAULT_EQ_SEL (as it is done already in the 'variable =3D const' cases now). For linear functions it would even be possible to use the histogram_bounds to get a good idea of the selectivity. But that is an optimization that even is not implemented for simple 'variable =3D const' cases yet. > In any case I'm not sure why you're resistant to maintaining an index > on an expression that you are frequently querying by --- that index > could have more direct use than just cueing ANALYZE what to figure > statistics on. In this case definitely not. The only effect that index has is to show the query planner that it is a bad idea to use the indexed expression as inner clause. In the good query plans that expression is evaluated on the heap in a sequential scan (reduces a set of ~10 tuples to ~8 tupels). The index is never used. And this index just sovles this one extreme case, there are hundrets of queries with very suboptimal query plans because of that. I don't want to create hunderts of indexes just to make sure that the index is not used. .. I would do that (auto-generate hundrets of indexes from our slow-query log) when there would be some kind of semi-index type which just collects statistics on ANALYZE. But afaik this is not possible with Postgres right now. Creating that many real indexes would pretty sure slow down inserts and updates that much that it is better to live with bizare query plans for the selects.. yours, - clifford --=20 : Clifford Wolf =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0Tel +43-1-8178292-00 : : LINBIT Information Technologies GmbH =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Fa= x +43-1-8178292-82 : : Sch=C3=B6nbrunnerstr 244, 1120 Vienna, Austria =C2=A0 =C2=A0http://www.li= nbit.com :
В списке pgsql-bugs по дате отправления: