Search Postgresql Archives

Re: planer picks a bad plan (seq-scan instead of index)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux