X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9426D32A684 for ; Tue, 12 Oct 2004 01:21:21 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 19534-01 for ; Tue, 12 Oct 2004 00:21:16 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id 20FD232A2C3 for ; Tue, 12 Oct 2004 01:21:17 +0100 (BST) Received: from server11.icaen.uiowa.edu (server11.icaen.uiowa.edu [128.255.17.51]) by server07.icaen.uiowa.edu (8.12.9/8.12.9) with ESMTP id i9C0LBSp009554; sent by ; Mon, 11 Oct 2004 19:21:15 -0500 (CDT) Received: from [192.168.1.11] (12-217-241-0.client.mchsi.com [12.217.241.0]) by server11.icaen.uiowa.edu (8.12.9/smtp-service-1.6) with ESMTP id i9C0Cj4p002504; (envelope-from ) Mon, 11 Oct 2004 19:12:45 -0500 (CDT) Message-ID: <416B217D.9090000@johnmeinel.com> Date: Mon, 11 Oct 2004 19:12:45 -0500 From: John Meinel User-Agent: Mozilla Thunderbird 0.8 (Windows/20040913) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Francisco Reyes Cc: pgsql-performance@postgresql.org Subject: Re: why my query is not using index?? References: <41639F3C.4050103@siche.net> <200410111425.02671.vygen@gmx.de> <20041011163756.M97379@zoraida.natserv.net> In-Reply-To: <20041011163756.M97379@zoraida.natserv.net> X-Enigmail-Version: 0.86.0.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig020F595836BEAD80916D9EEE" X-Virus-Scanned: clamd / ClamAV version 0.75.1, clamav-milter version 0.66n X-Virus-Scanned: clamd / ClamAV version 0.75.1, clamav-milter version 0.75 on clamav.icaen.uiowa.edu X-Virus-Status: Clean X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/147 X-Sequence-Number: 8623 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig020F595836BEAD80916D9EEE Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Francisco Reyes wrote: > On Mon, 11 Oct 2004, Janning Vygen wrote: > [...] > When I saw the default explain I was surprised to see that indexes were > not been used. For example the join on lines 4,5 are exactly the primary > key of the tables yet a sequential scan was used. > Note this: > The default explain was: > > Sort (cost=382.01..382.15 rows=56 width=196) > Sort Key: accounts.account_group, accounts.account_name, [...] Versus this: > > After set enable_seqscan to off; > It becomes > > Sort (cost=490.82..490.96 rows=56 width=196) > Sort Key: accounts.account_group, accounts.account_name, [...] Postgres believes that it will cost 382 to do a sequential scan, versus 490 for an indexed scan. Hence why it prefers to do the sequential scan. Try running explain analyze to see if how accurate it is. As Janning mentioned, sometimes sequential scans *are* faster. If the number of entries that will be found is large compared to the number of total entries (I don't know the percentages, but probably >30-40%), then it is faster to just load the data and scan through it, rather than doing a whole bunch of indexed lookups. John =:-> --------------enig020F595836BEAD80916D9EEE Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (Cygwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBayF9JdeBCYSNAAMRAhXHAJ9Oe5UXhr7l4rMCNpQU88A1ePzZmACdH7td cedOaVSPnnV6RVTC4dpe8fo= =J1/L -----END PGP SIGNATURE----- --------------enig020F595836BEAD80916D9EEE--