Re: [HACKERS] why is postgres estimating so badly?

"Luis Alberto Amigo Navarro" <lamigo(at)atc(dot)unican(dot)es> writes:
> -> Seq Scan on part (cost=0.00..12399.00 rows=1 width=4) (actual time=24.88..4076.81 rows=10856 loops=1)

Seems like the major misestimation is above: the LIKE clause on part is
estimated to select just one row, but it selects 10856 of 'em. Had the
planner realized the number of returned rows would be in the thousands,
it'd likely have used quite a different plan structure.

> AND part.name LIKE '%green%'

It's difficult for the planner to produce a decent estimate for the
selectivity of an unanchored LIKE clause, since there are no statistics
it can use for the purpose. We recently changed FIXED_CHAR_SEL in
src/backend/utils/adt/selfuncs.c from 0.04 to 0.20, which would make
this particular case come out better. (I believe the estimate would
work out to about 320, if part is 200K rows; that should be enough to
produce at least some change of plan.) You could try patching your
local installation likewise.

regards, tom lane