Re: Query planner making bad decisions

Cory Coager <ccoager(at)davisvision(dot)com> writes:
> Even better yet, if I turn off enable_nestloop the query runs in
> 3499.970 ms:

The reason it prefers a nestloop inappropriately is a mistaken estimate
that some plan node is only going to yield a very small number of rows
(like one or two --- there's not a hard cutoff, but usually more than
a couple of estimated rows will lead it away from a nestloop).
In this case the worst problem seems to be here:

> -> Index Scan using
> ticketcustomfieldvalues2 on objectcustomfieldvalues
> objectcustomfieldvalues_2 (cost=0.00..26514.04 rows=1 width=8) (actual
> time=1493.091..1721.155 rows=1575 loops=1)
> Filter: ((disabled = 0) AND
> ((objecttype)::text = 'RT::Ticket'::text) AND ((content)::text ~~
> '%Patient Sat Survey%'::text))

where we're off by a factor of 1500+ :-(

I think most likely the ~~ operator is the biggest problem.
Unfortunately 8.1's estimator for ~~ is not terribly bright. You could
try increasing your statistics target but I don't think it will help
much. Is there any chance of updating to 8.2 or later? 8.2 can do
significantly better on this type of estimate as long as it has enough
stats.

In any case I'd suggest raising default_statistics_target to 100 or so,
as you seem to be running queries complex enough to need good stats.
But I'm not sure that that will be enough to fix the problem in 8.1.

regards, tom lane