From: Franz J Fortuny <ffortuny@ivsol.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgSQL <pgsql-sql@postgresql.org>
Subject: RE: Speed or configuration
Date: Sun, 20 Aug 2000 17:09:28 -0500
Message-ID: <002f01c00af3$4efce0a0$0b01a8c0@ivsol> (raw)
References: <010501c00ac0$72d47a20$0b01a8c0@ivsol>
<10681.966794490@sss.pgh.pa.us>
"What version of Postgres are you using, and what does
EXPLAIN show
as the query plan for this query? How many tableY rows
is the sub-
query likely to produce, and how many matches do you
expect to get
from tableX?"
Version: postgresql-7.0.2-2.i386.rpm
Explain: Scan table, scan table. (Plus the costs, etc.)
About 9,000 matches from tableX are expected, and a lot
less from
tableY (about 2000, at the most).
The index structure of tableY includes 5 fields. Most of
the time, we need only the first and second fields. Less
often, up to the third field and a lot less often the
other 2 left fields (in the "where" clauses).
However, field1 of tableX references field1 of tableY.
If I use a program to get the results, they come out
incredibly fast (faster in postgreSQL than with the
commercial program)
(A select to get a first record set from tableY, then
navigate the rows, executing a select for tableX for
each row...)
These the structures of the tables:
create table tableX
(
col0 integer not null primary key,
col1 integer not null,
col2 integer not null,
col3 char(20),
col4 char(8),
col5 char(8),
unique(col1,col2,col3,col4,col5)
);
create table tableY
(
col0 integer not null references tableX(col0),
col1 integer,
col2 integer,
col3 float,
col4 float
);
The slow query is:
select col1,col2,sum(col3),sum(col4)
from tableY
where col0 in
(select col0 from tableX where col1=:col1 and
col2=:col2)
group by col1,col2;
Explain plan shows ALWAYS scan table, scan table, scan
table.
tableY contains about 900,000 rows, and tableX about
65,000.
Any more light about the slowness?
Franz J Fortuny
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: ffortuny@ivsol.com, tgl@sss.pgh.pa.us
Subject: Re: Speed or configuration
In-Reply-To: <002f01c00af3$4efce0a0$0b01a8c0@ivsol>
References: <010501c00ac0$72d47a20$0b01a8c0@ivsol> <10681.966794490@sss.pgh.pa.us> <002f01c00af3$4efce0a0$0b01a8c0@ivsol>
* 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