From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | <typea(at)l-i-e(dot)com>, <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Query Analysis |
Date: | 2002-11-20 23:52:40 |
Message-ID: | 200211201552.40452.josh@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
"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
From | Date | Subject | |
---|---|---|---|
Next Message | David Pradier | 2002-11-21 09:28:38 | Is there a system of cache in pgsql 7.3rc1 ? |
Previous Message | typea | 2002-11-20 23:40:46 | Query Analysis |