Thread: Bad Planner Statistics for Uneven distribution.

"Kevin McArthur" <Kevin@StormTide.ca> writes:
>          ->  Seq Scan on models_brands  (cost=0.00..6411.89 rows=369489 width=4) (actual time=0.040..1352.997
rows=369489loops=1) 
> ...
>    ->  Index Scan using models_brands_brand on models_brands  (cost=0.00..862236.96 rows=369489 width=4) (actual
time=0.122..1440.809rows=369489 loops=1) 

> Picks the wrong plan here. Should pick the index with seqscanning enabled.

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 ...

            regards, tom lane

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

"Guillaume Smet" <guillaume.smet@gmail.com> writes:
> 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)

Note that the above plan extract is pretty misleading, because it
doesn't account for the implicit "LIMIT 1" of an EXISTS() clause.
What the planner is *actually* imputing to this plan is 216410.97/92372
cost units, or about 2.34.  However that applies to the seqscan variant
as well.

I think the real issue with Kevin's example is that when doing an
EXISTS() on a brand_id that doesn't actually exist in the table, the
seqscan plan has worst-case behavior (ie, scan the whole table) while
the indexscan plan still manages to be cheap.  Because his brands table
has so many brand_ids that aren't in the table, that case dominates the
results.  Not sure how we could factor that risk into the cost
estimates.  The EXISTS code could probably special-case it reasonably
well for the simplest seqscan and indexscan subplans, but I don't see
what to do with more general subqueries (like joins).

            regards, tom lane