Received: from mx1.sabre.com (mx1.sabre.com [199.100.49.110]) by hub.org (8.9.3/8.9.3) with ESMTP id OAA34767 for ; Wed, 26 May 1999 14:13:25 -0400 (EDT) (envelope-from swheeler@sabre.com) Received: (from mailer@localhost) by mx1.sabre.com (8.9.3/8.9.3) id NAA12683 for ; Wed, 26 May 1999 13:13:01 -0500 (CDT) Received: from (pop3.sabre.com [144.9.112.62]) by mx1.sabre.com via smap (V2.1) id xma012378; Wed, 26 May 99 13:11:47 -0500 Received: from sabre.com ([172.29.63.42]) by pop3.sabre.com (Netscape Messaging Server 3.6) with ESMTP id AAA393E; Wed, 26 May 1999 13:11:44 -0500 Message-ID: <374C3851.46D34162@sabre.com> Date: Wed, 26 May 1999 13:07:13 -0500 From: "Steven M. Wheeler" Organization: The SABRE Group, UNIX Engineering X-Mailer: Mozilla 4.51 [en] (WinNT; I) X-Accept-Language: en MIME-Version: 1.0 To: Tom Lane CC: pgsql-sql@hub.org Subject: Re: [SQL] Re: pgsql-sql-digest V1 #225 References: <1573.927590372@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Boy is my face red;-} I just found out that my vacuum has not been running. Once I vacuumed my DB the performance problems went away. I can now issue a select count(*) from currnt; and get a response in less than 45 seconds. Also, I just sent a message to pgsql-admin about performance with pg_dumpall. Guess what? The vacuum fixed that too. Dumped my 1+GB DB in under 2 minutes (I didn't time it closely). Thanks again for all the help. -- Steven M. Wheeler UNIX Engineering The SABRE Group (918) 292-4119 (918) 292-4165 FAX Tom Lane wrote: > "Steven M. Wheeler" writes: > > Regarding your request for a backtrace, I recompiled with debugging and > > profiling options on. Subsequently I have attached the debugger and > > interrupted the backend a number of times. I keep coming up in mcount() and > > a couple of hash functions. BTW: I had let the query run for over 50+ hours > > without it returning a value. The offending SQL: select count(*) from > > currnt; Is there something more definitive you would like me to do? > > If you could interrupt the backend a few times and provide a full > backtrace (gdb "bt" command) each time, we could maybe form a picture of > what the heck it's doing. This report does seem *very* odd, especially > your discovery that adding a "where" clause speeds it up. (That'd be > fine if the where clause eliminated many rows, but since it doesn't...) > > Also, it would be useful to know what "explain" says about how the query > will be executed. I'd expect an index scan for the "select ... where" > case, and a plain sequential scan for the case without where; if it's > doing something else that would be important to know. > > One more thing --- exactly what is the declaration of the currnt table, > and of its indexes if any? > > BTW, I concur with Bruce's suggestion to try a recent 6.5 snapshot. > I don't see any hashjoin going on here, but it is true that we've > squashed a remarkable number of bugs between 6.4.* and 6.5. Perhaps > you are hitting one of them. > > regards, tom lane