explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6UKe

Settings
# exclusive inclusive rows x rows loops node
1. 2,415.905 28,185.464 ↓ 108.2 1,800,000 1

Unique (cost=369,438.44..369,812.59 rows=16,629 width=96) (actual time=24,593.92..28,185.464 rows=1,800,000 loops=1)

  • Buffers: shared hit=50050 read=38401, temp read=133685 written=133841
2. 4,769.849 25,769.559 ↓ 108.2 1,800,000 1

Sort (cost=369,438.44..369,480.01 rows=16,629 width=96) (actual time=24,593.917..25,769.559 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=50050 read=38401, temp read=133685 written=133841
3. 3,608.821 20,999.710 ↓ 108.2 1,800,000 1

Merge Join (cost=329,633.53..368,272.63 rows=16,629 width=96) (actual time=15,703.833..20,999.71 rows=1,800,000 loops=1)

  • Buffers: shared hit=50050 read=38401, temp read=95773 written=95855
4. 175.736 753.668 ↑ 1.5 100,000 1

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

  • Group Key: "TransactionLabels_1"."TransactionId", "TransactionLabels_1"."ClassifierId", "TransactionLabels_1"."LabelSetId"
  • Buffers: shared hit=4730 read=3604, temp read=577 written=579
5. 128.949 577.932 ↑ 2.4 102,506 1

Gather Merge (cost=48,754.42..80,309.11 rows=249,100 width=39) (actual time=281.55..577.932 rows=102,506 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=13481 read=10605, temp read=1660 written=1666
6. 110.550 448.983 ↑ 3.6 34,169 3 / 3

GroupAggregate (cost=47,754.39..50,556.77 rows=124,550 width=39) (actual time=273.625..448.983 rows=34,169 loops=3)

  • Group Key: "TransactionLabels_1"."TransactionId", "TransactionLabels_1"."ClassifierId", "TransactionLabels_1"."LabelSetId"
  • Buffers: shared hit=13481 read=10605, temp read=1660 written=1666
7. 167.932 338.433 ↑ 1.2 100,000 3 / 3

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

  • Sort Key: "TransactionLabels_1"."TransactionId", "TransactionLabels_1"."LabelSetId"
  • Sort Method: external merge Disk: 4616kB
  • Buffers: shared hit=13481 read=10605, temp read=1660 written=1666
8. 170.501 170.501 ↑ 1.2 100,000 3 / 3

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

  • Filter: ("TransactionLabels_1"."ClassifierId" = 'Decision'::text)
  • Buffers: shared hit=13401 read=10605
9. 3,585.858 16,637.221 ↓ 27,272.7 1,800,000 1

Sort (cost=280,879.11..280,879.28 rows=66 width=104) (actual time=15,422.255..16,637.221 rows=1,800,000 loops=1)

  • Sort Key: transaction_labels."TransactionId"
  • Sort Method: external sort Disk: 169136kB
  • Buffers: shared hit=45320 read=34797, temp read=95196 written=95276
10. 2,397.052 13,051.363 ↓ 27,272.7 1,800,000 1

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

  • Buffers: shared hit=45320 read=34797, temp read=29169 written=29175
11. 2,243.158 10,654.277 ↓ 27,272.7 1,800,000 1

Hash Join (cost=210,026.72..280,831.56 rows=66 width=116) (actual time=5,349.021..10,654.277 rows=1,800,000 loops=1)

  • Buffers: shared hit=45319 read=34797, temp read=29169 written=29175
12. 1,786.240 5,457.675 ↓ 2,752.3 300,000 1

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

  • Buffers: shared hit=31832 read=24278, temp read=9049 written=9055
13. 1,276.921 1,276.921 ↑ 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.059..1,276.921 rows=1,800,000 loops=1)

  • Buffers: shared hit=13546 read=10460
14. 86.020 2,394.514 ↓ 83.3 100,000 1

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

  • Buffers: shared hit=18286 read=13818, temp read=1627 written=1889
15. 115.618 2,308.494 ↓ 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,760.198..2,308.494 rows=100,000 loops=1)

  • Buffers: shared hit=18286 read=13818, temp read=1627 written=1633
16. 167.196 2,192.876 ↓ 83.3 100,000 1

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

  • Group Key: selected_transaction_labels."TransactionId", "TransactionLabels_2"."Label"
  • Buffers: shared hit=18286 read=13818, temp read=1627 written=1633
17. 103.377 2,025.680 ↓ 85.4 102,523 1

Gather Merge (cost=116,980.21..117,130.72 rows=1,200 width=25) (actual time=1,760.177..2,025.68 rows=102,523 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=57795 read=38321, temp read=4863 written=4881
18. 101.098 1,922.303 ↓ 57.0 34,174 3 / 3

GroupAggregate (cost=115,980.19..115,992.19 rows=600 width=25) (actual time=1,757.159..1,922.303 rows=34,174 loops=3)

  • Group Key: selected_transaction_labels."TransactionId", "TransactionLabels_2"."Label"
  • Buffers: shared hit=57795 read=38321, temp read=4863 written=4881
19. 158.665 1,821.205 ↓ 166.7 100,000 3 / 3

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

  • Sort Key: selected_transaction_labels."TransactionId", "TransactionLabels_2"."Label"
  • Sort Method: external merge Disk: 2376kB
  • Buffers: shared hit=57795 read=38321, temp read=4863 written=4881
20. 161.852 1,662.540 ↓ 166.7 100,000 3 / 3

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

  • Buffers: shared hit=57725 read=38321, temp read=3982 written=3997
21. 143.035 143.035 ↑ 1.2 100,000 3 / 3

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

  • Filter: ("TransactionLabels_2"."ClassifierId" = 'Decision'::text)
  • Buffers: shared hit=13451 read=10555
22. 81.667 1,357.653 ↓ 210.1 100,000 3 / 3

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

  • Buffers: shared hit=44254 read=27764, temp read=2880 written=3333
23. 406.107 1,275.986 ↓ 210.1 100,000 3 / 3

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

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

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

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

  • Filter: (selected_transaction_labels."ClassifierId" = 'Decision'::text)
  • Buffers: shared hit=44254 read=27764
26. 1,657.262 2,953.444 ↑ 1.0 1,800,000 1

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

  • Buffers: shared hit=13487 read=10519, temp written=17722
27. 1,296.182 1,296.182 ↑ 1.0 1,800,000 1

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

  • Buffers: shared hit=13487 read=10519
28. 0.011 0.034 ↑ 154.0 10 1

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

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

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

  • Filter: labelsets."Completed"
  • Buffers: shared hit=1
Planning time : 1.312 ms
Execution time : 29,240.409 ms