On Tue, Oct 26, 2010 at 2:18 PM, Ozz Nixon <ozznixon@gmail.com> 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.
>
> Any suggestions?
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
You could try something like what's suggested in this blog post:
http://jakub.fedyczak.net/post/26
I didn't actually tried it, but I think it should work ok.
cheers,
diego