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
