Re: eWeek Poll: Which database is most critical to your - Mailing list pgsql-hackers

Dann Corbit wrote:<br /><blockquote cite="midD90A5A6C612A39408103E6ECDD77B82920CC1F@voyager.corporate.connx.com"
type="cite"><prewrap="">-----Original Message-----<br />From: Neil Conway [<a class="moz-txt-link-freetext"
href="mailto:nconway@klamath.dyndns.org">mailto:nconway@klamath.dyndns.org</a>]<br/>Sent: Tuesday, February 26, 2002
3:04PM<br />To: <a class="moz-txt-link-abbreviated"
href="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a><br/>Subject: Re: [HACKERS] eWeek Poll:
Whichdatabase is most critical to<br />your<br /><br /><br />On Tue, 2002-02-26 at 15:30, Zak Greant wrote:<br
/></pre><blockquotetype="cite"><pre wrap="">Good Day All,<br /><br />eWeek has posted a poll that asks which database
serveris most<br /></pre></blockquote><pre wrap="">critical<br /></pre><blockquote type="cite"><pre wrap="">to your
organization.<br/></pre></blockquote><pre wrap=""><br />The article mentions a MySQL feature which apparently
improved<br/>performance considerably:<br /><br />//<br />MySQL 4.0.1's new, extremely fast query cache is also quite
notable,as<br />no other database we tested had this feature. If the text of an incoming<br />query has a byte-for-byte
matchwith a cached query, MySQL can retrieve<br />the results directly from the cache without compiling the query,
getting<br/>locks or doing index accesses. This query caching will be effective only<br />for tables with few updates
becauseany table updates that clear the<br />cache to guarantee correct results are always returned.<br />//<br /><br
/>Myguess is that it would be relatively simple to implement. Any<br />comments on this?<br /><br />If I implemented
this,any chance this would make it into the tree? Of<br />course, it would be:<br /><br />    - disabled by default<br
/>   - enabled on a table-by-table basis (maybe an ALTER TABLE command)<br /></pre> <pre wrap="">I don't see how it
willdo any good.  There is no "prepare" in<br />Postgresql<br />and therefore you will simply be reexecuting the
queriesevery time any<br />way.  Also, parameter markers only work in embedded SQL and that is a <br />single tasking
system.<br/><br />I think it would be a major piece of work to do anything useful along<br />those lines.<br /><br />If
youlook at how DB/2 works, you will see that they store prepared<br />statements.  Another alternative would be to keep
somepoint in the<br />parser marked and somehow jump to that point, but you would have to<br />be able to save a parse
treesomewhere and also recognize the query.<br /><br />Here is where problems come in...<br />-- Someone wants blue and
blue-green,etc shirts that are backordered<br />SELECT shirt, color, backorder_qty FROM garments WHERE color like<br
/>"BLUE%"<br/><br />Now, another query comes along:<br /><br />-- Someone else wants reddish, etc shirts that are
backordered:<br/>SELECT shirt, color, back
 
order_qty FROM garments WHERE color like "RED%"<br /><br />It's the same query with different data.  Without parameter
markersyou<br />will never know it.  And yet this is exactly the sort of caching that is<br
/>useful.</pre></blockquote>However, an exact match is still not a bad idea.   You might have a different execution
plandepending on the statistics of the data in your column.<br /><br /> If there were a way to store the execution plan
andstart executing from there it still might not be a bad idea.<br /><br /><blockquote
cite="midD90A5A6C612A39408103E6ECDD77B82920CC1F@voyager.corporate.connx.com"type="cite"><pre wrap=""><br /><br
/><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<br
/><<<br/><br />---------------------------(end of broadcast)---------------------------<br />TIP 5: Have you
checkedour extensive FAQ?<br /><br /><a class="moz-txt-link-freetext"
href="http://www.postgresql.org/users-lounge/docs/faq.html">http://www.postgresql.org/users-lounge/docs/faq.html</a><br
/></pre></blockquote><br/><br />