Re: [HACKERS] Index Tuning Features
* Andrew Sullivan: Just because I'm one of those statistics true believers, what sort of information do you think it is possible for the DBA to take into consideration, when building a hint, that could not in principle be gathered efficiently by a statistics system? Some statistics are very hard to gather from a sample, e.g. the number of distinct values in a column. -- Florian Weimer[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Durlacher Allee 47tel: +49-721-96201-1 D-76131 Karlsruhe fax: +49-721-96201-99 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Index Tuning Features
On Tue, 2006-10-10 at 20:17 -0400, Mark Woodward wrote: Another thing that this brings up is hints to a query. Over the years, I have run into situation where the planner wasn't great. It would be nice to try forcing different strategies on the planner and see if performance caan be improved. /*+ Not on this thread, p-l-e-a-s-e */ -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Index Tuning Features
Another thing that this brings up is hints to a query. Over the years, I have run into situation where the planner wasn't great. It would be nice to try forcing different strategies on the planner and see if performance caan be improved. you can do this by setting enable_access_method type parameters. No, not generally. Usual problems include join order and wrong index, not only wrong access method. Andreas ---(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: [HACKERS] Index Tuning Features
Thanks everybody for comments so far; this will be a useful discussion. On Tue, 2006-10-10 at 18:56 -0400, Robert Treat wrote: On Tuesday 10 October 2006 12:06, Tom Lane wrote: Similar in usage to an EXPLAIN, the RECOMMEND command would return a list of indexes that need to be added to get the cheapest plan for a particular query (no explain plan result though). Both of these seem to assume that EXPLAIN results, without EXPLAIN ANALYZE results to back them up, are sufficient for tuning. I find this idea a bit dubious, particularly for cases of marginal indexes. While I agree with Tom that generally EXPLAIN is not enough for tuning, I also know that when your dealing with queries that have run times in multiples of hours (and the corresponding hour long index builds) EXPLAIN ANALYZE just isn't an option. Anything that can be done to wheedle down your choices before you have to run EXPLAIN ANALYZE is a bonus. IMHO you need EXPLAIN, EXPLAIN ANALYZE and RECOMMEND As Robert points out, using EA can make tuning take a long time and that is the critical factor when you have a whole database/app to tune. This discussion helps me to make explicit what my thoughts had been on what an ideal index tuning process is: 1. Recommendation: Use RECOMMEND to get an 80/20 setting for a statement. As Peter suggests a user-space tool, I also imagine a tool that would automatically run RECOMMEND on all SQL statements in a workload and come up with proposals for additional indexes. We would have a first cut index design in minutes rather than days. 2. Evaluation: We can then create the potential indexes as Virtual ones and then re-run EXPLAINs to model how a whole workload would behave. We can begin to prune low-impact indexes out of the mix at this stage. Again, this can be done automatically. 3. Implementation: We re-create the new indexes as real indexes (perhaps concurrently) 4. Correction: We then run the workload and then use existing tools to spot the statements causing the most problems and manually assess them using EXPLAIN ANALYZE. Manually postulate new Virtual indexes and re-model the workload again as (2) Steps (3) and (4) have both been improved for 8.2. Steps (1) and (2) are completely new steps for 8.3 The above process can be performed without tool support, but its clear that further automation will help greatly here. I foresee that the development of both server-side and tools will take more than one release. Discussion of tool support can begin once we have agreed server-side capability. With that as a backdrop, further comments are: On Tue, 2006-10-10 at 19:15 -0400, Tom Lane wrote: Robert Treat [EMAIL PROTECTED] writes: Anything that can be done to wheedle down your choices before you have to run EXPLAIN ANALYZE is a bonus. Fair enough, but I prefer Peter's suggestion of attaching the hypothetical index definitions to EXPLAIN itself, rather than making bogus catalog entries. Something along the line of EXPLAIN statement ASSUMING INDEX fooi ON foo [ ASSUMING INDEX ... ] I do like this, though for step (2) above we would need to attach the appropriate indexes to each of the SQL statements prior to execution. Doing this for a single SQL statement is fine, but doing that for a whole workload of 1000s of statements is not very practical, hence an externally declarative approach seems better. I can imagine many other declarative approaches other than the one I proposed; it just seems pretty neat to me to use almost exactly the same syntax for a virtual index as for a real index. As I mentioned, ideally this would not be a full-strength catalog object, but I was thinking towards implementation also. Another possibility would be to use a local pg_virtual_indexes table. On Tue, 2006-10-10 at 18:06 +0200, Peter Eisentraut wrote: Simon Riggs wrote: - RECOMMEND command Similar in usage to an EXPLAIN, the RECOMMEND command would return a list of indexes that need to be added to get the cheapest plan for a particular query (no explain plan result though). This functionality also seems useful, but maybe it should be the job of a user-space tool? So from above, Yes, I see a user-space tool also, but not instead. The RECOMMEND command is the minimal server functionality required to enable an (external) automated tuning support tool to be developed. Possible architectures for this functionality include both user-space and server-space options. Much thinking has been done on this in the DB research community, with the general consensus being its easier to extend the planner to cope with postulation that it is to create an external postulation tool that acts (accurately) like the planner. DB2 advisor: An optimizer smart enough to recommend its own indexes. Gary Valentin, Michael Zuliani, Daniel C. Zilio, Guy M. Lohnman, and Alan Skelley. In The 16th International Conference on Data Engineering (ICDE'00), San Diego, CA. IEEE
Re: [HACKERS] Index Tuning Features
The above process can be performed without tool support, but its clear that further automation will help greatly here. I foresee that the development of both server-side and tools will take more than one release. Discussion of tool support can begin once we have agreed server-side capability. If it came to automated tools, wouldn't fit in this discussion to give some performance requirement limits to the RECOMMEND tool ? In a workload not all queries are real time or high priority, and such a lesser impact index can help enough sometimes to meet the requirements, compared to a high impact index which would make the query fly. Example: inserting in a table must be real time, reporting can be taken offline... So it would be nice to have a recommendation tool which can take into account the performance requirements of the individual queries, possibly making the right compromises to meat all requirements for all queries. Cheers, Csaba. ---(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: [HACKERS] Index Tuning Features
On 10/10/06, Mark Woodward [EMAIL PROTECTED] wrote: I think the idea of virtual indexes is pretty interesting, but ultimately a lesser solution to a more fundimental issue, and that would be hands on control over the planner. Estimating the effect of an index on a query prior to creating the index is a great idea, how that is done is something different than building concensus that it should be done. Another thing that this brings up is hints to a query. Over the years, I have run into situation where the planner wasn't great. It would be nice to try forcing different strategies on the planner and see if performance caan be improved. you can do this by setting enable_access_method type parameters. Here's your hammer, all your problems are now nails. The enable_xxx setting are OK for simple queries gone wrong, but if you have a more complex query, any one of those settins may help or hinder different parts of a query, then you would be left with choosing which of them helps more than hurts the over-all query. being able to alter the query plan would help in areas where there are data patterns in a database that the ANALYZE command can't pick up because it is not designed too. Imagine you have a street map database ordered by zip, street, number. The primary order is zipcode, the secondary order is street. There is a relationship of number to street, and zip to street. The analyzer, at least the last time I checked, does not recognize these relationships. So, a search by street and number would probably use a sequential scan rather than the street index. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Index Tuning Features
Mark Woodward [EMAIL PROTECTED] writes: The analyzer, at least the last time I checked, does not recognize these relationships. The analyzer is imperfect but arguing from any particular imperfection is weak because someone will just come back and say we should work on that problem -- though I note nobody's actually volunteering to do so whereas they appear to be for hints. I think the stronger argument is to say that there are some statistical properties that the analyzer _cannot_ be expected to figure out. Either because a) they're simply too complex to ever expect to be able to find automatically, b) too expensive to make it worthwhile in the general case, or c) because of some operational issue such as the data changing frequently enough that the analyzes that would be necessary to keep the statistics up to date would become excessively expensive or even be impossible to perform rapidly enough. The people arguing that hints themselves are of negative benefit are taking the argument far too far. I've never heard an Oracle DBA gripe about having to fix hints on an upgrade; they're usually the first ones to suggest hinting a poorly written query. In fact Oracle is going in the opposite direction of even relying on hints internally. Its plan stability feature depends on generating and storing hints internally associated with every query. The argument against hints is usually that the effort would be better spent elsewhere, not that hints are inherently a bad idea. We already have enable_* parameters and they are absolutely necessary for testing and experimenting to understand whether the planner is incorrect and where it has gone wrong. Hints are just a more precisely targeted version of these. There have been plenty of instances on this list where people posted 20-30 line query plans with several joins of each type where the enable_* parameters were too coarse grained to use effectively. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(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: [HACKERS] Index Tuning Features [2]
On Wed, 2006-10-11 at 14:30 +0200, Kai-Uwe Sattler wrote: sorry for opening a new thread but I have just subscribed to the list. Not at all, glad to hear about your implementation. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Index Tuning Features [2]
On Wed, 2006-10-11 at 14:30 +0200, Kai-Uwe Sattler wrote: We have already an implementation of an index advisor for 7.4.8. It definitely requires some work to port it to 8.2 and to make it usable for production environments. Furthermore, there are some performance bottlenecks (creating virtual indexes, calling the planner twice) but I think they can be solved. I'm sure everybody would be glad to see the existing work submitted as a Work-in-Progress patch to pgsql-patches. We can then have a look at it and see what to do with it. Whatever happens your experience will be invaluable in taking this forward. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Index Tuning Features
Mark Woodward [EMAIL PROTECTED] writes: The analyzer, at least the last time I checked, does not recognize these relationships. The analyzer is imperfect but arguing from any particular imperfection is weak because someone will just come back and say we should work on that problem -- though I note nobody's actually volunteering to do so whereas they appear to be for hints. I think the stronger argument is to say that there are some statistical properties that the analyzer _cannot_ be expected to figure out. Either because a) they're simply too complex to ever expect to be able to find automatically, b) too expensive to make it worthwhile in the general case, or c) because of some operational issue such as the data changing frequently enough that the analyzes that would be necessary to keep the statistics up to date would become excessively expensive or even be impossible to perform rapidly enough. Well, from a purely data domain standpoint, it is impossible to charactize the exact nature of a data set without enough information to recreate it. Anything less must be designed for a fixed set of assumptions. There is no way that every specific trend can be covered by a fixed number of assumptions. The argument that all we need is better statistics completely misses the point. There will *always* be a number cases where the planner will not work optimally. I would say that a simpler planner with better hints will always be capable of creating a better query plan. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Index Tuning Features
Mark Woodward [EMAIL PROTECTED] writes: I would say that a simpler planner with better hints will always be capable of creating a better query plan. This is demonstrably false: all you need is an out-of-date hint, and you can have a worse plan. The argument against hints is not about whether someone could knock together a crappy hint facility and be able to get some use out of it. It is about how much work it would take to design a *good* hint facility that makes it easy to maintain hints that are robust in the face of data and query changes. If someone were to sit down and design and build such a thing, it'd very likely get accepted into core Postgres --- but personally, I think the equivalent amount of effort would be better spent on improving the planner and the statistics. As Josh already noted, Oracle-like hints are pretty likely to get rejected ... not only because of doubts about their true usefulness, but out of fear of falling foul of some Oracle patent or other. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Index Tuning Features
Mark Woodward [EMAIL PROTECTED] writes: I would say that a simpler planner with better hints will always be capable of creating a better query plan. This is demonstrably false: all you need is an out-of-date hint, and you can have a worse plan. That doesn't make it false, it makes it higher maintenance. Hints are understood to require maintenance. The argument against hints is not about whether someone could knock together a crappy hint facility and be able to get some use out of it. It is about how much work it would take to design a *good* hint facility that makes it easy to maintain hints that are robust in the face of data and query changes. If someone were to sit down and design and build such a thing, it'd very likely get accepted into core Postgres --- but personally, I think the equivalent amount of effort would be better spent on improving the planner and the statistics. While it is always true that something can be improved, there comes a point where work outweighs benefits. I can't say that the planner is at that point, but I think that isn't even an issue. The notion of hints would probably one of the biggest steps toward improving the planner. Like I said, it is inarguable that there will always be queries that the planner can not execute efficiently based on the statistics gathered by analze. Since that number must be greater than zero, some methodology to deal with it should be created. As Josh already noted, Oracle-like hints are pretty likely to get rejected ... not only because of doubts about their true usefulness, but out of fear of falling foul of some Oracle patent or other. Well, if it would get rejected if it looked like Oracle, assuming you would probably be one of the people rejecting it, what do you envision as not being rejected? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Index Tuning Features
On Wed, Oct 11, 2006 at 12:40:42PM -0400, Gregory Stark wrote: poorly written query. In fact Oracle is going in the opposite direction of even relying on hints internally. Its plan stability feature depends on generating and storing hints internally associated with every query. But IBM, whose DB2 planner and optimiser is generally regarded as way better than Oracle's (at least by anyone I know who's used both), doesn't like hints. The IBM people all say the same thing Tom has said before: that the work to design the thing correctly is better spent making the planner and optimiser parts smarter and cheaper, because out of that work you also manage not to have the DBA accidentally mess things up by simple-minded rule-based hints. (Note that I'm not trying to wade into the actual argument; I'm just pointing out that even the biggest industry people don't agree on this point.) A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Index Tuning Features
On Wed, Oct 11, 2006 at 03:27:19PM -0400, Mark Woodward wrote: improving the planner. Like I said, it is inarguable that there will always be queries that the planner can not execute efficiently based on the statistics gathered by analze. Since that number must be greater than zero, some methodology to deal with it should be created. Just because I'm one of those statistics true believers, what sort of information do you think it is possible for the DBA to take into consideration, when building a hint, that could not in principle be gathered efficiently by a statistics system? It seems to me that you're claiming that DBAs can have magic knowledge. While I would be delighted to learn that my thumb in the air guesses in the past had turned out to be due to my deep knowledge of my data, I'm instead unhappily confessing that what I really, really wanted when I made those guesses was better knowledge, based on some analysis of the data. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Index Tuning Features
On Oct 11, 2006, at 3:00 PM, Andrew Sullivan wrote: On Wed, Oct 11, 2006 at 12:40:42PM -0400, Gregory Stark wrote: poorly written query. In fact Oracle is going in the opposite direction of even relying on hints internally. Its plan stability feature depends on generating and storing hints internally associated with every query. But IBM, whose DB2 planner and optimiser is generally regarded as way better than Oracle's (at least by anyone I know who's used both), doesn't like hints. The IBM people all say the same thing Tom has said before: that the work to design the thing correctly is better spent making the planner and optimiser parts smarter and cheaper, because out of that work you also manage not to have the DBA accidentally mess things up by simple-minded rule-based hints. (Note that I'm not trying to wade into the actual argument; I'm just pointing out that even the biggest industry people don't agree on this point.) DBAs can mess things up already if they misuse the tools they are provided. Like 'rm'. Which is there, but should _RARELY_ be used on database datafiles. The argument that people _could_ use them in a bad way is silly. Of course, they could use them in a bad way, that's not an _argument_. Everyone agrees people can be stupid. However, the planner will never be perfect. I would like to see 1 out of every 500,000 queries actually benefit from a hint system (which means that 499,999 of the queries were planned perfectly fine by the planner). To fix my one query, that is crucially important to my business, it is a much more sane approach to hint the system to change its plan than it is to have to upgrade my binaries. // Theo Schlossnagle // CTO -- http://www.omniti.com/~jesus/ // OmniTI Computer Consulting, Inc. -- http://www.omniti.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Index Tuning Features
Simon, The University of North Carolina (I think?) did some nice work on not only hypothetical indexes, but hypothetical materialized views (as well as really materialized view planner selection). Have you looked at that work? I think I forwarded the paper code to Jonah at one point ... -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Index Tuning Features
Andrew Sullivan wrote: Just because I'm one of those statistics true believers, what sort of information do you think it is possible for the DBA to take into consideration, when building a hint, that could not in principle be gathered efficiently by a statistics system? It seems to me that you're claiming that DBAs can have magic knowledge. Is one example is the table of addresses clustered by zip-code and indexes on State, City, County, etc? The current statistics systems at least see no correlation between these fields (since the alphabetical ordering of cities and numbering of postal codes is quite different). This makes the planner under-use the indexes because it sees no correlation and overestimates the number of pages read and the random accesses needed. However since San Francisco, CA data happens to be tightly packed on a few pages (since it shares the same few zip codes), few pages are needed and mostly sequential access could be used when querying SF data -- though the optimizer guesses most pages in the table may be hit, so often ignores the indexes. Now I'm not saying that a more advanced statistics system couldn't one-day be written that sees these patterns in the data -- but it doesn't seem likely in the near term. DBA-based hints could be a useful interim work-around. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Index Tuning Features
Tom Lane [EMAIL PROTECTED] writes: Robert Treat [EMAIL PROTECTED] writes: Anything that can be done to wheedle down your choices before you have to run EXPLAIN ANALYZE is a bonus. Fair enough, but I prefer Peter's suggestion of attaching the hypothetical index definitions to EXPLAIN itself, rather than making bogus catalog entries. Something along the line of While I do like avoiding the bogus catalog entries and attaching the declarations to the explain plan. One advantage of that is that I can see extending it to handling IGNORING INDEX foo as well which may be just as important. One disadvantage is that it doesn't let you gather any statistics related to the new index to see what the plan would really be. But indexes don't influence statistics I can hear already from the chorus. But the reason we have indexes not affecting planning is precisely because we don't want to require an analyze after creating an index before it's used. Which these bogus entries would resolve. If we had the ability to create bogus indexes it would kill two birds with one stone. You could use that as the facility for noting which multi-column combinations are interesting. You would create your proposed index, then run ANALYZE and EXPLAIN to your heart's content. When you have it set up just so then you REINDEX your index and you're set. We already have these bogus indexes incidentally, we just create the index with indisvalid=f. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Index Tuning Features [2]
Hi, Am 11.10.2006 um 19:39 schrieb Simon Riggs: I'm sure everybody would be glad to see the existing work submitted as a Work-in-Progress patch to pgsql-patches. Would a patch against a clean 7.4.8 source tree useful for you? Otherwise, I had to spend some time to migrate the code to 8.2... Best, Kai ---(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: [HACKERS] Index Tuning Features
Greg Stark [EMAIL PROTECTED] writes: You would create your proposed index, then run ANALYZE and EXPLAIN to your heart's content. When you have it set up just so then you REINDEX your index and you're set. And when you realize you don't want it after all ... you need an exclusive lock on the table to drop it. (Yes, you would, see relcache load.) The advantage of keeping this idea all inside EXPLAIN is that there's guaranteed to be no interference with anything else. We already have these bogus indexes incidentally, we just create the index with indisvalid=f. Au contraire, that is something completely different. indisvalid=f is really the exact opposite: it's not there to the planner and it is there to the executor. As for the statistics business: really, we use the presence of an index as a hint to gather certain kinds of stats about its underlying table. If we had (ahem) statistical hints then we could gather appropriate data with or without a real associated index. That sort of feature would have additional uses, ie, being able to estimate selectivities more accurately for expressions that might not have anything to do with any of the indexes on a table. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Index Tuning Features
Simon Riggs wrote: For 8.3, I'd like to add the following two related features to assist with Index Tuning and usability: - Virtual Indexes This seems useful, but I'm not sure we need a catalog object for that. It might be sufficient to declare these hypothetical indexes within the EXPLAIN command. That is after all the only place where they are applied. - RECOMMEND command Similar in usage to an EXPLAIN, the RECOMMEND command would return a list of indexes that need to be added to get the cheapest plan for a particular query (no explain plan result though). This functionality also seems useful, but maybe it should be the job of a user-space tool? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Index Tuning Features
Simon Riggs [EMAIL PROTECTED] writes: - Virtual Indexes An index which only exists in the catalog, so is visible to the planner but not the executor. Say what? What would that possibly be useful for, other than crashing any bit of code that failed to know about it? - RECOMMEND command Similar in usage to an EXPLAIN, the RECOMMEND command would return a list of indexes that need to be added to get the cheapest plan for a particular query (no explain plan result though). Both of these seem to assume that EXPLAIN results, without EXPLAIN ANALYZE results to back them up, are sufficient for tuning. I find this idea a bit dubious, particularly for cases of marginal indexes. Specifically, multi-column indexes are not considered very heavily in RECOMMEND. That seems like a bad idea as well --- multicol indexes are exactly the sort of thing a novice DBA might fail to consider. If you're going to do this then you should consider all cases. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Index Tuning Features
Peter Eisentraut wrote: Simon Riggs wrote: For 8.3, I'd like to add the following two related features to assist with Index Tuning and usability: - Virtual Indexes This seems useful, but I'm not sure we need a catalog object for that. It might be sufficient to declare these hypothetical indexes within the EXPLAIN command. That is after all the only place where they are applied. - RECOMMEND command Similar in usage to an EXPLAIN, the RECOMMEND command would return a list of indexes that need to be added to get the cheapest plan for a particular query (no explain plan result though). This functionality also seems useful, but maybe it should be the job of a user-space tool? On this same vein I thought it would be interesting if we added a suggestion to explain analyze... Something like: Your estimated number of rows appears to be off. Have you ran analyze lately? Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Index Tuning Features
On Tue, Oct 10, 2006 at 06:06:09PM +0200, Peter Eisentraut wrote: Simon Riggs wrote: For 8.3, I'd like to add the following two related features to assist with Index Tuning and usability: - Virtual Indexes This seems useful, but I'm not sure we need a catalog object for that. It might be sufficient to declare these hypothetical indexes within the EXPLAIN command. That is after all the only place where they are applied. If you wanted to try multiple scenarios, that might become a pain. I guess it depends on how verbose the syntax was... - RECOMMEND command Similar in usage to an EXPLAIN, the RECOMMEND command would return a list of indexes that need to be added to get the cheapest plan for a particular query (no explain plan result though). This functionality also seems useful, but maybe it should be the job of a user-space tool? I think it makes the most sense to have this in core, though I guess an argument could be made for having it be seperate from the backend. But it'd have to be easy to call from an external tool, such as pgAdmin, which means in probably needs to speak libpq. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Index Tuning Features
On Tuesday 10 October 2006 12:06, Tom Lane wrote:
Similar in usage to an EXPLAIN, the RECOMMEND command would return a
list of indexes that need to be added to get the cheapest plan for a
particular query (no explain plan result though).
Both of these seem to assume that EXPLAIN results, without EXPLAIN
ANALYZE results to back them up, are sufficient for tuning. I find
this idea a bit dubious, particularly for cases of marginal indexes.
While I agree with Tom that generally EXPLAIN is not enough for tuning, I also
know that when your dealing with queries that have run times in multiples of
hours (and the corresponding hour long index builds) EXPLAIN ANALYZE just
isn't an option. Anything that can be done to wheedle down your choices
before you have to run EXPLAIN ANALYZE is a bonus.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] Index Tuning Features
Robert Treat [EMAIL PROTECTED] writes: Anything that can be done to wheedle down your choices before you have to run EXPLAIN ANALYZE is a bonus. Fair enough, but I prefer Peter's suggestion of attaching the hypothetical index definitions to EXPLAIN itself, rather than making bogus catalog entries. Something along the line of EXPLAIN statement ASSUMING INDEX fooi ON foo [ ASSUMING INDEX ... ] although this exact syntax probably doesn't work unless we're willing to make ASSUMING a fully reserved word :-( I have some vague recollection that this idea has been discussed before... regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Index Tuning Features
Simon Riggs [EMAIL PROTECTED] writes: - RECOMMEND command Similar in usage to an EXPLAIN, the RECOMMEND command would return a list of indexes that need to be added to get the cheapest plan for a particular query (no explain plan result though). Both of these seem to assume that EXPLAIN results, without EXPLAIN ANALYZE results to back them up, are sufficient for tuning. I find this idea a bit dubious, particularly for cases of marginal indexes. I think the idea of virtual indexes is pretty interesting, but ultimately a lesser solution to a more fundimental issue, and that would be hands on control over the planner. Estimating the effect of an index on a query prior to creating the index is a great idea, how that is done is something different than building concensus that it should be done. Another thing that this brings up is hints to a query. Over the years, I have run into situation where the planner wasn't great. It would be nice to try forcing different strategies on the planner and see if performance caan be improved. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Index Tuning Features
Mark, Another thing that this brings up is hints to a query. Over the years, I have run into situation where the planner wasn't great. It would be nice to try forcing different strategies on the planner and see if performance caan be improved. See discussion on -performance. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Index Tuning Features
On 10/10/06, Mark Woodward [EMAIL PROTECTED] wrote: I think the idea of virtual indexes is pretty interesting, but ultimately a lesser solution to a more fundimental issue, and that would be hands on control over the planner. Estimating the effect of an index on a query prior to creating the index is a great idea, how that is done is something different than building concensus that it should be done. Another thing that this brings up is hints to a query. Over the years, I have run into situation where the planner wasn't great. It would be nice to try forcing different strategies on the planner and see if performance caan be improved. you can do this by setting enable_access_method type parameters. -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(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
