Explain's don't look the same to me. The first explain indicates use of
HASH JOIN, whereas the second is using NESTED LOOP.
I'm guessing some of the other indexes on the two tables are not the same
between the two databases. One of these other indexes is being used in the
first explain, but not the second.
Frank
At 05:56 PM 2/2/02 +0100, Cees van de Griend wrote:
>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.
>
>Attachment Converted: "m:\BELL_HSE\ATTACH\t"
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly
>