Re: planer picks a bad plan (seq-scan instead of index)
От | Thomas H. |
---|---|
Тема | Re: planer picks a bad plan (seq-scan instead of index) |
Дата | |
Msg-id | 0ad301c703fc$72d1c320$0201a8c0@iwing обсуждение исходный текст |
Ответ на | planer picks a bad plan (seq-scan instead of index) when adding an additional join ("Thomas H." <me@alternize.com>) |
Ответы |
Re: planer picks a bad plan (seq-scan instead of index)
|
Список | pgsql-general |
> 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
В списке pgsql-general по дате отправления: