Re: Preformance

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
>