Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id ED15598F705 for ; Tue, 5 Jun 2012 10:31:38 -0300 (ADT) Received: from blu0-omc4-s26.blu0.hotmail.com ([65.55.111.165]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SbtrC-0007ep-Dw for pgsql-performance@postgresql.org; Tue, 05 Jun 2012 13:31:37 +0000 Received: from BLU161-W57 ([65.55.111.137]) by blu0-omc4-s26.blu0.hotmail.com with Microsoft SMTPSVC(6.0.3790.4675); Tue, 5 Jun 2012 06:31:20 -0700 Message-ID: Content-Type: multipart/alternative; boundary="_7b19d082-8c72-4e28-86da-dfaffbd46a73_" X-Originating-IP: [206.210.99.2] From: Andrew Jaimes To: CC: Postgres Performance Subject: Re: Sequencial scan in a JOIN Date: Tue, 5 Jun 2012 09:31:19 -0400 Importance: Normal In-Reply-To: <4FCE0681.5040306@optionshouse.com> References: , <4FCE0681.5040306@optionshouse.com> MIME-Version: 1.0 X-OriginalArrivalTime: 05 Jun 2012 13:31:20.0017 (UTC) FILETIME=[7DF99C10:01CD431F] X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201206/21 X-Sequence-Number: 47047 --_7b19d082-8c72-4e28-86da-dfaffbd46a73_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable the default_statistics_target is set to 200=2C and I have run the analyze a= nd reindex on these tables before writing the email.=20 Andrew > Date: Tue=2C 5 Jun 2012 08:15:45 -0500 > From: sthomas@optionshouse.com > To: andrewjaimes@hotmail.com > CC: pgsql-performance@postgresql.org > Subject: Re: [PERFORM] Sequencial scan in a JOIN >=20 > On 06/05/2012 07:48 AM=2C Andrew Jaimes wrote: >=20 > > ' -> Hash Join (cost=3D10.93..99795.09 rows=3D242803 width=3D0) (act= ual > > time=3D0.541..2249.027 rows=3D33 loops=3D1)' > > ' Hash Cond: ((a_activity.activequeueid =3D l_userqueue.queueid) > > AND (a_activity.sbuid =3D e_usersessions.sbuid))' > > ' -> Seq Scan on a_activity (cost=3D0.00..88462.52 rows=3D1208= 167 > > width=3D22) (actual time=3D0.010..1662.142 >=20 > I'd be willing to bet your stats are way=2C way off. It expected 242=2C80= 3=20 > rows in the hash=2C but only got 33. In that kind of scenario=2C I could= =20 > easily see the planner choosing a sequence scan over an index scan=2C as= =20 > doing that many index seeks would be much more expensive than scanning=20 > the table. >=20 > What's your default_statistics_target=2C and when is the last time you=20 > analyzed the tables in this query? >=20 > --=20 > Shaun Thomas > OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL=2C 60604 > 312-444-8534 > sthomas@optionshouse.com >=20 > ______________________________________________ >=20 > See http://www.peak6.com/email_disclaimer/ for terms and conditions relat= ed to this email >=20 > --=20 > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org= ) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance = --_7b19d082-8c72-4e28-86da-dfaffbd46a73_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

the default_statistics_target is set to 200=2C and I have run the analyze a= nd reindex on these tables before writing the email.

Andrew

>=3B Date: Tue= =2C 5 Jun 2012 08:15:45 -0500
>=3B From: sthomas@optionshouse.com
&= gt=3B To: andrewjaimes@hotmail.com
>=3B CC: pgsql-performance@postgres= ql.org
>=3B Subject: Re: [PERFORM] Sequencial scan in a JOIN
>=3B=
>=3B On 06/05/2012 07:48 AM=2C Andrew Jaimes wrote:
>=3B
&g= t=3B >=3B ' ->=3B Hash Join (cost=3D10.93..99795.09 rows=3D242803 wi= dth=3D0) (actual
>=3B >=3B time=3D0.541..2249.027 rows=3D33 loops=3D= 1)'
>=3B >=3B ' Hash Cond: ((a_activity.activequeueid =3D l_u= serqueue.queueid)
>=3B >=3B AND (a_activity.sbuid =3D e_usersessions= .sbuid))'
>=3B >=3B ' ->=3B Seq Scan on a_activity (cost= =3D0.00..88462.52 rows=3D1208167
>=3B >=3B width=3D22) (actual time= =3D0.010..1662.142
>=3B
>=3B I'd be willing to bet your stats ar= e way=2C way off. It expected 242=2C803
>=3B rows in the hash=2C but = only got 33. In that kind of scenario=2C I could
>=3B easily see the = planner choosing a sequence scan over an index scan=2C as
>=3B doing = that many index seeks would be much more expensive than scanning
>=3B= the table.
>=3B
>=3B What's your default_statistics_target=2C a= nd when is the last time you
>=3B analyzed the tables in this query?<= br>>=3B
>=3B --
>=3B Shaun Thomas
>=3B OptionsHouse | 14= 1 W. Jackson Blvd. | Suite 500 | Chicago IL=2C 60604
>=3B 312-444-8534=
>=3B sthomas@optionshouse.com
>=3B
>=3B __________________= ____________________________
>=3B
>=3B See http://www.peak6.com/= email_disclaimer/ for terms and conditions related to this email
>=3B =
>=3B --
>=3B Sent via pgsql-performance mailing list (pgsql-per= formance@postgresql.org)
>=3B To make changes to your subscription:>=3B http://www.postgresql.org/mailpref/pgsql-performance

=

= --_7b19d082-8c72-4e28-86da-dfaffbd46a73_--