"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
