Thread: Automatic index advisor?
Hi, I'm writing a book on SQL, with a short section about query tuning and automatic optimization advisors for different RDBMS platforms, for instance, extensions to EXPLAIN that recommend new indexes based on workload and query plans. When I searched for a solution for PostgreSQL, Gurjeet Singh's name came up as the designer of an index adviser patch for PG. But the latest information I found was in 2007. There's also a project at pgFoundry, but it's listed as in a planning stage, and there are no releases. Can I ask what's the status of this capability for PostgreSQL? Has it become part of PostgreSQL 8.x or 9.0? I couldn't find any information in the docs. Is there any value-add tool for EnterpriseDB? If Gurjeet's patch is still under development, are there any other tools currently available for PostgreSQL that provide this kind of capability? Regards, Bill Karwin
Bill Karwin wrote: > When I searched for a solution for PostgreSQL, Gurjeet Singh's name > came up as the designer of an index adviser patch for PG. But the > latest information I found was in 2007. Sort of. That was originally written by Kai-Uwe Sattler: http://archives.postgresql.org/pgsql-patches/2006-10/msg00083.php Gurjeet talked a bit about his role in updating the whole thing at http://archives.postgresql.org/pgsql-patches/2007-04/msg00300.php and the result of that is that it needed some rework to be acceptable to the main project. Shortly afterwards Tom Lane updated the internal database hooks to allow a better integrated patch: http://archives.postgresql.org/pgsql-patches/2007-05/msg00421.php And as far as I know that's where it stopped at as far as community work goes. Gurjeet created the pgFoundry project but never uploaded any files compatible with the new hooks, and the Index Advisor project remained in the "Wishlist" for the 8.3 and 8.4 versions of the project without anyone seeing new patches submitted. > If Gurjeet's patch is still under development, are there any other > tools currently available for PostgreSQL that provide this kind of > capability? It may still be under development inside Enterprise DB, I haven't seen any reference to it but wouldn't be surprised if it shows up one day as a part of the next release of their "Postgres Plus Advanced Server" commercial product product or something like that. He's still floating around and may chime in directly here. We have a commercial product that does tuning advisory work at http://www.tuningcloud.com/ including index suggestions, but this list isn't the place to launch into a full ad about that. Much like Parallel Query, this sort of project is just big enough to be hard to fund without a commercial sponsor, and once you have one of those and it all works it's really difficult to then give the result away when it's an easy thing to sell as an add-on. There's certainly a dollar figure that would allow taking Kai-Uwe and Gurjeet's work and funding the necessary improvements to finish off making that integrated directly into the database. So far, if anybody has done that, they're not talking about it and/or not releasing it as open-source that I know of. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.us
On Feb 27, 2010, at 12:25 AM, Greg Smith wrote: > So far, if anybody has done that, they're not talking about it and/ > or not releasing it as open-source that I know of. Thanks Greg, it's good to get confirmation of this. I can certainly understand how implementing this feature to a point that would be useful would be a complex project. Regards, Bill Karwin