DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=skd80xfRCopj5HDRwNcVUrQ0QGvHXYXErvMQkDhzvzDDC11UNb2oUVc7PDTTmfZbFIDBQhTyZMhZbIp6PTZ3dC4O06rXgK6ZJ6x1VuAtmK1S2XkXarRi7jtqMQB4Uymjcs/7xL+llrwTxKxixbiJv7r+9KFt3GSl0U/gL92eeN0=
Message-ID: <1d4e0c10607211500t62be09f2v8284ceb5176b01bc@mail.gmail.com>
Date: Sat, 22 Jul 2006 00:00:01 +0200
From: "Guillaume Smet" <guillaume.smet@gmail.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Subject: Re: Bad Planner Statistics for Uneven distribution.
Cc: "Kevin McArthur" <Kevin@stormtide.ca>, pgsql-performance@postgresql.org
In-Reply-To: <8073.1153517393@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <003b01c6acf1$a66d8910$0701a8c0@kdesktop>
	<8073.1153517393@sss.pgh.pa.us>

Tom,

On 7/21/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> It's really not possible for a full-table indexscan to be faster than a
> seqscan, and not very credible for it even to be approximately as fast.
> I suspect your second query here is the beneficiary of the first query
> having fetched all the pages into cache.  In general, if you want to
> optimize for a mostly-cached database, you need to reduce
> random_page_cost below its default value ...

We discussed this case on IRC and the problem was not the first set of
queries but the second one:
select brand_id from brands where exists (select 1 from models_brands
where brand = brands.brand_id);).

Isn't there any way to make PostgreSQL have a better estimation here:
->  Index Scan using models_brands_brand on models_brands
(cost=0.00..216410.97 rows=92372 width=0) (actual time=0.008..0.008
rows=0 loops=303)
           Index Cond: (brand = $0)

I suppose it's because the planner estimates that there will be 92372
result rows that it chooses the seqscan instead of the index scan.
ALTER STATISTICS didn't change anything.
IIRC, there were already a few threads about the same sort of
estimation problem and there wasn't any solution to solve this
problem. Do you have any hint/ideas?

--
Guillaume