Обсуждение: GSoC Query
Sir/Ma'am,<br /><br /> I am a Mtech student and want to participate in GSoC. I have a project idea and want to discussits feasibility, usability and chance of selection with you.<br /><br />My idea is to add a functionality of Autotuning and Auto Indexing/ Reindexing in DB languages.<br /><br />Though I am not working on this I have some idea aboutimplementation.<br />Idea is that on the no. of rows deleted, Inserted in the table we can make our system capable toreindex the table that will save the time of user.<br /> Similarly using the no. of select hits on a table we can checkthat if maximum no. of times it is on a non-index field we can index on that field to make select faster.<br /><br />Iam looking forward to hear from you. <br /><br /> --<br clear="all" />Thanks & Regards,<br />Gaurav Kumar Gupta<br/>+91-9032844745<br />
On Sun, Mar 28, 2010 at 10:01 PM, gaurav gupta <gauravkumar.gupta@students.iiit.ac.in> wrote: > My idea is to add a functionality of Auto tuning and Auto Indexing/ > Reindexing in DB languages. > > Though I am not working on this I have some idea about implementation. > Idea is that on the no. of rows deleted, Inserted in the table we can make > our system capable to reindex the table that will save the time of user. Reindexing is not routine maintenance for PostgreSQL, so this seems fairly pointless. > Similarly using the no. of select hits on a table we can check that if > maximum no. of times it is on a non-index field we can index on that field > to make select faster. Well, a SELECT statement "hits" a whole row, not a single column; but even if you could somehow figure out a way to tally up per-column statistics (and it's certainly not obvious to me how to do such a thing) it doesn't follow that a column which is frequently accessed is a good candidate for indexing. I don't think this is a good project for a first-time hacker, or something that can realistically be completed in one summer. It sounds more like a PhD project to me. I wrote to another student who is considering submitting a GSOC proposal with some ideas I thought might be suitable. You might want to review that email: http://archives.postgresql.org/pgsql-hackers/2010-03/msg01034.php ...Robert
gaurav gupta wrote: > My idea is to add a functionality of Auto tuning and Auto Indexing/ > Reindexing in DB languages. Ah, the classic request to start with the tuning and index wizards. Really fun to work on, always lots of interest in it. Here's the thing: projects in this area attract endless interest. If it were possible to write something useful in a couple of months, we'd have a hundred such programs fighting for attention. So the fact that we actually have zero of them should tell you something about the actual difficultly level of the work. You could spend the whole summer just reading research papers on this topic and maybe catch up to the late 90's by the end. Here's the usual advice I give to students looking to make a useful contribution to any mature development project: the more boring the work sounds, the more likely it is you'll actually do something people can use. It's easy to find people who want to work on fun projects--so easy that they've all been done already. What's left is either much harder than it looks, or kind of dull to do. The idea behind intentionally picking a boring one is that you're more likely to get one that's unfinished for that reason, rather than because it's actually a year or two of work to complete. Or, in the case you're asking about, a decade or three if you were to start from scratch and were really smart. If you started working on this now rather than stopping to follow the research already done you might catch up to http://portal.acm.org/citation.cfm?id=810505 in a couple of months. > Similarly using the no. of select hits on a table we can check that if > maximum no. of times it is on a non-index field we can index on that > field to make select faster. It's impractical to figure out where indexes should go at without simulating what the optimizer would then do with them against a sample set of queries. You can't do anything useful just with basic statistics about the tables. I would recommend http://msdn.microsoft.com/en-us/library/aa226167(SQL.70).aspx as a good, practical introduction to the topic of what it takes to figure out where indexes go at, from someone who came up with a reasonable solution to that problem. You can find a list of the underlying research they cite (and an idea what has been done since then) at http://portal.acm.org/citation.cfm?id=673646 -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.us
<div class="gmail_quote"><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt0pt 0.8ex; padding-left: 1ex;"><div class="im"><br /><br /><blockquote class="gmail_quote" style="border-left: 1px solidrgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> Similarly using the no. of select hits on a tablewe can check that if maximum no. of times it is on a non-index field we can index on that field to make select faster.<br/></blockquote><br /></div> It's impractical to figure out where indexes should go at without simulating what theoptimizer would then do with them against a sample set of queries. You can't do anything useful just with basic statisticsabout the tables.<br /><br /> I would recommend <a href="http://msdn.microsoft.com/en-us/library/aa226167%28SQL.70%29.aspx" target="_blank">http://msdn.microsoft.com/en-us/library/aa226167(SQL.70).aspx</a>as a good, practical introduction to thetopic of what it takes to figure out where indexes go at, from someone who came up with a reasonable solution to thatproblem. You can find a list of the underlying research they cite (and an idea what has been done since then) at <ahref="http://portal.acm.org/citation.cfm?id=673646" target="_blank">http://portal.acm.org/citation.cfm?id=673646</a><divclass="im"><br /></div></blockquote></div><br />Evenif you have devised a way to find the appropriate set of indexes, just have a index adviser, which would advise a setof indexes for a set of queries and let the DBA and the application user take the final call, after looking at them..<br/><br />Gokul.<br />