explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PQl

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 41,956.525 ↑ 1.0 10 1

Limit (cost=1,537,982.83..1,537,982.93 rows=10 width=1,444) (actual time=41,956.512..41,956.525 rows=10 loops=1)

2. 0.012 41,956.524 ↑ 7,119.0 10 1

Group (cost=1,537,982.83..1,538,694.73 rows=71,190 width=1,444) (actual time=41,956.511..41,956.524 rows=10 loops=1)

  • Group Key: term.id, category.value, category.category_type
3. 10.573 41,956.512 ↑ 3,746.8 19 1

Sort (cost=1,537,982.83..1,538,160.81 rows=71,190 width=1,444) (actual time=41,956.509..41,956.512 rows=19 loops=1)

  • Sort Key: term.id, category.value, category.category_type
  • Sort Method: quicksort Memory: 3271kB
4. 3.296 41,945.939 ↑ 11.8 6,012 1

Hash Left Join (cost=232,077.35..1,487,473.14 rows=71,190 width=1,444) (actual time=8,040.386..41,945.939 rows=6,012 loops=1)

  • Hash Cond: (term.category_id = category.id)
5. 1,745.507 41,941.834 ↑ 11.8 6,012 1

Hash Right Join (cost=232,065.77..1,486,482.70 rows=71,190 width=420) (actual time=8,039.559..41,941.834 rows=6,012 loops=1)

  • Hash Cond: (translation.term_id = term.id)
6. 38,169.058 39,035.366 ↓ 2.0 2,000,009 1

Bitmap Heap Scan on ll_translations translation (cost=36,627.25..1,274,766.03 rows=1,013,664 width=8) (actual time=880.295..39,035.366 rows=2,000,009 loops=1)

  • Recheck Cond: ((locale_id)::text = ANY ('{1,18}'::text[]))
  • Rows Removed by Index Recheck: 15083699
  • Filter: (NOT deleted)
  • Heap Blocks: exact=36205 lossy=1001480
7. 866.308 866.308 ↑ 1.0 2,000,009 1

Bitmap Index Scan on ll_translation_locale_id (cost=0.00..36,373.83 rows=2,027,327 width=0) (actual time=866.308..866.308 rows=2,000,009 loops=1)

  • Index Cond: ((locale_id)::text = ANY ('{1,18}'::text[]))
8. 6.800 1,160.961 ↑ 23.4 3,006 1

Hash (cost=190,714.70..190,714.70 rows=70,306 width=420) (actual time=1,160.961..1,160.961 rows=3,006 loops=1)

  • Buckets: 16384 Batches: 16 Memory Usage: 208kB
9. 0.985 1,154.161 ↑ 23.4 3,006 1

Merge Left Join (cost=186,609.12..190,714.70 rows=70,306 width=420) (actual time=1,152.889..1,154.161 rows=3,006 loops=1)

  • Merge Cond: (term.aka_group_id = aka_term.aka_group_id)
  • Join Filter: (term.id <> aka_term.id)
  • Rows Removed by Join Filter: 6
10. 1.718 6.552 ↑ 23.4 3,000 1

Sort (cost=58,093.14..58,268.91 rows=70,306 width=428) (actual time=6.261..6.552 rows=3,000 loops=1)

  • Sort Key: term.aka_group_id
  • Sort Method: quicksort Memory: 1641kB
11. 0.508 4.834 ↑ 23.4 3,000 1

Nested Loop (cost=0.42..38,734.03 rows=70,306 width=428) (actual time=0.045..4.834 rows=3,000 loops=1)

12. 0.038 0.038 ↑ 1.0 134 1

Values Scan on "*VALUES*" (cost=0.00..1.68 rows=134 width=32) (actual time=0.001..0.038 rows=134 loops=1)

13. 4.288 4.288 ↑ 23.9 22 134

Index Scan using ll_terms_title_id on ll_terms term (cost=0.42..283.80 rows=525 width=438) (actual time=0.017..0.032 rows=22 loops=134)

  • Index Cond: ((title_id)::text = "*VALUES*".column1)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
14. 0.008 1,146.624 ↑ 20,021.5 25 1

Materialize (cost=128,515.98..131,018.67 rows=500,538 width=16) (actual time=1,146.614..1,146.624 rows=25 loops=1)

15. 426.420 1,146.616 ↑ 38,502.9 13 1

Sort (cost=128,515.98..129,767.33 rows=500,538 width=16) (actual time=1,146.611..1,146.616 rows=13 loops=1)

  • Sort Key: aka_term.aka_group_id
  • Sort Method: external merge Disk: 17624kB
16. 720.196 720.196 ↓ 2.0 1,001,078 1

Seq Scan on ll_terms aka_term (cost=0.00..72,574.75 rows=500,538 width=16) (actual time=0.272..720.196 rows=1,001,078 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 6
17. 0.009 0.809 ↑ 5.0 14 1

Hash (cost=10.70..10.70 rows=70 width=1,040) (actual time=0.809..0.809 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.800 0.800 ↑ 5.0 14 1

Seq Scan on ll_categories category (cost=0.00..10.70 rows=70 width=1,040) (actual time=0.795..0.800 rows=14 loops=1)