Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 364CD2E002C for ; Tue, 25 Mar 2008 10:17:08 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 50491-06 for ; Tue, 25 Mar 2008 10:16:54 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from ciao.gmane.org (main.gmane.org [80.91.229.2]) by postgresql.org (Postfix) with ESMTP id 53E782E003C for ; Tue, 25 Mar 2008 10:17:02 -0300 (ADT) Received: from list by ciao.gmane.org with local (Exim 4.43) id 1Je91N-0002Zm-UI for pgsql-performance@postgresql.org; Tue, 25 Mar 2008 13:16:57 +0000 Received: from lara.cc.fer.hr ([161.53.72.113]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Tue, 25 Mar 2008 13:16:57 +0000 Received: from ivoras by lara.cc.fer.hr with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Tue, 25 Mar 2008 13:16:57 +0000 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-performance@postgresql.org From: Ivan Voras Subject: Re: what is the maximum number of rows in a table in postgresql 8.1 Date: Tue, 25 Mar 2008 14:16:43 +0100 Lines: 29 Message-ID: References: <7be3f35d0803250448h62f47b13mebe0252fca9b90e1@mail.gmail.com> <20080325120101.GC6756@alvh.no-ip.org> <47E8EEF1.40709@postnewspapers.com.au> Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@ger.gmane.org X-Gmane-NNTP-Posting-Host: lara.cc.fer.hr User-Agent: Thunderbird 2.0.0.6 (X11/20071022) In-Reply-To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=AWL=0.000 X-Spam-Level: X-Archive-Number: 200803/473 X-Sequence-Number: 29427 sathiya psql wrote: > EXPLAIN ANALYZE SELECT count(*) from call_log_in_ram ; > QUERY > PLAN > ------------------------------------ > ---------------------------------------------------------------------------------------------- > Aggregate (cost=90760.80..90760.80 rows=1 width=0) (actual > time=6069.373..6069.374 rows=1 loops=1) > -> Seq Scan on call_log_in_ram (cost=0.00..89121.24 rows=3279119 > width=0) (actual time=0.012..4322.345 rows=3279119 loops=1) > Total runtime: 6069.553 ms > (3 rows) You will never get good performance automatically with COUNT(*) in PostgreSQL. You can either create your own infrastructure (triggers, statistics tables, etc) or use an approximate result like this: CREATE OR REPLACE FUNCTION fcount(varchar) RETURNS bigint AS $$ SELECT reltuples::bigint FROM pg_class WHERE relname=$1; $$ LANGUAGE 'sql'; Use the above function as: SELECT fcount('table_name'); fcount -------- 7412 (1 row)