Re: Slow query plan used

Andreas Kretschmer <andreas(at)a-kretschmer(dot)de> writes:
> please consider my plan B) and increase the stats. See my other mail.

I tried that also. Combined with the partial index. But still same result.

Bill Moran <wmoran(at)potentialtech(dot)com> writes:
> LIKE queries are probably challenging to plan, especially when they're
> not
> left-anchored: how can the planner be reasonalbly expected to estimate
> how many rows will be matched by a given LIKE expression.

That's clear to me. And because of that I expected the planner to use the table document as outer table in the nested loop join. Especially as here is an index available which gives a restriction to only 130 rows out of the 30000.

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> You might get some traction by creating indexes on lower(searchfield1) etc. This isn't even necessarily with an expectation that the planner would use
> those indexes in the plan ... but what it would do is make use of the statistics that ANALYZE will accumulate about the indexed expressions. I think that
> would give you better estimates about the LIKE rowcounts. You might have to crank up the statistics target for those indexes if the default isn't enough to
> make the estimates significantly better. (Obviously, don't forget to re-ANALYZE before checking results.)

I will try that. Does that mean the column statistics will only be collected when there's an index on the table/column?

Thanks for all your hints. I will go on and try.