Skip site navigation (1) Skip section navigation (2)

Re: Query Analysis

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 (view raw or flat)
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


In response to

Responses

pgsql-performance by date

Next:From: David PradierDate: 2002-11-21 09:28:38
Subject: Is there a system of cache in pgsql 7.3rc1 ?
Previous:From: typeaDate: 2002-11-20 23:40:46
Subject: Query Analysis

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group