From: Dennis Gearon <gearond@cvc.net>
To: Dann Corbit <DCorbit@connx.com>
Cc: Denis <denis@next2me.com>
Cc: pgsql-performance@postgresql.org
Cc: pgsql-general@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: Yet Another (Simple) Case of Index not used
Date: Tue, 08 Apr 2003 13:43:56 -0700
Message-ID: <3E93348C.20208@cvc.net> (raw)
In-Reply-To: <D90A5A6C612A39408103E6ECDD77B829408AC5@voyager.corporate.connx.com>
References: <D90A5A6C612A39408103E6ECDD77B829408AC5@voyager.corporate.connx.com>
from mysql manual:
-------------------------------------------------------------
"COUNT(*) is optimized to return very quickly if the SELECT retrieves from one
table, no other columns are retrieved, and there is no WHERE clause. For example:
mysql> select COUNT(*) from student;"
-------------------------------------------------------------
A nice little optimization, maybe not possible in a MVCC system.
Dann Corbit wrote:
>>-----Original Message-----
>>From: Denis [mailto:denis@next2me.com]
>>Sent: Tuesday, April 08, 2003 12:57 PM
>>To: pgsql-performance@postgresql.org;
>>pgsql-general@postgresql.org; pgsql-sql@postgresql.org
>>Subject: [GENERAL] Yet Another (Simple) Case of Index not used
>>
>>
>>Hi there,
>>I'm running into a quite puzzling simple example where the
>>index I've created on a fairly big table (465K entries) is
>>not used, against all common sense expectations: The query I
>>am trying to do (fast) is:
>>
>>select count(*) from addresses;
>>
>>This takes more than a second to complete, because, as the
>>'explain' command shows me, the index created on 'addresses'
>>is not used, and a seq scan is being used.
>
>
> As well it should be.
>
>
>>One would assume
>>that the creation of an index would allow the counting of the
>>number of entries in a table to be instantanous?
>
>
> Traversing the index to perform the count will definitely make the query
> many times slower.
>
> A general rule of thumb (not sure if it is true with PostgreSQL) is that
> if you have to traverse more than 10% of the data with an index then a
> full table scan will be faster. This is especially true when there is
> highly redundant data in the index fields. If there were an index on
> bit data type, and you have half and half 1 and 0, an index scan of the
> table will be disastrous.
>
> To simply scan the table, we will just sequentially read pages until the
> data is exhausted. If we follow the index, we will randomly jump from
> page to page, defeating the read buffering.
> [snip]
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
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: gearond@cvc.net, DCorbit@connx.com, denis@next2me.com, pgsql-performance@postgresql.org, pgsql-general@postgresql.org
Subject: Re: Yet Another (Simple) Case of Index not used
In-Reply-To: <3E93348C.20208@cvc.net>
* 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