From: | John R Pierce <pierce(at)hogranch(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Why Select Count(*) from table - took over 20 minutes? |
Date: | 2010-10-26 17:33:05 |
Message-ID: | 4CC710D1.3040001@hogranch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 10/26/10 10:18 AM, Ozz Nixon wrote:
> I am the only user on this system right now, and one table select count(*) took over 20 minutes:
>
> wikitags exists and has 58,988,656 records.
>
> Structure (in pascal) is:
>
> quer.SQL.Add('create table '+DBTags+' (');
> quer.SQL.Add(' pagename '+SQL_TITLE+'(100) not null,');
> quer.SQL.Add(' tagword '+SQL_TITLE+'(15) not null,');
> quer.SQL.Add(' soundex2 '+SQL_TITLE+'(4) not null,');
> quer.SQL.Add(' metaphone '+SQL_TITLE+'(15) not null,');
> quer.SQL.Add(' metaphone2 '+SQL_TITLE+'(22) not null,');
> quer.SQL.Add(' carverphone '+SQL_TITLE+'(22) not null,');
> quer.SQL.Add(' instances '+SQL_INT32+' not null,');
> if SQL_NAME_PRIMARY_KEYS then quer.SQL.Add(' constraint '+DBTags+'_PK');
> quer.SQL.Add(' primary key(pagename, tagword, instances)');
> quer.SQL.Add(')');
>
> where SQL_TITLE = 'varchar', SQL_IN32 = 'int'
>
> I have hung off indexes for each column, to resolve my previous "performance" issue from 3+ weeks ago. However, COUNT() is still dog slow - this table is a write once, read many... *never* update, nor delete.
count(*) has to read the whole table to get the accurate count. The
reason for this is that different clients can see different versions of
that table, for instance, if client A is already in a transaction, and
client B then does an INSERT, the two clients will see different values
for the count.
From | Date | Subject | |
---|---|---|---|
Next Message | Alan Hodgson | 2010-10-26 17:33:51 | Re: Why Select Count(*) from table - took over 20 minutes? |
Previous Message | Bill Moran | 2010-10-26 17:26:31 | Re: Why Select Count(*) from table - took over 20 minutes? |