pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedProblems with queries on small/medium sized tables
3+ messages / 3 participants
[nested] [flat]
* Problems with queries on small/medium sized tables
@ 1998-09-15 18:00 Greg Youngblood <YoungblG@houstoncellular.com>
0 siblings, 1 reply; 3+ messages in thread
From: Greg Youngblood @ 1998-09-15 18:00 UTC (permalink / raw)
To: 'PostgreSQL Novice List' <pgsql-novice@postgresql.org>; +Cc: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [NOVICE] Problems with queries on small/medium sized tables
@ 1998-09-15 21:08 David Hartwig <daybee@bellatlantic.net>
parent: Greg Youngblood <YoungblG@houstoncellular.com>
0 siblings, 0 replies; 3+ messages in thread
From: David Hartwig @ 1998-09-15 21:08 UTC (permalink / raw)
To: Greg Youngblood <YoungblG@houstoncellular.com>; +Cc: 'PostgreSQL Novice List' <pgsql-novice@postgreSQL.org>; pgsql-interfaces
The FAQ covers this in some detail. See:
http://www.insightdist.com/psqlodbc/psqlodbc_faq.html#canon
Actually as I read the FAQ today I realize how little we knew at the time of
its writing. This is actually a manifestation of the MS Jet Engine which
breaks queries into key sets. The Jet is used in many MS products and data
widgets. I servrves the useful purpose of allowing large data sets to be
browsed without having to consume the entire queries or tables.
The actual failure is in the server. The 6.4 version of the server resolves
this problem quite effectively. It is in beta now and will be released in mid
Oct.
In the short term you can disable the Jet for any query in the query
properties. To browse a multi-part key table you will have to turn off auto
detection of the key in on of several ways. I will address this later if you
need. Time grows short tonight.
Greg Youngblood wrote:
> 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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [INTERFACES] Problems with queries on small/medium sized tables
@ 1998-09-16 07:03 Sbragion Denis <infotecn@tin.it>
0 siblings, 0 replies; 3+ messages in thread
From: Sbragion Denis @ 1998-09-16 07:03 UTC (permalink / raw)
To: Greg Youngblood <YoungblG@houstoncellular.com>; +Cc: pgsql-interfaces
Hello,
At 13.00 15/09/98 -0500, you wrote:
>I am having a problem with queries.
>
...
>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.
the jet database engine often parses and rewrites queries to get simpler
ones. Sometimes these simpler queries contain many "OR" in the where clause
and the backend optimizer crashes on them. Use the trace feature of the
ODBC to see exactly which kind of queries are sent to the backend. To solve
it you have to use passthrough queries, but I have no idea how this can be
done in Access.
Hope it helps.
Bye!
Dr. Sbragion Denis
InfoTecna
Tel, Fax: +39 39 2324054
URL: http://space.tin.it/internet/dsbragio
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~1998-09-16 07:03 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-09-15 18:00 Problems with queries on small/medium sized tables Greg Youngblood <YoungblG@houstoncellular.com>
1998-09-15 21:08 ` David Hartwig <daybee@bellatlantic.net>
1998-09-16 07:03 Re: [INTERFACES] Problems with queries on small/medium sized tables Sbragion Denis <infotecn@tin.it>
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