From: | Brent Wood <b(dot)wood(at)niwa(dot)co(dot)nz> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | Denis <denis(at)next2me(dot)com> |
Subject: | Re: Yet Another (Simple) Case of Index not used |
Date: | 2003-04-09 00:44:48 |
Message-ID: | 20030409123856.Q34167-100000@storm.niwa.co.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-performance pgsql-sql |
On Wed, 9 Apr 2003, Martijn van Oosterhout wrote:
> On Tue, Apr 08, 2003 at 12:57:16PM -0700, Denis wrote:
> > 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.
> > One would assume that the creation of an index would allow the counting of
> > the number of entries in a table to be instantanous?
>
> Incorrect assumption. select count(*) can produce different results in
> different backends depending on the current state of the active
> transactions.
Some thoughts:
Select count(*) is often applied to views, and may take some time
depending on the underlying query.
However, for a single table, I would have thought that if there are no
write locks or open transactions for the table, the index would return a
faster result than a scan? Is there room for some optimisation here?
Does count(<primary_key>) work faster, poss using the unique index on the
key (for non-composite keys)?
Cheers
Brent Wood
From | Date | Subject | |
---|---|---|---|
Next Message | Neil Conway | 2003-04-09 01:08:04 | Re: How does PostgreSQL treat null values in unique |
Previous Message | Jim C. Nasby | 2003-04-09 00:31:29 | Re: How does PostgreSQL treat null values in unique composite |
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2003-04-09 01:59:37 | Re: [SQL] Yet Another (Simple) Case of Index not used |
Previous Message | Denis @ Next2Me | 2003-04-09 00:21:16 | Re: [SQL] Yet Another (Simple) Case of Index not used |
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2003-04-09 01:59:37 | Re: [SQL] Yet Another (Simple) Case of Index not used |
Previous Message | Denis @ Next2Me | 2003-04-09 00:21:16 | Re: [SQL] Yet Another (Simple) Case of Index not used |