Received: from bjke.com (firewall-user@ganymede.bjke.com [144.210.8.38]) by hub.org (8.9.3/8.9.3) with ESMTP id XAA91443 for ; Fri, 14 May 1999 23:51:43 -0400 (EDT) (envelope-from djackson@cpsgroup.com) Received: by bjke.com; id PAA24059; Fri, 14 May 1999 15:11:57 -0500 (CDT) Received: from unknown(144.210.12.10) by gauntlet.bjke.com via smap (4.1) id xma023907; Fri, 14 May 99 15:11:24 -0500 Received: by cpsmail with Internet Mail Service (5.5.2448.0) id ; Fri, 14 May 1999 15:11:41 -0500 Message-ID: From: "Jackson, DeJuan" To: "Steven M. Wheeler" , pgsql-sql@hub.org Subject: RE: [SQL] Re: pgsql-sql-digest V1 #225 Date: Fri, 14 May 1999 15:11:34 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2448.0) Content-Type: text/plain > Two problems, one question: > > First my configuration: > I have a 350MB+ DB with two tables in it. One of the tables contains > 2.5M+ rows, > the other is empty. I have 1 index on the table with rows in it. This is > all > running on a Compaq 450MHz Pentium II with 256MB RAM, 1GB of swap. The DB > is on > its own 6GB EIDE drive. > > Problem #1: > Using psql in interactive mode, I issue "select count(*) from currnt;". > The > postmaster starts to read data in, as seen in vmstat by the bi stat > jumping > sharply. Very rapidly, CPU goes to 0% idle, postmaster is using 99.4%, bi > stat > runs about 12K for 60-80 seconds, and then goes to 0, and everything stays > there. > I never get a return and the system stays maxed. When the row count in > this table > was below 2M, I would get a return count. I don't know the answer to your question/problem. You might want to try: select count(1) from currnt; The executor may not have to load the rows from the table then (not certain). You might want to run a gdb on a backend with your query to see where it's hanging. Could help in tracing down the problem. > Problem #2: > Running psql in interactive mode, I issue "select * from currnt where > cast(statdate as text) like '03-%-1999);". This runs for quite awhile, > eats into > swap to the tune of 670MB+ and then bombs out with a palloc error. After > reading > one of the FAQs, I first set ulimit -d to 65535, and then set it to > unlimited. No > change, still bombed out. > This query would be much more efficient (if you have an index on statdate) if you use: select * from currnt where statdate between '3/1/1999' and '3/31/1999'; If you think about your query the only way an index could be used is if you had text(statdate) indexed and then maybe not. > The question: > Am I trying to run too large a DB? Have I exceeded the capabilities of > Postgres > in its current incarnation? Have I got a hope in hell of getting this to > work? I > have already spent many, many, many, too many hours on these problems. > I don't have a database of these sizes, but you shouldn't be past the capacity of PosterSQL.