Skip site navigation (1) Skip section navigation (2)

Problems with queries on small/medium sized tables

From: Greg Youngblood <YoungblG(at)houstoncellular(dot)com>
To: "'PostgreSQL Novice List'" <pgsql-novice(at)postgresql(dot)org>
Cc: "'PostgreSQL Interfaces List'" <pgsql-interfaces(at)postgresql(dot)org>
Subject: Problems with queries on small/medium sized tables
Date: 1998-09-15 18:00:13
Message-ID: 2B9713A99045D211BB4E0008C75668690B0FFE@SCORPEXC1.houstoncell.com (view raw or flat)
Thread:
Lists: pgsql-interfaces
I am having a problem with queries.

First, my setup. I have PostgreSQL 6.3.2 running on a dual PPro200 box with
SuSE 5.2, Linux-SMP 2.0.35. The box has 64 meg RAM and 130 meg swap. I have
a database that has three tables and several indexes. The two biggest tables
have 331,200 rows of information. One of those has 5 columns, the other has
13 columns.

I connect to the database via ODBC on a Windows NT 4 SP3 workstation, with
Access 97. I am using the Insight Distribution Systems PostgreSQL ODBC
driver version 6.30.0249.

I can add data to the tables without any problems, however my queries seem
to fail miserably, with ODBC call failed.

Here's an example. I have the following columns
YYYYMMDD	HHMM		SYSTEM	USER		USAGE

where YYYYMMDD,HHMM,USAGE is int4, SYSTEM is char(16), and USER is char(32).
YYYYMMDD, HHMM, SYSTEM, USER are all indexed individually, plus they are all
used to create a unique key. 

The following query is used:
	SELECT usage.*
	FROM usage
	WHERE 
	(((usage.yyyymmdd) Between 19980701 And 19980731) 
	AND ((usage.link)='swbt08')) OR (((usage.link)='swbtja')) 
	ORDER BY
	usage.yyyymmdd, usage.hhmm, usage.stp, usage.link;

When run from psql the answer is returned fairly quickly. When run from
Access via ODBC, the system memory starts increasing until it gets to 60/70%
on the single postgres process, and CPU is usually below 10% (normal queries
shoot CPU up to 50 to 99 % and memory stays around 3 to 8 percent). Then the
ODBC call fails and Access doesn't get any information.

Also, using Access, when I have the database open, and the ables linked into
my Access database, if I open the actual table itself, the same thing
happens. The server sucks down memory to PostgreSQL until the ODBC call
fails.

Anyone have any ideas on what may be causing this? Or ways to fix this?

Thanks
Greg Youngblood

Gregory S. Youngblood
ext 2164
 
 


Responses

pgsql-interfaces by date

Next:From: David HartwigDate: 1998-09-15 21:08:55
Subject: Re: [NOVICE] Problems with queries on small/medium sized tables
Previous:From: Mikael HammarinDate: 1998-09-15 10:48:49
Subject: bug

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group