Skip site navigation (1) Skip section navigation (2)

Re: eWeek Poll: Which database is most critical to your

From: Thomas Swan <tswan-lst(at)ics(dot)olemiss(dot)edu>
To: Dann Corbit <DCorbit(at)connx(dot)com>
Cc: Neil Conway <nconway(at)klamath(dot)dyndns(dot)org>,pgsql-hackers(at)postgresql(dot)org
Subject: Re: eWeek Poll: Which database is most critical to your
Date: 2002-02-27 00:16:00
Message-ID: 3C7C2540.3040903@ics.olemiss.edu (view raw or flat)
Thread:
Lists: pgsql-hackers
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <title></title>
</head>
<body>
Dann Corbit wrote:<br>
<blockquote type="cite"
 cite="midD90A5A6C612A39408103E6ECDD77B82920CC1F(at)voyager(dot)corporate(dot)connx(dot)com">
  <pre wrap="">-----Original Message-----<br>From: Neil Conway [<a class="moz-txt-link-freetext" href="mailto:nconway(at)klamath(dot)dyndns(dot)org">mailto:nconway(at)klamath(dot)dyndns(dot)org</a>]<br>Sent: Tuesday, February 26, 2002 3:04 PM<br>To: <a class="moz-txt-link-abbreviated" href="mailto:pgsql-hackers(at)postgresql(dot)org">pgsql-hackers(at)postgresql(dot)org</a><br>Subject: Re: [HACKERS] eWeek Poll: Which database is most critical to<br>your<br><br><br>On Tue, 2002-02-26 at 15:30, Zak Greant wrote:<br></pre>
  <blockquote type="cite">
    <pre wrap="">Good Day All,<br><br>eWeek has posted a poll that asks which database server is 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 match with 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 because any table updates that clear the<br>cache to guarantee correct results are always returned.<br>//<br><br>My guess 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 will do any good.  There is no "prepare" in<br>Postgresql<br>and therefore you will simply be reexecuting the queries every 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 you look at how DB/2 works, you will see that they store prepared<br>statements.  Another alternative would be to keep some point in the<br>parser marked and somehow jump to that point, but you would have to<br>be able to save a parse tree somewhere 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 markers you<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. &nbsp; You might have a different
execution plan depending on the statistics of the data in your column.<br>
<br>
If there were a way to store the execution plan and start executing from
there it still might not be a bad idea.<br>
<br>
<blockquote type="cite"
 cite="midD90A5A6C612A39408103E6ECDD77B82920CC1F(at)voyager(dot)corporate(dot)connx(dot)com">
  <pre wrap=""><br><br>&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt;<br>&lt;&lt;<br><br>---------------------------(end of broadcast)---------------------------<br>TIP 5: Have you checked our 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>
</body>
</html>



Attachment: unknown_filename
Description: text/html (4.1 KB)

In response to

pgsql-hackers by date

Next:From: Rod TaylorDate: 2002-02-27 00:21:47
Subject: Re: eWeek Poll: Which database is most critical to your
Previous:From: Dann CorbitDate: 2002-02-27 00:08:01
Subject: Re: eWeek Poll: Which database is most critical to your

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group