Cory Coager <ccoager@xxxxxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
