explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8V5Z

Settings
# exclusive inclusive rows x rows loops node
1. 2,482.281 29,558.691 ↓ 108.2 1,800,000 1

Unique (cost=369,438.44..369,812.59 rows=16,629 width=96) (actual time=25,866.88..29,558.691 rows=1,800,000 loops=1)

  • Buffers: shared hit=55187 read=32785, temp read=133656 written=133812
2. 4,906.737 27,076.410 ↓ 108.2 1,800,000 1

Sort (cost=369,438.44..369,480.01 rows=16,629 width=96) (actual time=25,866.877..27,076.41 rows=1,800,000 loops=1)

  • Sort Key: transaction_labels."TransactionId", transaction_labels."CaseId", transaction_labels."LabelSetId", transaction_labels."LabellerId", "TransactionLabels"."ClassifierId", "TransactionLabels"."Label", "TransactionLabels"."Id", ((consensus_base.consensus_count / ((count("TransactionLabels_1"."Label")))::numeric))
  • Sort Method: external merge Disk: 142856kB
  • Buffers: shared hit=55187 read=32785, temp read=133656 written=133812
3. 3,695.557 22,169.673 ↓ 108.2 1,800,000 1

Merge Join (cost=329,633.53..368,272.63 rows=16,629 width=96) (actual time=16,733..22,169.673 rows=1,800,000 loops=1)

  • Buffers: shared hit=55187 read=32785, temp read=95744 written=95826
4. 182.487 747.463 ↑ 1.5 100,000 1

GroupAggregate (cost=48,754.42..84,307.3 rows=150,719 width=39) (actual time=256.474..747.463 rows=100,000 loops=1)

  • Group Key: "TransactionLabels_1"."TransactionId", "TransactionLabels_1"."ClassifierId", "TransactionLabels_1"."LabelSetId"
  • Buffers: shared hit=5047 read=3291, temp read=579 written=581
5. 134.754 564.976 ↑ 2.4 102,514 1

