LS,
I'm at a loss and need help.
I have a server (SuSE 7.1) with PostgreSQL 7.0.3 with 2 databases.
The databases are roughly the same size. Basicly the setup is as
follows:
tblNumber: project_id INTEGER
project_name VARCHAR(30)
ddi_number INTEGER
phone_number VARCHAR(30)
tblData: start_call DATE
project_id INTEGER
ddi_number INTEGER
...more fields...
The main index is on tblData on fields (start_call, project_id,
ddi_number). This index is indeed used as EXPLAIN shows.
The query is something like:
SELECT n.project_name,
n.phone_number,
SUM(d.data),
SUM(d.data...)
FROM tblNumber n,
tblData d
WHERE d.start_call BETWEEN i AND j
AND d.project_id BETWEEN k AND l
AND d.project_id = n.project_id
AND d.ddi_number = n.ddi_number
GROUP BY n.project_name, n.phone_number
ORDER BY n.project_name, n.phone_number;
VACUUM ANALYZE is run on both databases daily.
The strange part is that a query on the first database takes 2.5 seconds
and on the second one 3 minutes and 7.1 second!
EXPLAIN looks the same on both databases.
I've attached the output.
The first query is the quick one, the second the slow one.
What can be the cause of this huge difference in time?
Regards,
Cees.
