Re: planer picks a bad plan (seq-scan instead of index)

Thomas H. wrote:
>> OK - in that case try explicit subqueries:
>>
>> SELECT ... FROM
>> (SELECT * FROM shop.dvds
>> LEFT JOIN shop.oldtables.movies
>> WHERE lower(mov_name) LIKE ...
>> ) AS bar
>> LEFT JOIN shop.data_soundmedia
>
>
> same result, have tried this as well (22sec). it's the LEFT JOIN
> shop.data_soundmedia for which the planer picks a seqscan instead of
> index scan, no matter what...

Two things to try:
1. "SET enable_seqscan = false" and see if that forces it. If not
there's something very odd
2. Try adding a LIMIT 99 to the inner query (bar) so PG knows how many
(few) rows will emerge.

I'm guessing we're up against PG's poor estimate on the '%...%' filter.
If you were getting 160,000 rows in the final result then a seq-scan
might well be the way to go.

The only workaround that I can think of (if we can't persuade the
planner to cooperate) is to build a temp-table containing dvd_ean's for
the first part of the query then analyse it and join against that. That
way PG's row estimate will be accurate regardless of your text filtering.

--
Richard Huxton
Archonet Ltd