Re: Query Analysis

"typea":

> Why does this take minutes:
>
> SELECT DISTINCT *, 0 + (0 + 10 * (lower(title) like '%einstein%') ::int +
> 10 * (lower(author_flattened) like '%einstein%') ::int + 30 *
> (lower(subject_flattened) like '%einstein%') ::int + 30 * (lower(text)
> LIKE '%einstein%') ::int + 9 * (substring(lower(title), 1, 20) like
> '%einstein%') ::int + 25 * (substring(lower(text), 1, 20) LIKE
> '%einstein%') ::int ) AS points FROM article WHERE TRUE AND (FALSE OR
> (lower(title) like '%einstein%') OR (lower(author_flattened) like
> '%einstein%') OR (lower(subject_flattened) like '%einstein%') OR
> (lower(text) LIKE '%einstein%') ) ORDER BY points desc, volume, number,
> article.article LIMIT 10, 0
>
> while this takes seconds:
>
> SELECT *, 0 + 3 * ( title like '%Einstein%' )::int + 3 * ( author like
> '%Einstein%' )::int + ( ( 1 + 1 * ( lower(text) like '%einstein%' )::int )
> + ( 0 + ( subject like '%Einstein%' )::int ) ) AS points FROM article
> WHERE TRUE AND title like '%Einstein%' AND author like '%Einstein%' AND (
> ( TRUE AND lower(text) like '%einstein%' ) OR ( FALSE OR subject like
> '%Einstein%' ) ) ORDER BY points desc, volume, number, article.article
> LIMIT 10, 0

It's probably mostly the SELECT DISTINCT, which aggregates records and is
therefore slow. Try running EXPLAIN ANALYZE to see what steps are actually
taking the time.

--
-Josh Berkus
Aglio Database Solutions
San Francisco