From pvachon.videotron@videotron.ca Wed Jul  16 21:38:33 2008
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)--



From Gregory.Williamson@digitalglobe.com Wed Jul  16 22:02:51 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 19D9665026E
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 16 Jul 2008 19:02:51 -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 24292-06 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 16 Jul 2008 19:02:44 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from copgpuniv.digitalglobe.com (ew-fw-out.digitalglobe.com
	[205.166.175.2])
	by postgresql.org (Postfix) with ESMTP id 835C06501CB
	for <pgsql-performance@postgresql.org>;
	Wed, 16 Jul 2008 19:02:47 -0300 (ADT)
Received: from copgpuniv.digitalglobe.com (localhost.localdomain [127.0.0.1])
	by copgpuniv.digitalglobe.com (PGP Universal) with ESMTP
	id 8FEAB2A03A6; Wed, 16 Jul 2008 16:02:45 -0600 (MDT)
Received: from comailgate2.digitalglobe.com ([10.10.42.200])
	by copgpuniv.digitalglobe.com (PGP Universal service);
	Wed, 16 Jul 2008 16:02:45 -0600
X-PGP-Universal: processed;
	by copgpuniv.digitalglobe.com on Wed, 16 Jul 2008 16:02:45 -0600
Received: from COMAIL01.digitalglobe.com ([10.10.42.60]) by
	comailgate2.digitalglobe.com with Microsoft SMTPSVC(6.0.3790.1830);
	Wed, 16 Jul 2008 16:02:45 -0600
X-MimeOLE: Produced By Microsoft Exchange V6.5
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----_=_NextPart_001_01C8E78F.AC485292"
Subject: Re: Difference between 8.1 & 8.3
Date: Wed, 16 Jul 2008 15:59:42 -0600
Message-ID: 
 <8B319E5A30FF4A48BE7EEAAF609DB233021F3037@COMAIL01.digitalglobe.com>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: [PERFORM] Difference between 8.1 & 8.3
Thread-Index: AcjnjIJULgEEoH4nRRChQVeKgSy+2QAAr4uw
References: <487E6A36.3010100@videotron.ca>
From: "Gregory Williamson" <Gregory.Williamson@digitalglobe.com>
To: "Patrick Vachon" <pvachon.videotron@videotron.ca>,
	<pgsql-performance@postgresql.org>
X-OriginalArrivalTime: 16 Jul 2008 22:02:45.0368 (UTC)
	FILETIME=[AD499780:01C8E78F]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200807/137
X-Sequence-Number: 30737

This is a multi-part message in MIME format.

------_=_NextPart_001_01C8E78F.AC485292
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Patrick Vachon wrote:

> Hi guys,
>=20
> I've got a query that is running slower on 8.3 than on 8.1 (with=20
> equivalent server config),
> because the join ordering is not the same, at least that's my guess... =
;-)
>=20
> 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=20
> 80600 pages.
>=20
> 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=20
> same.
>=20
> 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?
>=20
> I'm pretty sure it is because of the reduced table sizes,
> since the server configuration is the same.
>=20
> Thoughts?

8.3 has fewer automatic casts to text types; perhaps you have indexes =
which are not being used because of mismatched types ? Perhaps an =
EXPLAIN ANALYZE from both, if possible, would clairfy.

HTH,

Greg Williamson
Senior DBA
DigitalGlobe

Confidentiality Notice: This e-mail message, including any attachments, =
is for the sole use of the intended recipient(s) and may contain =
confidential and privileged information and must be protected in =
accordance with those provisions. Any unauthorized review, use, =
disclosure or distribution is prohibited. If you are not the intended =
recipient, please contact the sender by reply e-mail and destroy all =
copies of the original message.

(My corporate masters made me say this.)

------_=_NextPart_001_01C8E78F.AC485292
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
6.5.7652.24">
<TITLE>RE: [PERFORM] Difference between 8.1 &amp; 8.3</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->

