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 | 0a2301c703f6$ed06ea40$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 |
> Try putting your conditions as part of the join: > SELECT * FROM shop.dvds > LEFT JOIN > oldtables.movies > ON > mov_id = dvd_mov_id > AND ( > lower(mov_name) LIKE '%superman re%' > OR lower(dvd_name) like '%superman re%' > OR lower(dvd_edition) LIKE '%superman re%' > ) > LEFT JOIN shop.data_soundmedia ON sm_info_ean = dvd_ean > unfortunately its getting optimized into the same plan :-) > 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. regards, thomas
В списке pgsql-general по дате отправления: