Edit Question - KnowledgeHub

Title Body

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

Tags (comma-separated) Cancel