Re: Many to many join seems slow?

> Please provide an EXPLAIN ANALYZE of the query.

Oops, sorry about that.

=# EXPLAIN ANALYZE 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 = tp.source_id
-# AND tp.translation_id = t.translation_id
-# AND t.language_id = l.language_id
-# AND l.name = 'French' ;

QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
----------------------------
Merge Join (cost=524224.49..732216.29 rows=92447 width=97) (actual
time=1088.871..1351.840 rows=170759 loops=1)
Merge Cond: (tp.source_id = s.source_id)
-> Sort (cost=524224.49..524455.60 rows=92447 width=55) (actual
time=1088.774..1113.483 rows=170759 loops=1)
Sort Key: tp.source_id
-> Nested Loop (cost=1794.69..516599.30 rows=92447
width=55) (actual time=23.252..929.847 rows=170759 loops=1)
-> Nested Loop (cost=1794.69..27087.87 rows=86197
width=55) (actual time=23.194..132.139 rows=159686 loops=1)
-> Index Scan using language_name_key on
"language" l (cost=0.00..8.27 rows=1 width=4) (actual
time=0.030..0.031 rows=1 loops=1)
Index Cond: ((name)::text = 'French'::text)
-> Bitmap Heap Scan on translation t
(cost=1794.69..25882.43 rows=95774 width=59) (actual
time=23.155..95.227 rows=159686 loops=1)
Recheck Cond: (t.language_id =
l.language_id)
-> Bitmap Index Scan on
translation_language_l_key (cost=0.00..1770.74 rows=95774 width=0)
(actual time=22.329..22.329 rows=159686 loops=1)
Index Cond: (t.language_id =
l.language_id)
-> Index Scan using translation_pair_translation_id
on translation_pair tp (cost=0.00..5.67 rows=1 width=8) (actual
time=0.004..0.004 rows=1 loops=159686)
Index Cond: (tp.translation_id = t.translation_id)
-> Index Scan using source_pkey on source s
(cost=0.00..206227.65 rows=159283 width=46) (actual
time=0.086..110.564 rows=186176 loops=1)
Total runtime: 1366.757 ms
(16 rows)

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

> Drew Wilson escribió:
>
>> =# 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 = tp.source_id
>> AND tp.translation_id = t.translation_id
>> AND t.language_id = l.language_id
>> AND l.name = 'French' ;
>
> Please provide an EXPLAIN ANALYZE of the query.
>
> --
> Alvaro Herrera http://
> www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.