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 5ABFD9FA2C7
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon,  1 May 2006 16:48:50 -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 29973-10
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon,  1 May 2006 16:48:38 -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 83B169F9586
	for <pgsql-performance@postgresql.org>;
	Mon,  1 May 2006 16:48:38 -0300 (ADT)
Received: (qmail 27065 invoked by uid 1006); 1 May 2006 19:48:36 -0000
Received: from 192.168.254.111 by lumberjack (envelope-from
	<Nolan.Cafferky@rbsinteractive.com>,
	uid 201) with qmail-scanner-1.25 (clamdscan: 0.67.  
	Clear:RC:1(192.168.254.111):.
	Processed in 0.032636 secs); 01 May 2006 19:48:36 -0000
Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
	s=domkey; d=rbsinteractive.com;
	b=x/cnIkExLNDVpMU+yYqeo7d4ixsWL2KinuFNhxx0PC863nPn/RhoJaxaGFbnVXDj9I2cmHuAHrpsWKMAGzJmt/zuS+PgDNzZvZqUMyJ8FF3AbgK9trpknogYfS9iziVK6nGaNWj4k+65SALD3jZFQdaa2I5Up25J6nif/b/059M=
	;
Received: from unknown (HELO ?192.168.254.111?) (192.168.254.111)
	by mail.89glass.com with SMTP; 1 May 2006 19:48:36 -0000
Message-ID: <44566612.8020709@rbsinteractive.com>
Date: Mon, 01 May 2006 12:48:34 -0700
From: Nolan Cafferky <Nolan.Cafferky@rbsinteractive.com>
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Cluster vs. non-cluster query planning
References: <445644F9.2020708@rbsinteractive.com>
In-Reply-To: <445644F9.2020708@rbsinteractive.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200605/19
X-Sequence-Number: 18806

> Questions:
> * What can I do to reduce the estimated row count on the query?
> * Why does clustering drive down the estimated cost for the index scan 
> so much? Does a change in correlation from .72 to 1 make that much of 
> a difference?
> * Can I convince my query planner to index scan without clustering on 
> the order_statuses_id index, or setting enable_seqscan = off? 


After some more digging on the mailing list, I found some comments on 
effective_cache_size.  Bringing it up from the default of 1000 does pust 
the estimated cost for the index scan below that of the sequential scan, 
but not by much. 

With SET effective_cache_size = 1000:
 Seq Scan on orders o  (cost=1.20..11395.53 rows=7029 width=8) (actual 
time=280.148..281.512 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.012..0.020 rows=1 loops=1)
           Filter: ((id_name)::text = 'new'::text)
 Total runtime: 281.700 ms

With SET effective_cache_size = 10000:
 Index Scan using orders_status_btree_idx on orders o  
(cost=1.20..9710.91 rows=7029 width=8) (actual time=0.050..0.372 
rows=105 loops=1)
   Index Cond: (order_statuses_id = $0)
   InitPlan
     ->  Seq Scan on order_statuses  (cost=0.00..1.20 rows=1 width=4) 
(actual time=0.016..0.024 rows=1 loops=1)
           Filter: ((id_name)::text = 'new'::text)

The ratios between estimated costs are still nowhere near the ratio of 
actual costs.