Received: from localhost (unknown [200.46.204.183])
	by developer.postgresql.org (Postfix) with ESMTP id 457A12E005D
	for <pgsql-performance-postgresql.org@developer.postgresql.org>;
	Tue,  3 Jun 2008 05:18:55 -0300 (ADT)
Received: from developer.postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 05440-01
	for <pgsql-performance-postgresql.org@developer.postgresql.org>;
	Tue,  3 Jun 2008 05:18:48 -0300 (ADT)
X-Greylist: delayed 00:18:31.062603 by SQLgrey-1.7.6
Received: from mrxmail0.mbuyu.com (mrxmail0.mbuyu.com [85.158.204.176])
	by developer.postgresql.org (Postfix) with ESMTP id 19C062E005B
	for <pgsql-performance@postgresql.org>;
	Tue,  3 Jun 2008 05:18:50 -0300 (ADT)
Received: from [172.16.26.14] (a82-93-9-158.adsl.xs4all.nl [82.93.9.158])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by mrxmail0.mbuyu.com (Postfix) with ESMTP id DA1F01DE4
	for <pgsql-performance@postgresql.org>;
	Tue,  3 Jun 2008 10:00:14 +0200 (CEST)
Message-ID: <4844F95B.4040009@m4n.nl>
Date: Tue, 03 Jun 2008 09:57:15 +0200
From: Marcin Citowicki <marcin.citowicki@m4n.nl>
User-Agent: Mozilla-Thunderbird 2.0.0.9 (X11/20080110)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: query performance question
Content-Type: multipart/mixed; boundary="------------040009060008060508020407"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200806/8
X-Sequence-Number: 30358

This is a multi-part message in MIME format.
--------------040009060008060508020407
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hello,

I have a table (transactions) containing 61 414 503 rows. The basic 
count query (select count(transid) from transactions) takes 138226 
milliseconds.
This is the query analysis output:

Aggregate  (cost=2523970.79..2523970.80 rows=1 width=8) (actual 
time=268964.088..268964.090 rows=1 loops=1);
  ->  Seq Scan on transactions  (cost=0.00..2370433.43 rows=61414943 
width=8) (actual time=13.886..151776.860 rows=61414503 loops=1);
Total runtime: 268973.248 ms;

Query has several indexes defined, including one on transid column:

non-unique;index-qualifier;index-name;type;ordinal-position;column-name;asc-or-desc;cardinality;pages;filter-condition

f;<null>;transactions_id_key;3;1;transid;<null>;61414488;168877;<null>;
t;<null>;trans_ip_address_index;3;1;ip_address;<null>;61414488;168598;<null>;
t;<null>;trans_member_id_index;3;1;member_id;<null>;61414488;169058;<null>;
t;<null>;trans_payment_id_index;3;1;payment_id;<null>;61414488;168998;<null>;
t;<null>;trans_status_index;3;1;status;<null>;61414488;169005;<null>;
t;<null>;transactions__time_idx;3;1;time;<null>;61414488;168877;<null>;
t;<null>;transactions_offer_id_idx;3;1;offer_id;<null>;61414488;169017;<null>;

I'm not a dba so I'm not sure if the time it takes to execute this query 
is OK or not, it just  seems a bit long to me.
I'd appreciate it if someone could share his/her thoughts on this. Is 
there a way to make this table/query perform better?
Any query I'm running that joins with transactions table takes forever 
to complete, but maybe this is normal for a table this size.
Regards,

Marcin


--------------040009060008060508020407
Content-Type: text/x-vcard; charset=utf-8;
 name="marcin_citowicki.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="marcin_citowicki.vcf"

begin:vcard
fn:Marcin Citowicki
n:Citowicki;Marcin
email;internet:marcin.citowicki@m4n.nl
x-mozilla-html:FALSE
version:2.1
end:vcard


--------------040009060008060508020407--