explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iLKJ

Settings
# exclusive inclusive rows x rows loops node
1. 105.913 283,086.428 ↑ 3.0 300,871 1

Sort (cost=40,372,100.16..40,374,376.96 rows=910,722 width=44) (actual time=283,048.279..283,086.428 rows=300,871 loops=1)

  • Sort Key: (count(*)) DESC
  • Sort Method: external merge Disk: 16064kB
2. 0.000 282,980.515 ↑ 3.0 300,871 1

Finalize GroupAggregate (cost=40,113,084.27..40,225,918.93 rows=910,722 width=44) (actual time=282,720.472..282,980.515 rows=300,871 loops=1)

  • Group Key: application_terms.term_id, terms.term, term_couple_frequency.count
3. 0.000 283,324.201 ↑ 2.5 301,197 1

Gather Merge (cost=40,113,084.27..40,209,222.35 rows=758,936 width=44) (actual time=282,720.464..283,324.201 rows=301,197 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 248.427 848,532.609 ↑ 3.8 100,399 3

Partial GroupAggregate (cost=40,112,084.25..40,120,622.28 rows=379,468 width=44) (actual time=282,694.636..282,844.203 rows=100,399 loops=3)

  • Group Key: application_terms.term_id, terms.term, term_couple_frequency.count
5. 854.727 848,284.182 ↓ 1.3 489,405 3

Sort (cost=40,112,084.25..40,113,032.92 rows=379,468 width=36) (actual time=282,693.841..282,761.394 rows=489,405 loops=3)

  • Sort Key: application_terms.term_id, terms.term, term_couple_frequency.count
  • Sort Method: external merge Disk: 19200kB
  • Worker 0: Sort Method: external merge Disk: 18496kB
  • Worker 1: Sort Method: external merge Disk: 18696kB
6. 5,277.663 847,429.455 ↓ 1.3 489,405 3

Parallel Hash Join (cost=38,455,216.48..40,066,542.17 rows=379,468 width=36) (actual time=280,954.852..282,476.485 rows=489,405 loops=3)

  • Hash Cond: (application_terms.term_id = terms.id)
7. 14,061.207 38,653.029 ↓ 1.3 489,405 3

Parallel Hash Join (cost=79,311.50..1,689,185.90 rows=386,078 width=4) (actual time=609.079..12,884.343 rows=489,405 loops=3)

  • Hash Cond: (application_terms.application_id = application_triplets.application_id)
8. 22,772.328 22,772.328 ↑ 1.3 48,146,274 3

Parallel Seq Scan on application_terms (cost=0.00..1,382,579.88 rows=60,182,888 width=8) (actual time=2.267..7,590.776 rows=48,146,274 loops=3)

9. 52.449 1,819.494 ↓ 1.8 16,355 3

Parallel Hash (cost=79,198.01..79,198.01 rows=9,079 width=4) (actual time=606.498..606.498 rows=16,355 loops=3)

  • Buckets: 65536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 2752kB
10. 651.825 1,767.045 ↓ 1.8 16,355 3

Hash Join (cost=15.22..79,198.01 rows=9,079 width=4) (actual time=3.410..589.015 rows=16,355 loops=3)

  • Hash Cond: (application_triplets.triplet_id = triplets.id)
11. 1,109.775 1,109.775 ↑ 1.3 2,473,062 3

Parallel Seq Scan on application_triplets (cost=0.00..71,017.50 rows=3,091,350 width=8) (actual time=1.540..369.925 rows=2,473,062 loops=3)

12. 0.057 5.445 ↑ 1.0 2 3

Hash (cost=15.20..15.20 rows=2 width=4) (actual time=1.814..1.815 rows=2 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.105 5.388 ↑ 1.0 2 3

Bitmap Heap Scan on triplets (cost=8.57..15.20 rows=2 width=4) (actual time=1.791..1.796 rows=2 loops=3)

  • Recheck Cond: (triplet = ANY ('{B01B,B01D}'::text[]))
  • Heap Blocks: exact=2
14. 5.283 5.283 ↑ 1.0 2 3

Bitmap Index Scan on triplets_triplet_key (cost=0.00..8.57 rows=2 width=0) (actual time=1.761..1.761 rows=2 loops=3)

  • Index Cond: (triplet = ANY ('{B01B,B01D}'::text[]))
15. 43,608.063 803,498.763 ↓ 367.2 4,645,994 3

Parallel Hash (cost=38,375,746.83..38,375,746.83 rows=12,652 width=40) (actual time=267,832.921..267,832.921 rows=4,645,994 loops=3)

  • Buckets: 65536 (originally 32768) Batches: 512 (originally 1) Memory Usage: 2688kB
16. 14,794.419 759,890.700 ↓ 367.2 4,645,994 3

Hash Join (cost=38,177,878.75..38,375,746.83 rows=12,652 width=40) (actual time=246,441.762..253,296.900 rows=4,645,994 loops=3)

  • Hash Cond: (terms.id = term_couple_frequency.term_id)
17. 5,780.922 5,780.922 ↑ 1.3 4,645,996 3

Parallel Seq Scan on terms (cost=0.00..182,598.57 rows=5,816,957 width=28) (actual time=0.183..1,926.974 rows=4,645,996 loops=3)

18. 5,684.493 739,315.359 ↓ 459.0 13,937,983 3

Hash (cost=38,177,499.17..38,177,499.17 rows=30,366 width=12) (actual time=246,438.453..246,438.453 rows=13,937,983 loops=3)

  • Buckets: 131072 (originally 32768) Batches: 256 (originally 1) Memory Usage: 3350kB
19. 3,902.013 733,630.866 ↓ 459.0 13,937,983 3

Subquery Scan on term_couple_frequency (cost=38,177,119.60..38,177,499.17 rows=30,366 width=12) (actual time=241,838.343..244,543.622 rows=13,937,983 loops=3)

20. 19,063.842 729,728.853 ↓ 459.0 13,937,983 3

Sort (cost=38,177,119.60..38,177,195.51 rows=30,366 width=12) (actual time=241,838.337..243,242.951 rows=13,937,983 loops=3)

  • Sort Key: (count(DISTINCT term_couples.couple_id)) DESC
  • Sort Method: external merge Disk: 354648kB
  • Worker 0: Sort Method: external merge Disk: 354648kB
  • Worker 1: Sort Method: external merge Disk: 354648kB
21. 107,712.363 710,665.011 ↓ 459.0 13,937,983 3

GroupAggregate (cost=36,753,035.09..38,174,858.82 rows=30,366 width=12) (actual time=181,683.324..236,888.337 rows=13,937,983 loops=3)

  • Group Key: term_couples.term_id
22. 535,572.966 602,952.648 ↑ 1.0 189,535,877 3

Sort (cost=36,753,035.09..37,226,875.12 rows=189,536,009 width=8) (actual time=181,634.979..200,984.216 rows=189,535,877 loops=3)

  • Sort Key: term_couples.term_id
  • Sort Method: external merge Disk: 3338248kB
  • Worker 0: Sort Method: external merge Disk: 3338248kB
  • Worker 1: Sort Method: external merge Disk: 3338248kB
23. 67,379.682 67,379.682 ↑ 1.0 189,535,877 3

Seq Scan on term_couples (cost=0.00..2,919,879.09 rows=189,536,009 width=8) (actual time=0.150..22,459.894 rows=189,535,877 loops=3)

Planning time : 4.070 ms
Execution time : 283,749.166 ms