X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 37B309DC819 for ; Thu, 23 Feb 2006 19:08:23 -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 90835-01 for ; Thu, 23 Feb 2006 19:08:25 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from nelson.osl.com (216-21-133-120.ip.van.radiant.net [216.21.133.120]) by postgresql.org (Postfix) with ESMTP id F2E6C9DC849 for ; Thu, 23 Feb 2006 19:08:19 -0400 (AST) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Subject: Re: Question about index scan vs seq scan when using count() Date: Thu, 23 Feb 2006 15:08:20 -0800 Message-ID: <144D12D7DD4EC04F99241498BB4EEDCC234043@nelson.osl.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [SQL] Question about index scan vs seq scan when using count() Thread-Index: AcY4xkrjGDGLLK/nQMiA3owHs6CdCwAAFNjwAAGyPFA= From: "Owen Jacobson" To: "Kashmira Patel \(kupatel\)" , "Andrew Sullivan" , X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.129 required=5 tests=[AWL=0.129] X-Spam-Score: 0.129 X-Spam-Level: X-Archive-Number: 200602/291 X-Sequence-Number: 24301 Kashmira Patel wrote: > I did do an EXPLAIN ANALYZE as well, it also showed a > sequential scan. The table has about 600+ rows, with around 6 of them > matching the given id. Wouldn't an index scan be faster in this case? Not necessarily. It's entirely possible, if your rows are small, that = 600 rows will fit on a single disk page. The index will be stored on = a(t least one) separate disk page. The cost of loading a page from disk = pretty much swamps the cost of processing rows on a page, so in general = the server tries to minimize the number of pages used. To use an index = for a one-page table, it'd have to load two pages (the table and the = index); to do a sequential scan over a one-page table it only has to = load the table. Indexes are useful because they allow the DB to reduce the total number = of pages loaded to complete a query. -Owen