Why Select Count(*) from table - took over 20 minutes?

From: Ozz Nixon <ozznixon(at)gmail(dot)com>
To: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Cc: Ozz Nixon <ozznixon(at)gmail(dot)com>
Subject: Why Select Count(*) from table - took over 20 minutes?
Date: 2010-10-26 17:18:41
Message-ID: D4718551-6E2A-4840-8BCF-20901EA0E570@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Any suggestions?

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bill Moran 2010-10-26 17:26:31 Re: Why Select Count(*) from table - took over 20 minutes?
Previous Message Szymon Guz 2010-10-26 14:37:58 Re: what can depend on index