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. 479.998 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. 82.809 282,844.203 ↑ 3.8 100,399 3 / 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. 284.909 282,761.394 ↓ 1.3 489,405 3 / 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. 1,759.221 282,476.485 ↓ 1.3 489,405 3 / 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. 4,687.069 12,884.343 ↓ 1.3 489,405 3 / 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. 7,590.776 7,590.776 ↑ 1.3 48,146,274 3 / 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. 17.483 606.498 ↓ 1.8 16,355 3 / 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. 217.275 589.015 ↓ 1.8 16,355 3 / 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. 369.925 369.925 ↑ 1.3 2,473,062 3 / 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.019 1.815 ↑ 1.0 2 3 / 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.035 1.796 ↑ 1.0 2 3 / 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. 1.761 1.761 ↑ 1.0 2 3 / 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. 14,536.021 267,832.921 ↓ 367.2 4,645,994 3 / 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. 4,931.473 253,296.900 ↓ 367.2 4,645,994 3 / 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. 1,926.974 1,926.974 ↑ 1.3 4,645,996 3 / 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. 1,894.831 246,438.453 ↓ 459.0 13,937,983 3 / 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. 1,300.671 244,543.622 ↓ 459.0 13,937,983 3 / 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. 6,354.614 243,242.951 ↓ 459.0 13,937,983 3 / 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. 35,904.121 236,888.337 ↓ 459.0 13,937,983 3 / 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. 178,524.322 200,984.216 ↑ 1.0 189,535,877 3 / 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. 22,459.894 22,459.894 ↑ 1.0 189,535,877 3 / 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