From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Diego Schulz <dschulz(at)gmail(dot)com> |
Cc: | PostgreSQL <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Why Select Count(*) from table - took over 20 minutes? |
Date: | 2010-10-27 11:15:08 |
Message-ID: | AANLkTimNQ+9821c---rX9z4w6VbyQ8UvCCMipeUri4et@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, Oct 26, 2010 at 4:30 PM, Diego Schulz <dschulz(at)gmail(dot)com> wrote:
> On Tue, Oct 26, 2010 at 2:18 PM, Ozz Nixon <ozznixon(at)gmail(dot)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(at)postgresql(dot)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.
Before you try that, you should ask yourself if you really need a 100%
accurate count. A reasonable approximation is maintained via the
stats system (pg_class.reltuples) that will often do and is free.
merlin
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel.Crespo | 2010-10-27 13:58:13 | How to merge data from two separate databases into one (maybe using xlogs)? |
Previous Message | Dmitriy Igrishin | 2010-10-27 10:14:45 | Re: Advice needed on application/database authentication/authorization/auditing model |