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...
I'd also be tempted to look at a tsearch2 setup for the word searches.
tsearch2 doesn't work that well for exact matches (including special
chars). but the culprit here isn't the '%...'%' seqscan, but rather the
additional joined table (where no lookup except for the join-column takes
place) that makes the query going from 200ms to 24sec.
Agreed, but I'd still be inclined to let tsearch do a first filter then
limit the results with LIKE.
would be a way to probably speed up the seqscan on shop.dvds that takes now
200ms. unfortunately, tsearch2 is broken for me in 8.2 (filling tsearch2
tvector columns crashes backend). but thats a different story :-)
- thomas
