explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c9od3

Settings
# exclusive inclusive rows x rows loops node
1. 33,406.417 83,722.290 ↓ 20,000.0 20,000 1

Nested Loop (cost=9,489.5..9,489.55 rows=1 width=104) (actual time=2,888.004..83,722.29 rows=20,000 loops=1)

  • Buffers: shared hit=984,382, temp read=10,061 written=14,560
2.          

CTE useablelabels

3. 161.594 1,094.685 ↓ 20,000.0 360,000 1

Nested Loop (cost=7,817.99..9,486.88 rows=18 width=54) (actual time=43.524..1,094.685 rows=360,000 loops=1)

  • Buffers: shared hit=984,382
4. 84.755 573.091 ↓ 20,000.0 360,000 1

Nested Loop (cost=7,817.84..9,483.82 rows=18 width=66) (actual time=43.515..573.091 rows=360,000 loops=1)

  • Buffers: shared hit=264,382
5. 48.404 68.336 ↓ 397.4 60,000 1

HashAggregate (cost=7,817.42..8,195.09 rows=151 width=16) (actual time=43.497..68.336 rows=60,000 loops=1)

  • Group Key: "TransactionLabels"."TransactionId", "TransactionLabels"."LabellerId"
  • Filter: (count(*) = 1)
  • Buffers: shared hit=5,233
6. 12.626 19.932 ↑ 1.0 60,000 1

Bitmap Heap Scan on TransactionLabels TransactionLabels (cost=1,742.54..7,366.34 rows=60,144 width=16) (actual time=7.872..19.932 rows=60,000 loops=1)

  • Heap Blocks: exact=4,872
  • Buffers: shared hit=5,233
7. 7.306 7.306 ↑ 1.0 60,000 1

Bitmap Index Scan on "IX_TransactionLabels_ClassifierId" (cost=0..1,727.5 rows=60,144 width=0) (actual time=7.306..7.306 rows=60,000 loops=1)

  • Index Cond: ("TransactionLabels"."ClassifierId" = 'decision_network'::text)
  • Buffers: shared hit=361
8. 420.000 420.000 ↑ 1.0 6 60,000

Index Scan using "IDX_TRANSACTIONID" on TransactionLabels transactionlabels (cost=0.42..8.46 rows=6 width=66) (actual time=0.003..0.007 rows=6 loops=60,000)

  • Index Cond: (transactionlabels."TransactionId" = "TransactionLabels"."TransactionId")
  • Filter: ("TransactionLabels"."LabellerId" = transactionlabels."LabellerId")
  • Buffers: shared hit=259,149
9. 360.000 360.000 ↑ 1.0 1 360,000

Index Scan using "IX_LabelSets_LabelSetId" on LabelSets labelsets (cost=0.15..0.17 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=360,000)

  • Index Cond: (labelsets."LabelSetId" = transactionlabels."LabelSetId")
  • Filter: labelsets."Completed"
  • Buffers: shared hit=720,000
10.          

CTE labelsused

11. 43.130 227.145 ↓ 60,000.0 60,000 1

WindowAgg (cost=0.45..0.47 rows=1 width=124) (actual time=174.483..227.145 rows=60,000 loops=1)

  • Buffers: temp read=3,887 written=1,058
12. 41.985 184.015 ↓ 60,000.0 60,000 1

Sort (cost=0.45..0.45 rows=1 width=116) (actual time=174.47..184.015 rows=60,000 loops=1)

  • Sort Key: useablelabels."TransactionId", useablelabels."Label"
  • Sort Method: external merge Disk: 4,592kB
  • Buffers: temp read=3,887 written=1,058
13. 32.557 142.030 ↓ 60,000.0 60,000 1

WindowAgg (cost=0.41..0.43 rows=1 width=116) (actual time=99.616..142.03 rows=60,000 loops=1)

  • Buffers: temp read=3,313 written=482
14. 44.688 109.473 ↓ 60,000.0 60,000 1

Sort (cost=0.41..0.42 rows=1 width=108) (actual time=99.606..109.473 rows=60,000 loops=1)

  • Sort Key: useablelabels."TransactionId"
  • Sort Method: external merge Disk: 3,832kB
  • Buffers: temp read=3,313 written=482
15. 64.785 64.785 ↓ 60,000.0 60,000 1

CTE Scan on useablelabels useablelabels (cost=0..0.4 rows=1 width=108) (actual time=0.018..64.785 rows=60,000 loops=1)

  • Filter: (useablelabels."ClassifierId" = 'decision_network'::text)
  • Buffers: temp read=2,834 written=1
16.          

CTE consensusvalues

17. 291.410 291.410 ↓ 60,000.0 60,000 1

CTE Scan on labelsused labelsused (cost=0..0.03 rows=1 width=156) (actual time=174.488..291.41 rows=60,000 loops=1)

  • Buffers: temp read=3,887 written=1,658
18.          

CTE transactionconsensus

19. 15.608 380.437 ↓ 20,000.0 20,000 1

Group (cost=0.03..0.05 rows=1 width=72) (actual time=357.463..380.437 rows=20,000 loops=1)

  • Group Key: consensusvalues."TransactionId", consensusvalues."CaseId", consensusvalues."Label", consensusvalues."Consensus"
  • Buffers: temp read=4,110 written=2,562
20. 27.202 364.829 ↓ 45,063.0 45,063 1

Sort (cost=0.03..0.04 rows=1 width=72) (actual time=357.461..364.829 rows=45,063 loops=1)

  • Sort Key: consensusvalues."TransactionId", consensusvalues."CaseId", consensusvalues."Label", consensusvalues."Consensus"
  • Sort Method: external merge Disk: 1,784kB
  • Buffers: temp read=4,110 written=2,562
21. 337.627 337.627 ↓ 45,063.0 45,063 1

CTE Scan on consensusvalues consensusvalues (cost=0..0.02 rows=1 width=72) (actual time=174.49..337.627 rows=45,063 loops=1)

  • Filter: (consensusvalues."Consensus" > 0.5)
  • Buffers: temp read=3,887 written=2,338
22.          

CTE duplicatetransactionsfromotherclassifiers

23. 223.945 2,310.123 ↓ 20,000.0 360,000 1

Sort (cost=1.56..1.61 rows=18 width=112) (actual time=2,251.791..2,310.123 rows=360,000 loops=1)

  • Sort Key: useablelabels_1."Id"
  • Sort Method: external merge Disk: 25,624kB
  • Buffers: shared hit=984,382, temp read=5,951 written=8,810
24. 283.097 2,086.178 ↓ 20,000.0 360,000 1

WindowAgg (cost=0.74..1.19 rows=18 width=112) (actual time=1,639.133..2,086.178 rows=360,000 loops=1)

  • Buffers: shared hit=984,382, temp read=2,748 written=5,593
25. 465.150 1,803.081 ↓ 20,000.0 360,000 1

Sort (cost=0.74..0.78 rows=18 width=104) (actual time=1,639.122..1,803.081 rows=360,000 loops=1)

  • Sort Key: useablelabels_1."TransactionId", useablelabels_1."Label", useablelabels_1."ClassifierId", useablelabels_1."LabellerId"
  • Sort Method: external merge Disk: 21,984kB
  • Buffers: shared hit=984,382, temp read=2,748 written=5,593
26. 1,337.931 1,337.931 ↓ 20,000.0 360,000 1

CTE Scan on useablelabels useablelabels_1 (cost=0..0.36 rows=18 width=104) (actual time=43.527..1,337.931 rows=360,000 loops=1)

  • Buffers: shared hit=984,382, temp written=2,833
27.          

CTE uniquetransactionidsfromotherclassifiers

28. 114.204 2,568.903 ↓ 20,000.0 20,000 1

HashAggregate (cost=0.46..0.47 rows=1 width=36) (actual time=2,512.404..2,568.903 rows=20,000 loops=1)

  • Group Key: t."TransactionId"
  • Buffers: shared hit=984,382, temp read=5,951 written=11,998
29. 2,454.699 2,454.699 ↓ 232,257.0 232,257 1

CTE Scan on duplicatetransactionsfromotherclassifiers t (cost=0..0.45 rows=1 width=8) (actual time=2,251.796..2,454.699 rows=232,257 loops=1)

  • Filter: ((t."ClassifierId" <> 'decision_network'::text) AND (t.duplicaterowcount = 1))
  • Buffers: shared hit=984,382, temp read=5,951 written=11,998
30. 2,595.873 2,595.873 ↓ 20,000.0 20,000 1

CTE Scan on uniquetransactionidsfromotherclassifiers uniquetransactionidsfromotherclassifiers (cost=0..0.02 rows=1 width=36) (actual time=2,512.406..2,595.873 rows=20,000 loops=1)

  • Buffers: shared hit=984,382, temp read=5,951 written=11,998
31. 47,720.000 47,720.000 ↓ 20,000.0 20,000 20,000

CTE Scan on transactionconsensus transactionconsensus (cost=0..0.02 rows=1 width=72) (actual time=0.018..2.386 rows=20,000 loops=20,000)

  • Buffers: temp read=4,110 written=2,562