Gather Merge (cost=48,754.42..80,309.11 rows=249,100 width=39) (actual time=256.447..564.976 rows=102,514 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=14494 read=9582, temp read=1660 written=1666
6. 112.631 430.222 ↑ 3.6 34,171 3 / 3

GroupAggregate (cost=47,754.39..50,556.77 rows=124,550 width=39) (actual time=251.569..430.222 rows=34,171 loops=3)

  • Group Key: "TransactionLabels_1"."TransactionId", "TransactionLabels_1"."ClassifierId", "TransactionLabels_1"."LabelSetId"
  • Buffers: shared hit=14494 read=9582, temp read=1660 written=1666
7. 161.834 317.591 ↑ 1.2 100,000 3 / 3

Sort (cost=47,754.39..48,065.77 rows=124,550 width=44) (actual time=251.552..317.591 rows=100,000 loops=3)

  • Sort Key: "TransactionLabels_1"."TransactionId", "TransactionLabels_1"."LabelSetId"
  • Sort Method: external merge Disk: 4632kB
  • Buffers: shared hit=14494 read=9582, temp read=1660 written=1666
8. 155.757 155.757 ↑ 1.2 100,000 3 / 3

Seq Scan on TransactionLabels TransactionLabels_1 (cost=0..33,381 rows=124,550 width=44) (actual time=0.025..155.757 rows=100,000 loops=3)

  • Filter: ("TransactionLabels_1"."ClassifierId" = 'Decision'::text)
  • Buffers: shared hit=14424 read=9582
9. 3,855.539 17,726.653 ↓ 27,272.7 1,800,000 1

Sort (cost=280,879.11..280,879.28 rows=66 width=104) (actual time=16,476.508..17,726.653 rows=1,800,000 loops=1)

  • Sort Key: transaction_labels."TransactionId"
  • Sort Method: external sort Disk: 169136kB
  • Buffers: shared hit=50140 read=29494, temp read=95165 written=95245
10. 2,577.108 13,871.114 ↓ 27,272.7 1,800,000 1

Hash Join (cost=210,071.37..280,877.12 rows=66 width=104) (actual time=5,472.942..13,871.114 rows=1,800,000 loops=1)

  • Buffers: shared hit=50140 read=29494, temp read=29138 written=29144
11. 2,506.121 11,293.954 ↓ 27,272.7 1,800,000 1

Hash Join (cost=210,026.72..280,831.56 rows=66 width=116) (actual time=5,472.865..11,293.954 rows=1,800,000 loops=1)

  • Buffers: shared hit=50139 read=29494, temp read=29138 written=29144
12. 1,949.417 5,789.635 ↓ 2,752.3 300,000 1

Hash Join (cost=117,184.72..168,640.72 rows=109 width=68) (actual time=2,473.718..5,789.635 rows=300,000 loops=1)

  • Buffers: shared hit=35619 read=20008, temp read=9018 written=9024
13. 1,366.655 1,366.655 ↑ 1.0 1,800,000 1

Seq Scan on TransactionLabels transaction_labels (cost=0..42,006 rows=1,800,000 width=45) (actual time=0.063..1,366.655 rows=1,800,000 loops=1)

  • Buffers: shared hit=14809 read=9197
14. 88.861 2,473.563 ↓ 83.3 100,000 1

Hash (cost=117,166.72..117,166.72 rows=1,200 width=49) (actual time=2,473.562..2,473.563 rows=100,000 loops=1)

  • Buffers: shared hit=20810 read=10811, temp read=1596 written=1858
15. 120.588 2,384.702 ↓ 83.3 100,000 1

Subquery Scan on consensus_base (cost=116,980.21..117,166.72 rows=1,200 width=49) (actual time=1,825.733..2,384.702 rows=100,000 loops=1)

  • Buffers: shared hit=20810 read=10811, temp read=1596 written=1602
16. 176.400 2,264.114 ↓ 83.3 100,000 1

GroupAggregate (cost=116,980.21..117,154.72 rows=1,200 width=49) (actual time=1,825.731..2,264.114 rows=100,000 loops=1)

  • Group Key: selected_transaction_labels."TransactionId", "TransactionLabels_2"."Label"
  • Buffers: shared hit=20810 read=10811, temp read=1596 written=1602
17. 105.377 2,087.714 ↓ 85.4 102,539 1

Gather Merge (cost=116,980.21..117,130.72 rows=1,200 width=25) (actual time=1,825.653..2,087.714 rows=102,539 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=63872 read=32244, temp read=4864 written=4882
18. 106.330 1,982.337 ↓ 57.0 34,180 3 / 3

GroupAggregate (cost=115,980.19..115,992.19 rows=600 width=25) (actual time=1,810.814..1,982.337 rows=34,180 loops=3)

  • Group Key: selected_transaction_labels."TransactionId", "TransactionLabels_2"."Label"
  • Buffers: shared hit=63872 read=32244, temp read=4864 written=4882
19. 161.185 1,876.007 ↓ 166.7 100,000 3 / 3

Sort (cost=115,980.19..115,981.69 rows=600 width=17) (actual time=1,810.8..1,876.007 rows=100,000 loops=3)

  • Sort Key: selected_transaction_labels."TransactionId", "TransactionLabels_2"."Label"
  • Sort Method: external merge Disk: 2240kB
  • Buffers: shared hit=63872 read=32244, temp read=4864 written=4882
20. 162.422 1,714.822 ↓ 166.7 100,000 3 / 3

Hash Join (cost=82,244.55..115,952.5 rows=600 width=17) (actual time=1,404.619..1,714.822 rows=100,000 loops=3)

  • Buffers: shared hit=63802 read=32244, temp read=3982 written=3997
21. 147.915 147.915 ↑ 1.2 100,000 3 / 3

Seq Scan on TransactionLabels TransactionLabels_2 (cost=0..33,381 rows=124,550 width=17) (actual time=0.033..147.915 rows=100,000 loops=3)

  • Filter: ("TransactionLabels_2"."ClassifierId" = 'Decision'::text)
  • Buffers: shared hit=14714 read=9292
22. 80.741 1,404.485 ↓ 210.1 100,000 3 / 3

Hash (cost=82,238.6..82,238.6 rows=476 width=4) (actual time=1,404.484..1,404.485 rows=100,000 loops=3)

  • Buffers: shared hit=49066 read=22952, temp read=2880 written=3333
23. 420.492 1,323.744 ↓ 210.1 100,000 3 / 3

GroupAggregate (cost=78,801.88..82,233.84 rows=476 width=4) (actual time=694.787..1,323.744 rows=100,000 loops=3)

  • Group Key: selected_transaction_labels."TransactionId"
  • Filter: (count(DISTINCT selected_transaction_labels."LabelSetId") = 1)
  • Buffers: shared hit=49066 read=22952, temp read=2880 written=2895
24. 454.642 903.252 ↓ 1.0 300,000 3 / 3

Sort (cost=78,801.88..79,549.18 rows=298,920 width=16) (actual time=694.764..903.252 rows=300,000 loops=3)

  • Sort Key: selected_transaction_labels."TransactionId"
  • Sort Method: external merge Disk: 7680kB
  • Buffers: shared hit=49066 read=22952, temp read=2880 written=2895
25. 448.610 448.610 ↓ 1.0 300,000 3 / 3

Seq Scan on TransactionLabels selected_transaction_labels (cost=0..46,506 rows=298,920 width=16) (actual time=0.022..448.61 rows=300,000 loops=3)

  • Filter: (selected_transaction_labels."ClassifierId" = 'Decision'::text)
  • Buffers: shared hit=49066 read=22952
26. 1,697.078 2,998.198 ↑ 1.0 1,800,000 1

Hash (cost=42,006..42,006 rows=1,800,000 width=60) (actual time=2,998.198..2,998.198 rows=1,800,000 loops=1)

  • Buffers: shared hit=14520 read=9486, temp written=17722
27. 1,301.120 1,301.120 ↑ 1.0 1,800,000 1

Seq Scan on TransactionLabels TransactionLabels (cost=0..42,006 rows=1,800,000 width=60) (actual time=0.022..1,301.12 rows=1,800,000 loops=1)

  • Buffers: shared hit=14520 read=9486
28. 0.011 0.052 ↑ 154.0 10 1

Hash (cost=25.4..25.4 rows=1,540 width=12) (actual time=0.052..0.052 rows=10 loops=1)

  • Buffers: shared hit=1
29. 0.041 0.041 ↑ 154.0 10 1

Seq Scan on LabelSets labelsets (cost=0..25.4 rows=1,540 width=12) (actual time=0.034..0.041 rows=10 loops=1)

  • Filter: labelsets."Completed"
  • Buffers: shared hit=1
Planning time : 1.284 ms
Execution time : 30,655.3 ms