X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 1AE7A9FA3CD for ; Mon, 1 May 2006 21:02:14 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 27027-04 for ; Mon, 1 May 2006 21:02:01 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.89glass.com (mail.89glass.com [72.10.2.250]) by postgresql.org (Postfix) with SMTP id 3D2929FA2BE for ; Mon, 1 May 2006 21:02:00 -0300 (ADT) Received: (qmail 25859 invoked by uid 1006); 2 May 2006 00:01:58 -0000 Received: from 192.168.254.111 by lumberjack (envelope-from , uid 201) with qmail-scanner-1.25 (clamdscan: 0.67. Clear:RC:1(192.168.254.111):. Processed in 0.060224 secs); 02 May 2006 00:01:58 -0000 Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=domkey; d=rbsinteractive.com; b=TVk7S+JQOqPVHoAN+yZSrPDYOjAJ+mhHTh2w7Zv7AE+Cn/vT8Gqdp4e9ou0HDabSs3ymr8tJgZ0li1l242No9KJRvJbamt5La4WZcFPj2unc9+b+POqA+cUznhC+Zrx88TK8MaECOmEZSGTe1xvjn8grKjLRzCSxpRpCIaW8gKc= ; Received: from unknown (HELO ?192.168.254.111?) (192.168.254.111) by mail.89glass.com with SMTP; 2 May 2006 00:01:58 -0000 Message-ID: <4456A177.3020505@rbsinteractive.com> Date: Mon, 01 May 2006 17:01:59 -0700 From: Nolan Cafferky User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane CC: pgsql-performance@postgresql.org Subject: Re: Cluster vs. non-cluster query planning References: <445644F9.2020708@rbsinteractive.com> <44566612.8020709@rbsinteractive.com> <8044.1146515444@sss.pgh.pa.us> <445678B1.9050908@rbsinteractive.com> <20934.1146526502@sss.pgh.pa.us> In-Reply-To: <20934.1146526502@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------060601050208010305000704" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/24 X-Sequence-Number: 18811 This is a multi-part message in MIME format. --------------060601050208010305000704 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Tom Lane wrote: >Nolan Cafferky writes: > >>But, I'm guessing that random_page_cost = 1 is not a realistic value. >> > >Well, that depends. If all your data can be expected to fit in memory >then it is a realistic value. (If not, you should be real careful not >to make performance decisions on the basis of test cases that *do* fit >in RAM...) > >In any case, if I recall your numbers correctly you shouldn't need to >drop it nearly that far to get the thing to make the right choice. >A lot of people run with random_page_cost set to 2 or so. > Thanks for the advice. I will check what changing random_page_cost does for the rest of the queries on our system. I did learn why the estimated row count was so high. This is new knowledge to me, so I'm going to share it. SELECT reltuples FROM pg_class WHERE relname = 'orders'; -> produces 98426. SELECT n_distinct FROM pg_stats WHERE tablename = 'orders' and attname = 'order_statuses_id'; -> currently 13. Seq Scan on orders o (cost=1.20..11395.53 rows=7570 width=8) (actual time=283.599..285.031 rows=105 loops=1) Filter: (order_statuses_id = $0) InitPlan -> Seq Scan on order_statuses (cost=0.00..1.20 rows=1 width=4) (actual time=0.031..0.038 rows=1 loops=1) Filter: ((id_name)::text = 'new'::text) Total runtime: 285.225 ms (98426 / 13)::integer = 7571 ~= 7570, the estimated row count. So the query planner isn't able to combine the knowledge of the id value from order_statuses with most_common_vals, most_common_freqs, or histogram_bounds from pg_stats. That seems a little odd to me, but maybe it makes sense. I suppose the planner can't start executing parts of the query to aid in the planning process. In the future, I will probably pre-select from order_statuses before executing this query. Thanks! --------------060601050208010305000704 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Tom Lane wrote: 
Nolan Cafferky <Nolan.Cafferky@rbsinteractive.com> writes:

But, I'm guessing that random_page_cost = 1 is not a realistic value.


Well, that depends.  If all your data can be expected to fit in memory
then it is a realistic value.  (If not, you should be real careful not
to make performance decisions on the basis of test cases that *do* fit
in RAM...)

In any case, if I recall your numbers correctly you shouldn't need to
drop it nearly that far to get the thing to make the right choice.
A lot of people run with random_page_cost set to 2 or so.
Thanks for the advice.  I will check what changing random_page_cost does for the rest of the queries on our system.

I did learn why the estimated row count was so high.  This is new knowledge to me, so I'm going to share it.

SELECT reltuples FROM pg_class WHERE relname = 'orders'; -> produces 98426.
SELECT n_distinct FROM pg_stats WHERE tablename = 'orders' and attname = 'order_statuses_id'; -> currently 13.

 Seq Scan on orders o  (cost=1.20..11395.53 rows=7570 width=8) (actual time=283.599..285.031 rows=105 loops=1)
   Filter: (order_statuses_id = $0)
   InitPlan
     ->  Seq Scan on order_statuses  (cost=0.00..1.20 rows=1 width=4) (actual time=0.031..0.038 rows=1 loops=1)
           Filter: ((id_name)::text = 'new'::text)
 Total runtime: 285.225 ms

(98426 / 13)::integer = 7571 ~= 7570, the estimated row count.

So the query planner isn't able to combine the knowledge of the id value from order_statuses with most_common_vals, most_common_freqs, or histogram_bounds from pg_stats. That seems a little odd to me, but maybe it makes sense. I suppose the planner can't start executing parts of the query to aid in the planning process.

In the future, I will probably pre-select from order_statuses before executing this query.

Thanks!

 --------------060601050208010305000704--