Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 87487650276
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 16 Jul 2008 18:38:33 -0300 (ADT)
Received: from postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 99868-09 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 16 Jul 2008 18:38:26 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from relais.videotron.ca (relais.videotron.ca [24.201.245.36])
	by postgresql.org (Postfix) with ESMTP id 285DD65026E
	for <pgsql-performance@postgresql.org>;
	Wed, 16 Jul 2008 18:38:30 -0300 (ADT)
MIME-version: 1.0
Content-type: multipart/alternative;
	boundary="Boundary_(ID_I/lK3c7WQwYCqmW4vH8XDQ)"
Received: from [10.10.20.36] ([216.94.157.147]) by VL-MH-MR001.ip.videotron.ca
	(Sun Java(tm) System Messaging Server 6.3-4.01 (built Aug  3 2007;
	32bit))
	with ESMTPA id <0K4400734C3A0ZE1@VL-MH-MR001.ip.videotron.ca> for
	pgsql-performance@postgresql.org; Wed, 16 Jul 2008 17:38:01 -0400 (EDT)
Message-id: <487E6A36.3010100@videotron.ca>
Date: Wed, 16 Jul 2008 17:37:58 -0400
From: Patrick Vachon <pvachon.videotron@videotron.ca>
User-Agent: Thunderbird 2.0.0.14 (Windows/20080421)
To: pgsql-performance@postgresql.org
Subject: Difference between 8.1 & 8.3
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=AWL=-0.001,
	HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200807/136
X-Sequence-Number: 30736

This is a multi-part message in MIME format.

--Boundary_(ID_I/lK3c7WQwYCqmW4vH8XDQ)
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7BIT

Hi guys,

I've got a query that is running slower on 8.3 than on 8.1 (with 
equivalent server config),
because the join ordering is not the same, at least that's my guess... ;-)

In 8.1.4, table A had 122880 pages, B 112690 pages and C 80600 pages.
Now in 8.3.3, table A has only 77560 pages, B 69580 but C remains at 
80600 pages.

In 8.1 the tables were joined in that way (using explain analyse):
C join A join B
now in 8.3:
B join A join C
Beside that, the plan is very similar, but the indexes used are not the 
same.

Could the number of disk pages of a table influence the
order in which it is joined, even when it is scanned with an index?

I'm pretty sure it is because of the reduced table sizes,
since the server configuration is the same.

Thoughts?

Thanks,
Patrick


--Boundary_(ID_I/lK3c7WQwYCqmW4vH8XDQ)
Content-type: text/html; charset=ISO-8859-1
Content-transfer-encoding: 7BIT

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body bgcolor="#ffffff" text="#000000">
<div class="moz-text-flowed"
 style="font-family: -moz-fixed; font-size: 13px;" lang="x-western">Hi
guys,
<br>
<br>
I've got a query that is running slower on 8.3 than on 8.1 (with
equivalent server config),
<br>
because the join ordering is not the same, at least that's my guess...
;-)
<br>
<br>
In 8.1.4, table A had 122880 pages, B 112690 pages and C 80600 pages.
<br>
Now in 8.3.3, table A has only 77560 pages, B 69580 but C remains at
80600 pages.
<br>
<br>
In 8.1 the tables were joined in that way (using explain analyse):
<br>
C join A join B
<br>
now in 8.3:
<br>
B join A join C
<br>
Beside that, the plan is very similar, but the indexes used are not the
same.
<br>
<br>
Could the number of disk pages of a table influence the
<br>
order in which it is joined, even when it is scanned with an index?
<br>
<br>
I'm pretty sure it is because of the reduced table sizes,
<br>
since the server configuration is the same.
<br>
<br>
Thoughts?
<br>
<br>
Thanks,
<br>
Patrick
<br>
<br>
</div>
</body>
</html>

--Boundary_(ID_I/lK3c7WQwYCqmW4vH8XDQ)--