Re: Planner problems in 8.2.4 and 8.2.5 (was: Possible planner bug/regression introduced in 8.2.5)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

> Is there a reason you rounded off most of the costs? It looks like the
> estimated costs of the two join types are nearly equal, and so it's pure
> chance which one gets chosen.

No real reason, it's just a post-processing script used to make explain
output a little more readable. I'll leave in all the sigfigs next time.

> This might be a bug in the LIKE estimator (if so, it's in 8.2.3 as
> well). I don't have time to look closer right now, but can you show us
> the pg_stats row for orders_smaller.order_number?

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? I could have sworn I
tried it with 100 last week and saw the bad plan. Guess I should also
boost my default target testing up a bit as well. Thanks for the quick patch,
we'll definitely apply that as well for safety.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200711081137
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFHMztMvJuQZxSWSsgRA0pfAKDHWvUafv0bwL/nzmP5yXuptTPX7gCfbMNr
uMLI9yy6Prwt0DOHBsLu/Pk=
=1Vsj
-----END PGP SIGNATURE-----