Re: [PERFORM] parameterized LIKE does not use index
On Thu, Jun 23, 2005 at 11:55:35AM -0700, Josh Berkus wrote: Bruno, I remember some discussion about delaying planning until the first actual query so that planning could use actual parameters to do the planning. If you really want to have it check the parameters every time, I think you will need to replan every time. I don't know if there is a way to save some of the prepare working while doing this. That wouldn't help much in Kurt's case.Nor in most real cases, which is why I think the idea never went anywhere. I suspect the only way to do this and have it work well would be to cache plans based on the relevant statistics of the parameters passed in. Basically, as part of parsing (which could always be cached, btw, so long as schema changes clear the cache), you store what fields in what tables/indexes each parameter corresponds to. When you go to execute you look up the stats relevant to each parameter; you can then cache plans according to the stats each parameter has. Of course caching all that is a non-trivial amount of work, so you'd only want to do it for pretty complex queries. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] parameterized LIKE does not use index
Of course, I could modify the application and send different SQL depending on which case we're in or just constructing a query with a literal each time, but is there a way to add a hint to the SQL that would cause the query to be re-planned if it's a case that could use the index? Or can I convince the (Perl) driver to do so? There should be an option to tell DBD::Pg not to cache a query plan. Let's see yes. pg_server_prepare=0, passed to the prepare() call. That does the trick! Now I can have the cake and eat it! (clean code and high perf) Now it's tempting to dream of some mechanism that could make the database consider replanning the query automatically once it knows the parameter, or choose from a set of plans depending on the parameter. In this case the general plan was about three orders of magnitude slower than the specialized plan. But I guess this case is not all that common and the developer can work around it. thanks, kurt. -- ir. Kurt De Grave http://www.PharmaDM.com PharmaDM nv. phone: +32-16-298494 Kapeldreef 60, B-3001 Leuven, Belgiumfax: +32-16-298490 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] parameterized LIKE does not use index
On Thu, Jun 23, 2005 at 10:33:18 +0200, Kurt De Grave [EMAIL PROTECTED] wrote: Now it's tempting to dream of some mechanism that could make the database consider replanning the query automatically once it knows the parameter, or choose from a set of plans depending on the parameter. In this case the general plan was about three orders of magnitude slower than the specialized plan. But I guess this case is not all that common and the developer can work around it. I remember some discussion about delaying planning until the first actual query so that planning could use actual parameters to do the planning. If you really want to have it check the parameters every time, I think you will need to replan every time. I don't know if there is a way to save some of the prepare working while doing this. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] parameterized LIKE does not use index
Bruno, I remember some discussion about delaying planning until the first actual query so that planning could use actual parameters to do the planning. If you really want to have it check the parameters every time, I think you will need to replan every time. I don't know if there is a way to save some of the prepare working while doing this. That wouldn't help much in Kurt's case.Nor in most real cases, which is why I think the idea never went anywhere. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] parameterized LIKE does not use index
Kurt, Of course, I could modify the application and send different SQL depending on which case we're in or just constructing a query with a literal each time, but is there a way to add a hint to the SQL that would cause the query to be re-planned if it's a case that could use the index? Or can I convince the (Perl) driver to do so? There should be an option to tell DBD::Pg not to cache a query plan. Let's see yes. pg_server_prepare=0, passed to the prepare() call. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
