Andreas Kretschmer <andreas@a-kretschmer.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@potentialtech.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
loopjoin. Especially as here is an index available which gives a restriction to only 130 rows out of the 30000.
Tom Lane <tgl@sss.pgh.pa.us> writes:
> You might get some traction by creating indexes on lower(searchfield1) etc. This isn't even necessarily with an
expectationthat 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
theindexed expressions. I think that
> would give you better estimates about the LIKE rowcounts. You might have to crank up the statistics target for those
indexesif 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.