8
I have the following query
SELECT translation.id
FROM "TRANSLATION" translation
INNER JOIN "UNIT" unit
ON translation.fk_id_unit = unit.id
INNER JOIN "DOCUMENT" document
ON unit.fk_id_document = document.id
WHERE document.fk_id_job = 3665
ORDER BY translation.id asc
LIMIT 50
It runs for dreadful 110 seconds.
The table sizes:
+----------------+-------------+
| Table | Records |
+----------------+-------------+
| TRANSLATION | 6,906,679 |
| UNIT | 6,906,679 |
| DOCUMENT | 42,321 |
+----------------+-------------+
However, when I change the LIMIT parameter from 50 to 1000, the query finishes in 2 seconds.
Here is the query plan for the slow one
Limit (cost=0.00..146071.52 rows=50 width=8) (actual time=111916.180..111917.626 rows=50 loops=1)
-> Nested Loop (cost=0.00..50748166.14 rows=17371 width=8) (actual time=111916.179..111917.624 rows=50 loops=1)
Join Filter: (unit.fk_id_document = document.id)
-> Nested Loop (cost=0.00..39720545.91 rows=5655119 width=16) (actual time=0.051..15292.943 rows=5624514 loops=1)
-> Index Scan using "TRANSLATION_pkey" on "TRANSLATION" translation (cost=0.00..7052806.78 rows=5655119 width=16) (actual time=0.039..1887.757 rows=5624514 loops=1)
-> Index Scan using "UNIT_pkey" on "UNIT" unit (cost=0.00..5.76 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=5624514)
Index Cond: (unit.id = translation.fk_id_translation_unit)
-> Materialize (cost=0.00..138.51 rows=130 width=8) (actual time=0.000..0.006 rows=119 loops=5624514)
-> Index Scan using "DOCUMENT_idx_job" on "DOCUMENT" document (cost=0.00..137.86 rows=130 width=8) (actual time=0.025..0.184 rows=119 loops=1)
Index Cond: (fk_id_job = 3665)
and for the fast one