<P><FONT SIZE=3D2>Patrick Vachon wrote:<BR>
<BR>
&gt; Hi guys,<BR>
&gt;<BR>
&gt; I've got a query that is running slower on 8.3 than on 8.1 =
(with<BR>
&gt; equivalent server config),<BR>
&gt; because the join ordering is not the same, at least that's my =
guess... ;-)<BR>
&gt;<BR>
&gt; In 8.1.4, table A had 122880 pages, B 112690 pages and C 80600 =
pages.<BR>
&gt; Now in 8.3.3, table A has only 77560 pages, B 69580 but C remains =
at<BR>
&gt; 80600 pages.<BR>
&gt;<BR>
&gt; In 8.1 the tables were joined in that way (using explain =
analyse):<BR>
&gt; C join A join B<BR>
&gt; now in 8.3:<BR>
&gt; B join A join C<BR>
&gt; Beside that, the plan is very similar, but the indexes used are not =
the<BR>
&gt; same.<BR>
&gt;<BR>
&gt; Could the number of disk pages of a table influence the<BR>
&gt; order in which it is joined, even when it is scanned with an =
index?<BR>
&gt;<BR>
&gt; I'm pretty sure it is because of the reduced table sizes,<BR>
&gt; since the server configuration is the same.<BR>
&gt;<BR>
&gt; Thoughts?<BR>
<BR>
8.3 has fewer automatic casts to text types; perhaps you have indexes =
which are not being used because of mismatched types ? Perhaps an =
EXPLAIN ANALYZE from both, if possible, would clairfy.<BR>
<BR>
HTH,<BR>
<BR>
Greg Williamson<BR>
Senior DBA<BR>
DigitalGlobe<BR>
<BR>
Confidentiality Notice: This e-mail message, including any attachments, =
is for the sole use of the intended recipient(s) and may contain =
confidential and privileged information and must be protected in =
accordance with those provisions. Any unauthorized review, use, =
disclosure or distribution is prohibited. If you are not the intended =
recipient, please contact the sender by reply e-mail and destroy all =
copies of the original message.<BR>
<BR>
(My corporate masters made me say this.)<BR>
</FONT>
</P>

</BODY>
</HTML>
------_=_NextPart_001_01C8E78F.AC485292--



From pvachon.videotron@videotron.ca Tue Jul  22 16:34:44 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 6480B65025A
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 22 Jul 2008 13:34:44 -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 41756-02-8
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 22 Jul 2008 13:34:18 -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 C6B2165036A
	for <pgsql-performance@postgresql.org>;
	Tue, 22 Jul 2008 13:19:58 -0300 (ADT)
MIME-version: 1.0
Content-type: multipart/alternative;
	boundary="Boundary_(ID_gtKy/8RjhNyPZVQ30wUwjw)"
