explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hdSl

Settings
# exclusive inclusive rows x rows loops node
1. 0.357 20.235 ↑ 3.6 424 1

GroupAggregate (cost=16,349.68..16,388.07 rows=1,528 width=18) (actual time=19.772..20.235 rows=424 loops=1)

  • Group Key: pt.trans_id
2.          

CTE cte_all_langs

3. 0.332 1.233 ↑ 1.4 109 1

HashAggregate (cost=3,608.50..3,610.02 rows=152 width=2) (actual time=1.214..1.233 rows=109 loops=1)

  • Group Key: pmatch_translations.lang_id
4. 0.752 0.901 ↑ 1.1 1,407 1

Bitmap Heap Scan on pmatch_translations (cost=56.26..3,604.68 rows=1,528 width=2) (actual time=0.164..0.901 rows=1,407 loops=1)

  • Recheck Cond: (hash_id = '-9215640509391561808'::bigint)
  • Heap Blocks: exact=70
5. 0.149 0.149 ↑ 1.1 1,407 1

Bitmap Index Scan on pmatch_translations__composite_1 (cost=0.00..55.88 rows=1,528 width=0) (actual time=0.148..0.149 rows=1,407 loops=1)

  • Index Cond: (hash_id = '-9215640509391561808'::bigint)
6.          

CTE cte_phr_langs

7. 0.014 0.055 ↓ 1.8 56 1

Unique (cost=0.42..79.44 rows=31 width=2) (actual time=0.028..0.055 rows=56 loops=1)

8. 0.041 0.041 ↓ 1.6 56 1

Index Only Scan using pmatch_links_pkey on pmatch_links (cost=0.42..79.35 rows=35 width=2) (actual time=0.027..0.041 rows=56 loops=1)

  • Index Cond: (phrase_id = '-9215640654011170530'::bigint)
  • Heap Fetches: 56
9.          

CTE cte_ava_langs

10. 0.044 1.421 ↑ 2.9 53 1

HashSetOp Except (cost=0.00..5.95 rows=152 width=6) (actual time=1.414..1.421 rows=53 loops=1)

11. 0.018 1.377 ↑ 1.1 165 1

Append (cost=0.00..5.49 rows=183 width=6) (actual time=1.216..1.377 rows=165 loops=1)

12. 0.018 1.281 ↑ 1.4 109 1

Subquery Scan on *SELECT* 1 (cost=0.00..4.56 rows=152 width=6) (actual time=1.216..1.281 rows=109 loops=1)

13. 1.263 1.263 ↑ 1.4 109 1

CTE Scan on cte_all_langs (cost=0.00..3.04 rows=152 width=2) (actual time=1.215..1.263 rows=109 loops=1)

14. 0.010 0.078 ↓ 1.8 56 1

Subquery Scan on *SELECT* 2 (cost=0.00..0.93 rows=31 width=6) (actual time=0.030..0.078 rows=56 loops=1)

15. 0.068 0.068 ↓ 1.8 56 1

CTE Scan on cte_phr_langs (cost=0.00..0.62 rows=31 width=2) (actual time=0.029..0.068 rows=56 loops=1)

16. 0.513 19.878 ↑ 1.4 1,336 1

Sort (cost=12,654.28..12,659.10 rows=1,929 width=18) (actual time=19.764..19.878 rows=1,336 loops=1)

  • Sort Key: pt.trans_id
  • Sort Method: quicksort Memory: 153kB
17. 0.000 19.365 ↑ 1.4 1,336 1

Hash Semi Join (cost=1,061.63..12,549.02 rows=1,929 width=18) (actual time=3.804..19.365 rows=1,336 loops=1)

  • Hash Cond: (pt.lang_id = cte_ava_langs.lang_id)
18. 12.094 23.635 ↓ 2.2 4,170 1

Gather (cost=1,056.69..12,517.55 rows=1,929 width=18) (actual time=0.658..23.635 rows=4,170 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
19. 6.974 11.541 ↓ 1.8 2,085 2 / 2

Nested Loop (cost=56.69..11,324.65 rows=1,135 width=18) (actual time=0.183..11.541 rows=2,085 loops=2)

20. 0.224 0.346 ↑ 1.3 704 2 / 2

Parallel Bitmap Heap Scan on pmatch_translations pt (cost=56.26..3,596.81 rows=899 width=10) (actual time=0.150..0.346 rows=704 loops=2)

  • Recheck Cond: (hash_id = '-9215640509391561808'::bigint)
  • Heap Blocks: exact=61
21. 0.122 0.122 ↑ 1.1 1,407 1 / 2

Bitmap Index Scan on pmatch_translations__composite_1 (cost=0.00..55.88 rows=1,528 width=0) (actual time=0.244..0.244 rows=1,407 loops=1)

  • Index Cond: (hash_id = '-9215640509391561808'::bigint)
22. 4.221 4.221 ↑ 1.0 3 1,407 / 2

Index Only Scan using pmatch_links__trans_id on pmatch_links pl (cost=0.42..8.57 rows=3 width=8) (actual time=0.005..0.006 rows=3 loops=1,407)

  • Index Cond: (trans_id = pt.trans_id)
  • Heap Fetches: 3517
23. 0.012 1.446 ↑ 2.9 53 1

Hash (cost=3.04..3.04 rows=152 width=2) (actual time=1.446..1.446 rows=53 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
24. 1.434 1.434 ↑ 2.9 53 1

CTE Scan on cte_ava_langs (cost=0.00..3.04 rows=152 width=2) (actual time=1.415..1.434 rows=53 loops=1)

Planning time : 0.554 ms
Execution time : 26.904 ms