pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jackson, DeJuan <djackson@cpsgroup.com>
To: Steven M. Wheeler <swheeler@sabre.com>
To: pgsql-sql@hub.org
Subject: RE: [SQL] Re: pgsql-sql-digest V1 #225
Date: Fri, 14 May 1999 15:11:34 -0500
Message-ID: <D05EF808F2DFD211AE4A00105AA1B5D216ED7A@cpsmail> (raw)

> 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.  




view thread (7+ messages)  latest in thread

Message-ID: <D05EF808F2DFD211AE4A00105AA1B5D216ED7A@cpsmail>
Permalink:  ../D05EF808F2DFD211AE4A00105AA1B5D216ED7A@cpsmail/
Also on:    postgresql.org/message-id/D05EF808F2DFD211AE4A00105AA1B5D216ED7A@cpsmail

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: djackson@cpsgroup.com, swheeler@sabre.com, pgsql-sql@hub.org
  Subject: Re: [SQL] Re: pgsql-sql-digest V1 #225
  In-Reply-To: <D05EF808F2DFD211AE4A00105AA1B5D216ED7A@cpsmail>
  References: <D05EF808F2DFD211AE4A00105AA1B5D216ED7A@cpsmail>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox