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.