Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 47B1786E04D for ; Mon, 30 Jan 2012 20:42:18 -0400 (AST) Received: from smtp.01.com ([199.36.142.181]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Rs1nd-0002g1-AJ for pgsql-performance@postgresql.org; Tue, 31 Jan 2012 00:42:17 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by smtp-out-2.01.com (Postfix) with ESMTP id C96B34F499F for ; Mon, 30 Jan 2012 18:42:04 -0600 (CST) X-Virus-Scanned: amavisd-new at smtp-out-2.01.com Received: from smtp.01.com ([127.0.0.1]) by localhost (smtp-out-2.01.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 0utXwczUwU7F for ; Mon, 30 Jan 2012 18:42:04 -0600 (CST) Received: from localhost.localdomain (localhost.localdomain [127.0.0.1]) by smtp-out-2.01.com (Postfix) with ESMTP id A83B54F4B61 for ; Mon, 30 Jan 2012 18:42:04 -0600 (CST) Received: from Sidney-Stratton.local (unknown [12.154.42.75]) by smtp-out-2.01.com (Postfix) with ESMTPSA id 07A664F499F; Mon, 30 Jan 2012 18:42:02 -0600 (CST) Message-ID: <4F2738DA.6030102@agliodbs.com> Date: Mon, 30 Jan 2012 16:42:02 -0800 From: Josh Berkus Organization: PostgreSQL Experts Inc. User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US; rv:1.9.2.24) Gecko/20111103 Lightning/1.0b2 Thunderbird/3.1.16 MIME-Version: 1.0 To: Alessandro Gagliardi CC: pgsql-performance@postgresql.org Subject: Re: Why should such a simple query over indexed columns be so slow? References: <4F270AB1.6070100@agliodbs.com> In-Reply-To: X-Enigmail-Version: 1.1.1 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201201/142 X-Sequence-Number: 46154 > Looking at > http://www.postgresql.org/docs/current/static/runtime-config-query.html#GUC-RANDOM-PAGE-COSTI > wonder if I should try reducing random_page_cost? Yes, and I should speak to Heroku about reducing it by default. RPC represents the ratio between the cost of a sequential lookup of a single row vs. the cost of a random lookup. On standard spinning media on a dedicated server 4.0 is a pretty good estimate of this. However, you are running on shared storage in a cloud, which has different math. > Something that might help when it comes to advice on performance tuning is > that this database is used only for analytics. It's essentially a partial > replication of a production (document-oriented) database. So a lot of > normal operations that might employ a series of sequential fetches may not > actually be the norm in my case. Rather, I'm doing a lot of counts on data > that is typically randomly distributed. In that case, you might consider increasing default_statistics_target to 1000 and ANALYZEing your whole database. That increases the sample size for the database statstics collector, and most of the time will result in somewhat better plans on large tables and data with skewed distributions. This is not something which Heroku would do as standard, since most of their users are doing basic transactional webapps. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com