pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Daniel Manley <dmanley@libertyrms.info>
Cc: pgsql-performance@postgresql.org
Subject: Re: strange estimate for number of rows
Date: Thu, 13 Nov 2003 15:19:08 -0500
Message-ID: <11160.1068754748@sss.pgh.pa.us> (raw)
In-Reply-To: <3FB3DD1E.2000509@libertyrms.info>
References: <20031113181415.GB25546@libertyrms.info>
	<5882.1068749786@sss.pgh.pa.us>
	<3FB3DD1E.2000509@libertyrms.info>

Daniel Manley <dmanley@libertyrms.info> writes:
> The product_id alone gives a difference of a millions rows from estimate 
> to actual, vs. the factor of 2 from the transaction_date.

You should be thinking in terms of ratios, not absolute difference.
The rows estimate for product_id doesn't look too bad to me; the one for
transaction_date is much further off (a factor of 2).  Which is odd,
because the system can usually do all right on range estimates if you've
let it run an ANALYZE with enough histogram bins.  Could we see the
pg_stats row for transaction_date?

> We tried a couple of scenarios with effective_cache_size=60000, 
> cpu_index_tuple_cost=0.0001 and random_page_cost=2 with no change in the 
> plan.

Since you need about a factor of 3 change in the cost estimate to get it to
switch plans, changing random_page_cost by a factor of 2 ain't gonna do
it (the other two numbers are second-order adjustments unlikely to have
much effect, I think).  Try 1.5, or even less ... of course, you have to
keep an eye on your other queries and make sure they don't go nuts, but
from what I've heard about your hardware setup a low random_page_cost
isn't out of line with the physical realities.

			regards, tom lane



view thread (6+ messages)  latest in thread

Message-ID: <11160.1068754748@sss.pgh.pa.us>
Permalink:  ../11160.1068754748@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/11160.1068754748@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, dmanley@libertyrms.info
  Subject: Re: strange estimate for number of rows
  In-Reply-To: <11160.1068754748@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox