Re: [PERFORM] O_DIRECT setting

Sorry about the belated reply, its been busy around here. > > Incidentally, postgres heap files suffer really, really bad fragmentation, > > which affects sequential scan operations (VACUUM, ANALYZE, REINDEX ...) > > quite drastically. We have in-house patches that somewhat alleiviate this, > > bu

[PERFORM] Web server to Database Taking forever

Sometimes when you click on a link on my site to access my postgres database it takes forever for it to connect. You can click this link and see how long it takes.http://www.3idiots.com:8080/example.../wantedlist.jspIt doesn't do it all the time. Sometimes its really fast. I can't figure ou

Re: [PERFORM] O_DIRECT setting

On Thu, Sep 30, 2004 at 07:02:32PM +1200, Guy Thornley wrote: > Sorry about the belated reply, its been busy around here. > > > > Incidentally, postgres heap files suffer really, really bad fragmentation, > > > which affects sequential scan operations (VACUUM, ANALYZE, REINDEX ...) > > > quite dra

Re: [PERFORM] Web server to Database Taking forever

Scott, > Sometimes when you click on a link on my site to access my postgres > database it takes forever for it to connect. You can click this link and > see how long it takes. > > http://www.3idiots.com:8080/example.../wantedlist.j

Re: [PERFORM] Web server to Database Taking forever

It is all working now. The thing is I didn't change anything. So do you still think its Tomcat or the jdbc driver? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Josh Berkus Sent: Thursday, September 30, 2004 12:39 PM To: Scott Dunn Cc: [EMAIL PROTECTE

Re: [PERFORM] Web server to Database Taking forever

On Thu, 30 Sep 2004 14:58:27 -0400, Scott Dunn <[EMAIL PROTECTED]> wrote: > It is all working now. The thing is I didn't change anything. So do you > still think its Tomcat or the jdbc driver? > a suspect might be the nature of JSP. on the first hit, JSP is converted to a Servlet, the compiled

Re: [PERFORM] Web server to Database Taking forever

On Thu, 2004-09-30 at 12:58, Scott Dunn wrote: > It is all working now. The thing is I didn't change anything. So do you > still think its Tomcat or the jdbc driver? Are getting an unnaturally large number of processes or threads or pooled connections or what-not somewhere maybe? Have you trie

Re: [PERFORM] Caching of Queries

On Mon, Sep 27, 2004 at 09:30:31PM +0100, Matt Clark wrote: > It's certainly the case that the typical web app (which, along with > warehouses, seems to be one half of the needy apps), could probably do > worse than use pooling as well. I'm not well up enough on pooling to > know how bulletproo

Re: [PERFORM] Caching of Queries

If you're not using a connection pool of some kind then you might as well forget query plan caching, because your connect overhead will swamp the planning cost. This does not mean you have to use something like pgpool (which makes some rather questionable claims IMO); any decent web application la

[PERFORM] spurious function execution in prepared statements.

OK, I have a situation that might be a performance problem, a bug, or an unavoidable consequence of using prepared statements. The short version is that I am getting function executions for rows not returned in a result set when they are in a prepared statement. In other words, I have a query: se

Re: [PERFORM] [HACKERS] spurious function execution in prepared statements.

On Thu, Sep 30, 2004 at 09:45:51AM -0400, Merlin Moncure wrote: > Now, if the same query is executed as a prepared statement, > prepare ps(...) as select f(t.c) from t where [expr] limit 1; > execute ps; > > now, if ps ends up using a index scan on t, everything is ok. However, > if ps does a seq

Re: [PERFORM] [HACKERS] spurious function execution in prepared statements.

> Here's another workaround that may let you use a prepared statement: > > prepare ps(...) as > select f(c) from (select c from t where [expr] limit 1) as t1 > > -Mike I was just exploring that. In fact, the problem is not limited to prepared statements...it's just that they are more likely to

Re: [PERFORM] spurious function execution in prepared statements.

On Thu, 30 Sep 2004, Merlin Moncure wrote: > OK, I have a situation that might be a performance problem, a bug, or an > unavoidable consequence of using prepared statements. The short version > is that I am getting function executions for rows not returned in a > result set when they are in a pr

Re: [PERFORM] [HACKERS] spurious function execution in prepared statements.

"Merlin Moncure" <[EMAIL PROTECTED]> writes: > now, if ps ends up using a index scan on t, everything is ok. However, > if ps does a seqscan, f executes for every row on t examined until the > [expr] criteria is met. Is this a bug? Works for me. regression=# create function f(int) returns int a

Re: [PERFORM] spurious function execution in prepared statements.

Stephan Szabo wrote: > On Thu, 30 Sep 2004, Merlin Moncure wrote: > > > OK, I have a situation that might be a performance problem, a bug, or an > > unavoidable consequence of using prepared statements. The short version > > is that I am getting function executions for rows not returned in a > >

Re: [PERFORM] spurious function execution in prepared statements.

"Merlin Moncure" <[EMAIL PROTECTED]> writes: > Here is the actual query: > select lock_cuid(id), * > ... > order by wcl_vin_no, wcl_claim_no, id > limit 1 Looks like Stephan made the right guess. Logically the LIMIT executes after the ORDER BY, so the sorted result has to be formed co