DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:in-reply-to:references:mime-version:content-type:message-id:cc:content-transfer-encoding:from:subject:date:to:x-mailer;
	b=pZQR4IBybW6SWnrv4ZrZkUU0ttY9+CP21YRnTeNWjyqqvtvmxmNDrUFzX2cQPvznZU4rTo7ylMyrhBzlw5ziN2C+f6OvXBuzK6RKjWGHrdHcgNyq6o16DGNVrRiWW20y9auk8jIxHd5jaK2dlW08nOQV+hQ7xM/mx5pzV0EUAbQ=
In-Reply-To: <20070515140505.GF6298@alvh.no-ip.org>
References: <49735678-25F5-48AA-B464-0864BAE56B55@gmail.com>
	<20070515140505.GF6298@alvh.no-ip.org>
Mime-Version: 1.0 (Apple Message framework v752.3)
Content-Type: text/plain; charset=ISO-8859-1; delsp=yes; format=flowed
Message-Id: <2FD25590-AAE3-47AE-9E75-42D1361C38E2@gmail.com>
Cc: pgsql-performance@postgresql.org
Content-Transfer-Encoding: quoted-printable
From: Drew Wilson <drewmwilson@gmail.com>
Subject: Re: Many to many join seems slow?
Date: Tue, 15 May 2007 07:11:40 -0700
To: Alvaro Herrera <alvherre@commandprompt.com>

> Please provide an EXPLAIN ANALYZE of the query.

Oops, sorry about that.

=3D# EXPLAIN ANALYZE SELECT s.source_id, s.value as sourceValue, =20
t.value as translationValue
-#     FROM
-#         source s,
-#     translation_pair tp,
-#     translation t,
-#     language l
-#     WHERE
-#     s.source_id =3D tp.source_id
-#     AND tp.translation_id =3D t.translation_id
-#     AND t.language_id =3D l.language_id
-#     AND l.name =3D 'French' ;
                                                                        =20=

           QUERY PLAN
------------------------------------------------------------------------=20=

------------------------------------------------------------------------=20=

----------------------------
Merge Join  (cost=3D524224.49..732216.29 rows=3D92447 width=3D97) =
(actual =20
time=3D1088.871..1351.840 rows=3D170759 loops=3D1)
    Merge Cond: (tp.source_id =3D s.source_id)
    ->  Sort  (cost=3D524224.49..524455.60 rows=3D92447 width=3D55) =
(actual =20
time=3D1088.774..1113.483 rows=3D170759 loops=3D1)
          Sort Key: tp.source_id
          ->  Nested Loop  (cost=3D1794.69..516599.30 rows=3D92447 =20
width=3D55) (actual time=3D23.252..929.847 rows=3D170759 loops=3D1)
                ->  Nested Loop  (cost=3D1794.69..27087.87 rows=3D86197 =20=

width=3D55) (actual time=3D23.194..132.139 rows=3D159686 loops=3D1)
                      ->  Index Scan using language_name_key on =20
"language" l  (cost=3D0.00..8.27 rows=3D1 width=3D4) (actual =20
time=3D0.030..0.031 rows=3D1 loops=3D1)
                            Index Cond: ((name)::text =3D =
'French'::text)
                      ->  Bitmap Heap Scan on translation t  =20
(cost=3D1794.69..25882.43 rows=3D95774 width=3D59) (actual =20
time=3D23.155..95.227 rows=3D159686 loops=3D1)
                            Recheck Cond: (t.language_id =3D =20
l.language_id)
                            ->  Bitmap Index Scan on =20
translation_language_l_key  (cost=3D0.00..1770.74 rows=3D95774 width=3D0) =
=20
(actual time=3D22.329..22.329 rows=3D159686 loops=3D1)
                                  Index Cond: (t.language_id =3D =20
l.language_id)
                ->  Index Scan using translation_pair_translation_id =20
on translation_pair tp  (cost=3D0.00..5.67 rows=3D1 width=3D8) (actual =20=

time=3D0.004..0.004 rows=3D1 loops=3D159686)
                      Index Cond: (tp.translation_id =3D =
t.translation_id)
    ->  Index Scan using source_pkey on source s  =20
(cost=3D0.00..206227.65 rows=3D159283 width=3D46) (actual =20
time=3D0.086..110.564 rows=3D186176 loops=3D1)
Total runtime: 1366.757 ms
(16 rows)

On May 15, 2007, at 7:05 AM, Alvaro Herrera wrote:

> Drew Wilson escribi=F3:
>
>> =3D# explain SELECT s.source_id, s.value AS sourceValue, t.value AS
>> translationValue
>>       FROM
>>           source s,
>>           translation_pair tp,
>>           translation t,
>>           language l
>>       WHERE
>>           s.source_id =3D tp.source_id
>>           AND tp.translation_id =3D t.translation_id
>>           AND t.language_id =3D l.language_id
>>           AND l.name =3D 'French' ;
>
> Please provide an EXPLAIN ANALYZE of the query.
>
> --=20
> Alvaro Herrera                                http://=20
> www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.