Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 122E064FCD6 for ; Thu, 27 Nov 2008 00:50:32 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 61027-10 for ; Thu, 27 Nov 2008 00:50:29 -0400 (AST) X-Greylist: delayed 00:27:17.902374 by SQLgrey-1.7.6 Received: from qw-out-2122.google.com (qw-out-2122.google.com [74.125.92.24]) by mail.postgresql.org (Postfix) with ESMTP id A990F64FC8B for ; Thu, 27 Nov 2008 00:50:29 -0400 (AST) Received: by qw-out-2122.google.com with SMTP id 8so197756qwh.61 for ; Wed, 26 Nov 2008 20:50:28 -0800 (PST) Received: by 10.214.115.12 with SMTP id n12mr6346902qac.52.1227759790504; Wed, 26 Nov 2008 20:23:10 -0800 (PST) Received: from fedora9-32 ([204.14.152.163]) by mx.google.com with ESMTPS id 4sm2650721yxj.7.2008.11.26.20.23.08 (version=SSLv3 cipher=RC4-MD5); Wed, 26 Nov 2008 20:23:09 -0800 (PST) Organization: Kevin Kempter, LLC To: "pgsql-performance" Subject: performance tuning queries Date: Wed, 26 Nov 2008 21:21:04 -0700 User-Agent: KMail/1.9.10 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200811262121.05463.kevin@kevinkempterllc.com> From: Kevin Kempter 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: 200811/280 X-Sequence-Number: 32011 Hi All; I'm looking for tips / ideas per performance tuning some specific queries. These are generally large tables on a highly active OLTP system (100,000 - 200,000 plus queries per day) First off, any thoughts per tuning inserts into large tables. I have a large table with an insert like this: insert into public.bigtab1 (text_col1, text_col2, id) values ... QUERY PLAN ------------------------------------------ Result (cost=0.00..0.01 rows=1 width=0) (1 row) The query cost is low but this is one of the slowest statements per pgfouine Next we have a select count(*) that also one of the top offenders: select count(*) from public.tab3 where user_id=31 and state='A' and amount>0; QUERY PLAN ----------------------------------------------------------------------------------------------------- Aggregate (cost=3836.53..3836.54 rows=1 width=0) -> Index Scan using order_user_indx ontab3 user_id (cost=0.00..3834.29 rows=897 width=0) Index Cond: (idx_user_id = 31406948::numeric) Filter: ((state = 'A'::bpchar) AND (amount > 0::numeric)) (4 rows) We have an index on the user_id but not on the state or amount, add index to amount ? Thoughts ?