X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id E03DE9DCA8D for ; Tue, 10 Jan 2006 11:32:15 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 27249-01-6 for ; Tue, 10 Jan 2006 11:32:18 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from rambo.iniquinet.com (rambo.iniquinet.com [69.39.89.10]) by postgresql.org (Postfix) with ESMTP id 97BE99DC87E for ; Tue, 10 Jan 2006 11:17:04 -0400 (AST) Received: (qmail 15429 invoked by uid 1010); 10 Jan 2006 10:17:08 -0500 Received: from Robert_Creager@LogicalChaos.org by rambo.iniquinet.com by uid 1002 with qmail-scanner-1.20 (spamassassin: 3.1.0. Clear:RC:0(63.147.78.66):SA:0(?/?):. Processed in 0.903932 secs); 10 Jan 2006 15:17:08 -0000 Received: from unknown (HELO thunder.logicalchaos.org) (perl?test@logicalchaos.org@63.147.78.66) by rambo.iniquinet.com with AES256-SHA encrypted SMTP; 10 Jan 2006 10:17:07 -0500 Received: from logicalchaos.org (localhost [127.0.0.1]) by thunder.logicalchaos.org (Postfix) with ESMTP id 202C0126054; Tue, 10 Jan 2006 08:17:06 -0700 (MST) Date: Tue, 10 Jan 2006 08:17:05 -0700 From: Robert Creager To: Michael Fuhr Cc: PGPerformance Subject: Re: Index isn't used during a join. Message-ID: <20060110081705.221792f9@thunder.logicalchaos.org> In-Reply-To: <20060110055818.GA55076@winnie.fuhr.org> References: <20060109212338.6420af12@thunder.logicalchaos.org> <20060110055818.GA55076@winnie.fuhr.org> Organization: Starlight Vision, LLC. X-Mailer: Sylpheed-Claws 1.0.4 (GTK+ 1.2.10; i586-mandriva-linux-gnu) Mime-Version: 1.0 Content-Type: multipart/signed; boundary=Signature_Tue__10_Jan_2006_08_17_05_-0700_h4.0YeYibKxKw2n8; protocol="application/pgp-signature"; micalg=pgp-sha1 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200601/88 X-Sequence-Number: 16566 --Signature_Tue__10_Jan_2006_08_17_05_-0700_h4.0YeYibKxKw2n8 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable When grilled further on (Mon, 9 Jan 2006 22:58:18 -0700), Michael Fuhr confessed: > On Mon, Jan 09, 2006 at 09:23:38PM -0700, Robert Creager wrote: > > I'm working with a query to get more info out with a join. The base > > query works great speed wise because of index usage. When the join is > > tossed in, the index is no longer used, so the query performance tanks. >=20 > The first query you posted returns 285 rows and the second returns > over one million; index usage aside, that difference surely accounts > for a performance penalty. And as is often pointed out, index scans > aren't always faster than sequential scans: the more of a table a > query has to fetch, the more likely a sequential scan will be faster. Thanks for pointing out the obvious that I missed. Too much data in the se= cond query. It's supposed to match (row wise) what was returned from the f= irst query. Just ignore me for now... Thanks, Rob --=20 08:15:24 up 3 days, 42 min, 9 users, load average: 2.07, 2.20, 2.25 Linux 2.6.12-12-2 #4 SMP Tue Jan 3 19:56:19 MST 2006 --Signature_Tue__10_Jan_2006_08_17_05_-0700_h4.0YeYibKxKw2n8 Content-Type: application/pgp-signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2 (GNU/Linux) iEYEARECAAYFAkPDz/EACgkQLQ/DKuwDYzkAigCbBvC3b0FyrhF8bSq4ESimD4md QSIAnR/IXEhKexDSJsD7SxwGDiyKsDdA =BoOU -----END PGP SIGNATURE----- --Signature_Tue__10_Jan_2006_08_17_05_-0700_h4.0YeYibKxKw2n8--