From: Owen Jacobson <ojacobson@osl.com>
To: Kashmira Patel \(kupatel\) <kupatel@cisco.com>
To: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
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> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ojacobson@osl.com, kupatel@cisco.com, ajs@crankycanuck.ca
Subject: Re: Question about index scan vs seq scan when using count()
In-Reply-To: <144D12D7DD4EC04F99241498BB4EEDCC234043@nelson.osl.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox