Обсуждение: Discarding the resulting rows
<table border="0" cellpadding="0" cellspacing="0"><tr><td style="font: inherit;" valign="top">Hello Hackers:<br /><br />Twoquestions.<br /><br />1.<br /><br />I would like to execute queries such as <br /><br />select * from part and timethe query. But I want to ignore how much time is taken for printing the result to a file or the screen.<br /><br />Basically,I would like to discard the result rows after doing all the work required to execute the query.<br /><br />Ilooked at the documentation and I saw something about using the keyword PERFORM rather than SELECT.<br /><br />I triedPERFORM * from part; <br /><br />But this gave me a syntax error.<br /><br />Please let me know how this can be done.<br/><br />2.<br /><br />How do I clear the buffer caches between two query runs?<br />I believe this is not possiblein Postgres. Can someone please confirm this or tell me how it may be done.<br /><br />Thanks,<br /><br />Murali.<br/><br /><br /><br />-----------------------------------------------------------------<br />Please visit <a href="http://NumberFest.com"rel="nofollow" target="_blank">NumberFest.com</a> for educational number puzzles & mind exercisesfor all ages! And please tell your friends about it. Thank You!<br /></td></tr></table><br />
"Murali M. Krishna" <murali1729@yahoo.com> wrote: > Basically, I would like to discard the result rows after doing all > the work required to execute the query. I would use EXPLAIN ANALYZE SELECT ... > I looked at the documentation and I saw something about using the > keyword PERFORM rather than SELECT. I don't remember ever seeing anything like that. Do you have a URL? > How do I clear the buffer caches between two query runs? The easiest way to clear the PostgreSQL cache is to restart the service. PostgreSQL goes through the OS cache; so you'll need to clear that, too. How you do that is dependent on your OS. Of course, in most real use cases, a significant portion of the database would be cached, so unless you're dealing with a very unusual situation, it's hard to see what the value would be of such a benchmark, unless you're trying to create an artificial "worst case" scenario for bounding purposes. Oh, and most serious database servers have 256MB or more of battery backed cache on the RAID controller; don't forget to deal with that somehow. -Kevin
On Mon, Apr 26, 2010 at 2:36 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > "Murali M. Krishna" <murali1729@yahoo.com> wrote: > >> I looked at the documentation and I saw something about using the >> keyword PERFORM rather than SELECT. > > I don't remember ever seeing anything like that. Do you have a URL? > i guess he is refering to the plpgsql's PERFORM statement, which of course he can't use outside a plpgsql function... mmm... well, IIRC, in 9.0 he will be able to do DO $$ PERFORM * FROM tabla; $$ LANGUAGE plpgsql; but i think DO is not an EXPLAINing statement -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
On Mon, Apr 26, 2010 at 3:36 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > "Murali M. Krishna" <murali1729@yahoo.com> wrote: > >> Basically, I would like to discard the result rows after doing all >> the work required to execute the query. > > I would use EXPLAIN ANALYZE SELECT ... There's some overhead to that, of course. ....Robert
On Mon, Apr 26, 2010 at 3:03 PM, Robert Haas <robertmhaas@gmail.com> wrote: > On Mon, Apr 26, 2010 at 3:36 PM, Kevin Grittner > <Kevin.Grittner@wicourts.gov> wrote: >> "Murali M. Krishna" <murali1729@yahoo.com> wrote: >> >>> Basically, I would like to discard the result rows after doing all >>> the work required to execute the query. >> >> I would use EXPLAIN ANALYZE SELECT ... > > There's some overhead to that, of course. > he could see the "actual time" in the very first row of the EXPLAIN ANALYZE... isn't that a value that is more close to what the OP is looking for? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
Robert Haas <robertmhaas@gmail.com> wrote: > On Mon, Apr 26, 2010 at 3:36 PM, Kevin Grittner > <Kevin.Grittner@wicourts.gov> wrote: >> "Murali M. Krishna" <murali1729@yahoo.com> wrote: >> >>> Basically, I would like to discard the result rows after doing >>> all the work required to execute the query. >> >> I would use EXPLAIN ANALYZE SELECT ... > > There's some overhead to that, of course. Good point. At the moment I can't think how to do better, though. Other suggestions, anyone? -Kevin
Jaime Casanova <jcasanov@systemguards.com.ec> writes:
> On Mon, Apr 26, 2010 at 3:03 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Mon, Apr 26, 2010 at 3:36 PM, Kevin Grittner
>> <Kevin.Grittner@wicourts.gov> wrote:
>>> I would use EXPLAIN ANALYZE SELECT ...
>>
>> There's some overhead to that, of course.
> he could see the "actual time" in the very first row of the EXPLAIN
> ANALYZE... isn't that a value that is more close to what the OP is
> looking for?
Well, it will include the instrumentation overhead of EXPLAIN ANALYZE,
which can be nontrivial depending on your hardware and the query plan.
On the other hand, EXPLAIN skips the cost of converting the result data
to text form, not to mention the network overhead of delivering it; so
in another sense it's underestimating the work involved.
I guess the real question is exactly what the OP is hoping to measure
and why.
regards, tom lane
<table border="0" cellpadding="0" cellspacing="0"><tr><td style="font: inherit;" valign="top"><br /><br />Hello All:<br /><br/>The optimizer assumes that data is disk resident when computing the cost of a query plan.<br />I am trying to ascertainwhat the correlation is between times and costs of some benchmark queries to see how good the cost model is.<br/><br />Since I have more than 100 queries, it would be painful to stop and start the server each time to force allthe buffer pages out. Also, some of these queries have large number of result rows. I don't want the time to be skewedby the output time.<br /><br />Cheers,<br /><br />Murali.<br /><br /><br /><br />-----------------------------------------------------------------<br/>Please visit <a href="http://NumberFest.com" rel="nofollow"target="_blank">NumberFest.com</a> for educational number puzzles & mind exercises for all ages! And pleasetell your friends about it. Thank You!<br /><br /><br />--- On <b>Mon, 4/26/10, Tom Lane <i><tgl@sss.pgh.pa.us></i></b>wrote:<br /><blockquote style="border-left: 2px solid rgb(16, 16, 255); margin-left:5px; padding-left: 5px;"><br />From: Tom Lane <tgl@sss.pgh.pa.us><br />Subject: Re: [HACKERS] Discardingthe resulting rows<br />To: "Jaime Casanova" <jcasanov@systemguards.com.ec><br />Cc: "Robert Haas" <robertmhaas@gmail.com>,"Kevin Grittner" <Kevin.Grittner@wicourts.gov>, pgsql-hackers@postgresql.org, "MuraliM. Krishna" <murali1729@yahoo.com><br />Date: Monday, April 26, 2010, 1:25 PM<br /><br /><div class="plainMail">JaimeCasanova <<a href="/mc/compose?to=jcasanov@systemguards.com.ec" ymailto="mailto:jcasanov@systemguards.com.ec">jcasanov@systemguards.com.ec</a>>writes:<br />> On Mon, Apr 26, 2010at 3:03 PM, Robert Haas <<a href="/mc/compose?to=robertmhaas@gmail.com" ymailto="mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>>wrote:<br />>> On Mon, Apr 26, 2010 at 3:36 PM,Kevin Grittner<br />>> <<a href="/mc/compose?to=Kevin.Grittner@wicourts.gov" ymailto="mailto:Kevin.Grittner@wicourts.gov">Kevin.Grittner@wicourts.gov</a>>wrote:<br />>>> I would use EXPLAINANALYZE SELECT ...<br />>> <br />>> There's some overhead to that, of course.<br /><br />> he couldsee the "actual time" in the very first row of the EXPLAIN<br />> ANALYZE... isn't that a value that is more closeto what the OP is<br />> looking for?<br /><br />Well, it will include the instrumentation overhead of EXPLAIN ANALYZE,<br/>which can be nontrivial depending on your hardware and the query plan.<br /><br />On the other hand, EXPLAINskips the cost of converting the result data<br />to text form, not to mention the network overhead of deliveringit; so<br />in another sense it's underestimating the work involved.<br /><br />I guess the real question is exactlywhat the OP is hoping to measure<br />and why.<br /><br /> regards, tom lane<br /><br />-- <br />Sent viapgsql-hackers mailing list (<a href="/mc/compose?to=pgsql-hackers@postgresql.org" ymailto="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a>)<br/>To make changes to your subscription:<br/><a href="http://www.postgresql.org/mailpref/pgsql-hackers" target="_blank">http://www.postgresql.org/mailpref/pgsql-hackers</a><br/></div></blockquote></td></tr></table><br />