| From: | "Owen Jacobson" <ojacobson(at)osl(dot)com> |
|---|---|
| To: | "Kashmira Patel \(kupatel\)" <kupatel(at)cisco(dot)com>, "Andrew Sullivan" <ajs(at)crankycanuck(dot)ca>, <pgsql-sql(at)postgresql(dot)org> |
| Subject: | Re: Question about index scan vs seq scan when using count() |
| Date: | 2006-02-23 23:08:20 |
| Message-ID: | 144D12D7DD4EC04F99241498BB4EEDCC234043@nelson.osl.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-sql |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Daniel Joo | 2006-02-24 01:45:03 | how to add primary key to existing table with multiple primary keys |
| Previous Message | Kashmira Patel (kupatel) | 2006-02-23 22:55:59 | Re: Question about index scan vs seq scan when using count() |