> 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
