DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=from:to:subject:date:user-agent:references:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:message-id;
	b=bvuDXdfdYxjBYz1OJJPJ4Mf8CW2Gv+RBkpR3oXe+SAsCsNFgt+kHjdRb7a1imuixUY
	aKF/1TmF/B4rQhieqGjTFKzHZLz31sfvstGZZ0u0s9lnVoi50NIbQA7hPSkjU/bu2n0R
	9IlirqOiZ3pw1RonRc55YVSybX08piFBrKMWU=
From: Marc Cousin <cousinmarc@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Query optimization
Date: Sun, 30 Nov 2008 20:37:39 +0100
User-Agent: KMail/1.9.10
References: <ggumvn$1n73$1@news.hub.org>
In-Reply-To: <ggumvn$1n73$1@news.hub.org>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200811302037.39618.cousinmarc@gmail.com>

Le Sunday 30 November 2008 19:45:11 tmp, vous avez =E9crit=A0:
> I am struggeling with the following query which fetches a random subset
> of 200 questions that matches certain tags within certain languages.
> However, the query takes forever to evaluate, even though I have a
> "limit 200" appended. Any ideas on how to optimize it?
>
> QUERY: =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D
>
> SELECT distinct q.question_id
>                FROM question_tags qt, questions q
>                WHERE q.question_id =3D qt.question_id
>                      AND q.STATUS =3D 1
>                      AND not q.author_id =3D 105
>                      AND ((qt.language_id =3D 5 and qt.tag_id in
> (1101,917,734,550,367,183)) or (qt.language_id =3D 4 and qt.tag_id in
> (856,428)) or (qt.language_id =3D
>   3 and qt.tag_id in (1156,1028,899,771,642,514,385,257,128)) or
> (qt.language_id =3D 2 and qt.tag_id in
> (1193,1101,1009,917,826,734,642,550,458,367,275,183,91)))
>                       and q.question_id not in (413)
>                LIMIT 200
>
> EXPLAIN ANALYZE: =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>
>   Limit  (cost=3D1.50..1267.27 rows=3D200 width=3D4) (actual
> time=3D278.169..880.934 rows=3D200 loops=3D1)
>     ->  Unique  (cost=3D1.50..317614.50 rows=3D50185 width=3D4) (actual
> time=3D278.165..880.843 rows=3D200 loops=3D1)
>           ->  Merge Join  (cost=3D1.50..317489.04 rows=3D50185 width=3D4)
> (actual time=3D278.162..880.579 rows=3D441 loops=3D1)
>                 Merge Cond: (qt.question_id =3D q.question_id)
>                 ->  Index Scan using question_tags_question_id on
> question_tags qt  (cost=3D0.00..301256.96 rows=3D82051 width=3D4) (actual
> time=3D24.171..146.811 rows=3D6067 loops=3D1)
>                       Filter: (((language_id =3D 5) AND (tag_id =3D ANY
> ('{1101,917,734,550,367,183}'::integer[]))) OR ((language_id =3D 4) AND
> (tag_id =3D ANY ('{856,428}'::integer[]))) OR ((language_id =3D 3) AND
> (tag_id =3D ANY ('{1156,1028,899,771,642,514,385,257,128}'::integer[])))
> OR ((language_id =3D 2) AND (tag_id =3D ANY
> ('{1193,1101,1009,917,826,734,642,550,458,367,275,183,91}'::integer[]))))
>                 ->  Index Scan using questions_pkey on questions q
> (cost=3D0.00..15464.12 rows=3D83488 width=3D4) (actual time=3D222.956..73=
1.737
> rows=3D1000 loops=3D1)
>                       Filter: ((q.author_id <> 105) AND (q.question_id
> <> 413) AND (q.status =3D 1))
>   Total runtime: 881.152 ms
> (9 rows)

=46irst, because of the distinct, the limit 200 wont reduce the work to be =
done=20
a lot : it will still have to sort for the unique. Only when everything is=
=20
sorted will it take only the first 200 records. And anyhow it seems there a=
re=20
only 441 rows before doing the distinct, so, at least for this query, it=20
won't change a lot the times.

Then it seems to me that you may try to create composed indexes, because th=
ere=20
is a lot of filtering after the index scans (that is if you want the query =
to=20
be faster).=20
Maybe (q.author_id,q.status).

=46or qt you may try (qt.language_id,qt.tag_id)...

Hope it helps

Cheers