"Greg Sabino Mullane" <greg@turnstep.com> writes:
> I tried the patch you sent, with no change. However, I then changed the
> default_statistics_target to 100, reanalyzed, and it came back with the
> "good" plan. Trying this on the original larger query (which pulls from
> tables with millions of rows, not the 10,000 subsets I created) worked
> fine too. Very odd. I confirmed that a lower stats worked fine on 8.2.3,
> and then narrowed it down to 99 - at 98 and below on 8.2.5, the "bad"
> plan is used, and at 99 and above, the "good" one is. I guess as a rule
> of thumb we'll crank up the default estimate on our 8.2 boxes. Any particular
> number recommended? Any reason why 99 is so magical?
If there are 100 or more histogram entries it'll do the estimation by
counting how many of the histogram entries match the pattern, rather
than using the prefix-range-based estimator (which is pretty much
all-fantasy anyway for a pattern with leading % :-().
http://archives.postgresql.org/pgsql-committers/2006-09/msg00331.php
I'm too lazy to go check, but I think the stats target is interpreted as
the number of histogram bins rather than values, which is why you'd see
the switchover at 99 not 100.
> I could have sworn I
> tried it with 100 last week and saw the bad plan.
Without that patch, 8.2.x's NOT LIKE estimator is completely bogus
anyway :-(. It has been broken right along --- I'm not sure why
your query produced a different plan in 8.2.3 than later, but it
wasn't as a result of changes here.
regards, tom lane