"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