explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Uvf

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

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

  • Buffers: shared hit=55516 read=33509, temp read=133723 written=133879
2. 4,780.763 26,586.801 ↓ 108.2 1,800,000 1

Sort (cost=369,438.44..369,480.01 rows=16,629 width=96) (actual time=25,405.078..26,586.801 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=55516 read=33509, temp read=133723 written=133879
3. 3,607.252 21,806.038 ↓ 108.2 1,800,000 1

Merge Join (cost=329,633.53..368,272.63 rows=16,629 width=96) (actual time=16,500.919..21,806.038 rows=1,800,000 loops=1)

  • Buffers: shared hit=55516 read=33509, temp read=95811 written=95893
4. 176.399 826.869 ↑ 1.5 100,000 1

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

  • Group Key: "TransactionLabels_1"."TransactionId", "TransactionLabels_1"."ClassifierId", "TransactionLabels_1"."LabelSetId"
  • Buffers: shared hit=5032 read=3651, temp read=602 written=604
5. 133.454 650.470 ↑ 2.4 102,332 1

Gather Merge (cost=48,754.42..80,309.11 rows=249,100 width=39) (actual time=347.525..650.47 rows=102,332 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=14015 read=10061, temp read=1660 written=1666
6. 113.245 517.016 ↑ 3.7 34,111 3 / 3

GroupAggregate (cost=47,754.39..50,556.77 rows=124,550 width=39) (actual time=338.734..517.016 rows=34,111 loops=3)

  • Group Key: "TransactionLabels_1"."TransactionId", "TransactionLabels_1"."ClassifierId", "TransactionLabels_1"."LabelSetId"
  • Buffers: shared hit=14015 read=10061, temp read=1660 written=1666
7. 199.977 403.771 ↑ 1.2 100,000 3 / 3

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

  • Sort Key: "TransactionLabels_1"."TransactionId", "TransactionLabels_1"."LabelSetId"
  • Sort Method: external merge Disk: 4816kB
  • Buffers: shared hit=14015 read=10061, temp read=1660 written=1666
8. 203.794 203.794 ↑ 1.2 100,000 3 / 3

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

  • Filter: ("TransactionLabels_1"."ClassifierId" = 'Decision'::text)
  • Buffers: shared hit=13945 read=10061
9. 3,632.405 17,371.917 ↓ 27,272.7 1,800,000 1

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

  • Sort Key: transaction_labels."TransactionId"
  • Sort Method: external sort Disk: 169136kB
  • Buffers: shared hit=50484 read=29858, temp read=95209 written=95289
10. 2,423.991 13,739.512 ↓ 27,272.7 1,800,000 1

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

  • Buffers: shared hit=50484 read=29858, temp read=29182 written=29188
11. 2,286.161 11,315.491 ↓ 27,272.7 1,800,000 1

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

  • Buffers: shared hit=50483 read=29858, temp read=29182 written=29188
12. 1,855.892 5,812.277 ↓ 2,752.3 300,000 1

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

  • Buffers: shared hit=36442 read=19893, temp read=9062 written=9068
13. 1,333.447 1,333.447 ↑ 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,333.447 rows=1,800,000 loops=1)

  • Buffers: shared hit=14328 read=9678
14. 94.183 2,622.938 ↓ 83.3 100,000 1

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

  • Buffers: shared hit=22114 read=10215, temp read=1640 written=1902
15. 122.394 2,528.755 ↓ 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,939.131..2,528.755 rows=100,000 loops=1)

  • Buffers: shared hit=22114 read=10215, temp read=1640 written=1646
16. 180.781 2,406.361 ↓ 83.3 100,000 1

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

  • Group Key: selected_transaction_labels."TransactionId", "TransactionLabels_2"."Label"
  • Buffers: shared hit=22114 read=10215, temp read=1640 written=1646
17. 117.198 2,225.580 ↓ 85.4 102,476 1

Gather Merge (cost=116,980.21..117,130.72 rows=1,200 width=25) (actual time=1,939.096..2,225.58 rows=102,476 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=66827 read=29289, temp read=4864 written=4882
18. 109.868 2,108.382 ↓ 56.9 34,159 3 / 3

GroupAggregate (cost=115,980.19..115,992.19 rows=600 width=25) (actual time=1,932.117..2,108.382 rows=34,159 loops=3)

  • Group Key: selected_transaction_labels."TransactionId", "TransactionLabels_2"."Label"
  • Buffers: shared hit=66827 read=29289, temp read=4864 written=4882
19. 171.651 1,998.514 ↓ 166.7 100,000 3 / 3

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

  • Sort Key: selected_transaction_labels."TransactionId", "TransactionLabels_2"."Label"
  • Sort Method: external merge Disk: 2448kB
  • Buffers: shared hit=66827 read=29289, temp read=4864 written=4882
20. 182.505 1,826.863 ↓ 166.7 100,000 3 / 3

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

  • Buffers: shared hit=66757 read=29289, temp read=3982 written=3997
21. 175.798 175.798 ↑ 1.2 100,000 3 / 3

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

  • Filter: ("TransactionLabels_2"."ClassifierId" = 'Decision'::text)
  • Buffers: shared hit=14233 read=9773
22. 81.850 1,468.560 ↓ 210.1 100,000 3 / 3

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

  • Buffers: shared hit=52502 read=19516, temp read=2880 written=3333
23. 432.298 1,386.710 ↓ 210.1 100,000 3 / 3

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

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

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

  • Sort Key: selected_transaction_labels."TransactionId"
  • Sort Method: external merge Disk: 7680kB
  • Buffers: shared hit=52502 read=19516, temp read=2880 written=2895
25. 481.950 481.950 ↓ 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.012..481.95 rows=300,000 loops=3)

  • Filter: (selected_transaction_labels."ClassifierId" = 'Decision'::text)
  • Buffers: shared hit=52502 read=19516
26. 1,809.437 3,217.053 ↑ 1.0 1,800,000 1

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

  • Buffers: shared hit=14041 read=9965, temp written=17722
27. 1,407.616 1,407.616 ↑ 1.0 1,800,000 1

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

  • Buffers: shared hit=14041 read=9965
28. 0.012 0.030 ↑ 154.0 10 1

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

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

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

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