From: Guillaume Smet <guillaume.smet@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kevin McArthur <Kevin@stormtide.ca>
Cc: pgsql-performance@postgresql.org
Subject: Re: Bad Planner Statistics for Uneven distribution.
Date: Sat, 22 Jul 2006 00:00:01 +0200
Message-ID: <1d4e0c10607211500t62be09f2v8284ceb5176b01bc@mail.gmail.com> (raw)
In-Reply-To: <8073.1153517393@sss.pgh.pa.us>
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
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: guillaume.smet@gmail.com, tgl@sss.pgh.pa.us, Kevin@stormtide.ca
Subject: Re: Bad Planner Statistics for Uneven distribution.
In-Reply-To: <1d4e0c10607211500t62be09f2v8284ceb5176b01bc@mail.gmail.com>
* 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