Received: from [10.10.20.42] ([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 <0K4E002S6YDN4250@VL-MH-MR001.ip.videotron.ca> for
	pgsql-performance@postgresql.org; Tue, 22 Jul 2008 11:15:29 -0400 (EDT)
Message-id: <4885F993.6020803@videotron.ca>
Date: Tue, 22 Jul 2008 11:15:31 -0400
From: Patrick Vachon <pvachon.videotron@videotron.ca>
User-Agent: Thunderbird 2.0.0.14 (Windows/20080421)
To: Gregory Williamson <Gregory.Williamson@digitalglobe.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Difference between 8.1 & 8.3
References: <487E6A36.3010100@videotron.ca>
	<8B319E5A30FF4A48BE7EEAAF609DB233021F3037@COMAIL01.digitalglobe.com>
In-reply-to: 
 <8B319E5A30FF4A48BE7EEAAF609DB233021F3037@COMAIL01.digitalglobe.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200807/206
X-Sequence-Number: 30806

This is a multi-part message in MIME format.

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

Hi,

Finally found the problem.
Turning off nested loops gave me much better performance on 8.3 than 8.1.

The problem seems to come from postgresql miscalculation of the number 
of rows returned by nested loops.
It is well described in that thread:
http://archives.postgresql.org/pgsql-performance/2008-03/msg00371.php

In my case, it was by a factor of 20000.

Of course, I can't turn off nested loop in my database,
it will impact performance on small tables too much...
So there is no easy fix for that, it seems,
beside playing with per-column statistics-gathering target maybe?

Patrick

Gregory Williamson wrote:
>
> Patrick Vachon wrote:
>
> > 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?
>
> 8.3 has fewer automatic casts to text types; perhaps you have indexes 
> which are not being used because of mismatched types ? Perhaps an 
> EXPLAIN ANALYZE from both, if possible, would clairfy.
>
> HTH,
>
> Greg Williamson
> Senior DBA
> DigitalGlobe
>
> Confidentiality Notice: This e-mail message, including any 
> attachments, is for the sole use of the intended recipient(s) and may 
> contain confidential and privileged information and must be protected 
> in accordance with those provisions. Any unauthorized review, use, 
> disclosure or distribution is prohibited. If you are not the intended 
> recipient, please contact the sender by reply e-mail and destroy all 
> copies of the original message.
>
> (My corporate masters made me say this.)
>
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG. 
> Version: 7.5.524 / Virus Database: 270.5.0/1558 - Release Date: 7/17/2008 9:56 AM
>   

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

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hi,<br>
<br>
Finally found the problem.<br>
Turning off nested loops gave me much better performance on 8.3 than
8.1.<br>
<br>
The problem seems to come from postgresql miscalculation of the number
of rows returned by nested loops.<br>
It is well described in that thread:<br>
<a class="moz-txt-link-freetext"
 href="http://archives.postgresql.org/pgsql-performance/2008-03/msg00371.php">http://archives.postgresql.org/pgsql-performance/2008-03/msg00371.php</a><br>
<br>
In my case, it was by a factor of 20000.<br>
<br>
Of course, I can't turn off nested loop in my database,<br>
it will impact performance on small tables too much...<br>
So there is no easy fix for that, it seems,<br>
beside playing with per-column statistics-gathering target maybe?<br>
<br>
Patrick<br>
<br>
Gregory Williamson wrote:
<blockquote
 cite="mid:8B319E5A30FF4A48BE7EEAAF609DB233021F3037@COMAIL01.digitalglobe.com"
 type="cite">
  <meta http-equiv="Content-Type" content="text/html; ">
  <meta name="Generator"
 content="MS Exchange Server version 6.5.7652.24">
  <title>RE: [PERFORM] Difference between 8.1 &amp; 8.3</title>
<!-- Converted from text/plain format -->
  <p><font size="2">Patrick Vachon wrote:<br>
  <br>
&gt; Hi guys,<br>
&gt;<br>
&gt; I've got a query that is running slower on 8.3 than on 8.1 (with<br>
&gt; equivalent server config),<br>
&gt; because the join ordering is not the same, at least that's my
guess... ;-)<br>
&gt;<br>
&gt; In 8.1.4, table A had 122880 pages, B 112690 pages and C 80600
pages.<br>
&gt; Now in 8.3.3, table A has only 77560 pages, B 69580 but C remains
at<br>
&gt; 80600 pages.<br>
&gt;<br>
&gt; In 8.1 the tables were joined in that way (using explain analyse):<br>
&gt; C join A join B<br>
&gt; now in 8.3:<br>
&gt; B join A join C<br>
&gt; Beside that, the plan is very similar, but the indexes used are
not the<br>
&gt; same.<br>
&gt;<br>
&gt; Could the number of disk pages of a table influence the<br>
&gt; order in which it is joined, even when it is scanned with an index?<br>
&gt;<br>
&gt; I'm pretty sure it is because of the reduced table sizes,<br>
&gt; since the server configuration is the same.<br>
&gt;<br>
&gt; Thoughts?<br>
  <br>
8.3 has fewer automatic casts to text types; perhaps you have indexes
which are not being used because of mismatched types ? Perhaps an
EXPLAIN ANALYZE from both, if possible, would clairfy.<br>
  <br>
HTH,<br>
  <br>
Greg Williamson<br>
Senior DBA<br>
DigitalGlobe<br>
  <br>
Confidentiality Notice: This e-mail message, including any attachments,
is for the sole use of the intended recipient(s) and may contain
confidential and privileged information and must be protected in
accordance with those provisions. Any unauthorized review, use,
disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply e-mail and destroy all
copies of the original message.<br>
  <br>
(My corporate masters made me say this.)<br>
  </font>
  </p>
  <pre wrap="">
<hr size="4" width="90%">
No virus found in this incoming message.
Checked by AVG. 
Version: 7.5.524 / Virus Database: 270.5.0/1558 - Release Date: 7/17/2008 9:56 AM
  </pre>
</blockquote>
</body>
</html>

--Boundary_(ID_gtKy/8RjhNyPZVQ30wUwjw)--