Re: BUG #5816: index not used in function
От | Korry Douglas |
---|---|
Тема | Re: BUG #5816: index not used in function |
Дата | |
Msg-id | B0C7DA1F-8574-40DA-9BFD-ECE24C2360E3@enterprisedb.com обсуждение исходный текст |
Ответ на | Re: BUG #5816: index not used in function ("frank" <frank@ros-i.com>) |
Список | pgsql-bugs |
Frank, thanks for educating me.=20 -- Korry > -----Original Message----- > From: Korry Douglas [mailto:korry.douglas@enterprisedb.com]=20 > Sent: Sunday, January 09, 2011 2:34 PM > To: frank > Cc: 'Kevin Grittner'; pgsql-bugs@postgresql.org > Subject: Re: [BUGS] BUG #5816: index not used in function >=20 >=20=20 >=20 > > We may have different perceptions of something being a 'bug'. I always >=20 > > have several simple ways of determining it. One of them is when a >=20 > > work-around is in the proposal. Yours is one. >=20 >=20=20 >=20 > It seems to me that the important question in this case is whether or not= the query produced the correct result. >=20 > The important question by nature is not whether correct result is produce= eventually. In this case, the important thing is the inability to use the = index when in fact, if designed and implemented correctly, it could. >=20 >=20=20 >=20 > You are complaining about a performance issue, not a correctness issue, r= ight? >=20 > No. I am pointing out a low-level-design/implementation defect. Poor perf= ormance is the symptom. Poor performance due to sub-optimal execution of th= e spec is a correctness issue. (See my comment on the nature of a function = below) >=20 >=20=20 >=20 > Kevin's work-around is meant to help you *gain better performance*, not t= o obtain correct results when you are getting incorrect results. >=20 > If it is not a bug, why do we need a work-around? >=20 >=20=20 >=20 > > There can be quite a number of ways of looking at the issue. First, it >=20 > > is truly an implementation matter (making it in the true sense a bug). I >=20 > > do not believe that the spec would in formal way say that 'well, there >=20 > > are caveats where you have to do this and that to work around'. >=20 >=20=20 >=20 > The "spec" (by which I assume you mean the SQL standard) says nothing abo= ut which execution plan will be selected (by) the optimizer. >=20 > No. Whatever the spec, it will never say that a function will not work as= a function, or something that works outside one will not work once moved i= nside. >=20 >=20=20 >=20 > > <snip> >=20 > > If by 'kept from one execution to another' means that (the concept of) a >=20 > > plan is implemented static, this can be a low level design issue, which >=20 > > in general will still be regarded as implementation, thus a bug. >=20 >=20=20 >=20 > The execution plan is not quite static - it is computed the first time yo= u run the function (within a session) and is discarded when your session en= ds (or when the compiled function/execution plan becomes obsolete because o= f a change to a dependency). >=20 >=20=20 >=20 > That is by design. >=20 > Then the design is poor. >=20 >=20=20 >=20 > If you want a dynamic plan that is re-computed each time you execute the = query, you can get that behavior by using dynamic SQL, as Kevin suggested. >=20 >=20=20 >=20 > This seems far fetched and irrelevant. Whatever is truly static should be= implemented static; whatever is dynamic should be implemented dynamic; wha= tever is partially static, the static part should be static and the dynamic= part should be dynamic. It is natural and correct treatment. >=20 >=20=20 >=20 > Purely dynamic situation in which the final query can not be determined i= n any fashion, will have to be constructed either outside of the function o= r within, so EXECUTE is the only way to handle. What do you think of requir= ing the caller to construct such a static statement as =93select count(*) f= rom sometable=94 and use EXECUTE? >=20 >=20=20 >=20 > By the same token, =93select thiscolumn from thistable where upper(thisco= lumn) like $1=94 has to be treated statically for the static part. The only= unknown is the parameter, which can be, by the right design and implementa= tion, delayed till execution (runtime). The code to deal with this is what = I pointed out (via a conditional). If the plan is a piece of code, then the= conditional will be in it. If the plan is a piece of text to be further in= terpreted for actual execution (why would one want to do it that way?), the= conditional could contain a text reference to two pieces of code (w/o the = use of the index). If it is not properly designed/implemented and such situ= ation results in the loss of the said ability, it is a defect to be address= ed. Whether one wants to address it is one issue. A defect is a defect. >=20 >=20=20 >=20 > You seem to suggest that the plan was only built at (the first) execution= . That is poor design/implementation. >=20 >=20=20 >=20 > Lastly, what is a function? One of the fundamental features of a function= is encapsulation. One is guaranteed some well-defined output based on well= -defined input. No implementation detail is necessary or is obliged to be a= vailable. The user does not have to know what table or anything for that ma= tter is involved. When you push for what is suggested as a work-around, it = defeats one of the basic purposes for a function. >=20 >=20=20 >=20 > More can be said, but why one wants to defend a defect is quite beyond me. >=20 >=20=20 >=20 > -- Korry >=20
В списке pgsql-bugs по дате отправления: