RE: [SQL] Re: pgsql-sql-digest V1 #225

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