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

Поиск
Список
Период
Сортировка
От Ozz Nixon
Тема Why Select Count(*) from table - took over 20 minutes?
Дата
Msg-id D4718551-6E2A-4840-8BCF-20901EA0E570@gmail.com
обсуждение исходный текст
Ответ на Re: what can depend on index  (Szymon Guz <mabewlun@gmail.com>)
Ответы Re: Why Select Count(*) from table - took over 20 minutes?  (Bill Moran <wmoran@potentialtech.com>)
Re: Why Select Count(*) from table - took over 20 minutes?  (John R Pierce <pierce@hogranch.com>)
Re: Why Select Count(*) from table - took over 20 minutes?  (Alan Hodgson <ahodgson@simkin.ca>)
Re: Why Select Count(*) from table - took over 20 minutes?  (Diego Schulz <dschulz@gmail.com>)
Re: Why Select Count(*) from table - took over 20 minutes?  (Diego Schulz <dschulz@gmail.com>)
Список 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()
isstill dog slow - this table is a write once, read many... *never* update, nor delete. 

Any suggestions?

В списке pgsql-general по дате отправления:

Предыдущее
От: Szymon Guz
Дата:
Сообщение: Re: what can depend on index
Следующее
От: Bill Moran
Дата:
Сообщение: Re: Why Select Count(*) from table - took over 20 minutes?