explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DjM

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0 20,000 1

Nested Loop (cost=0..0 rows=0 width=0) (actual rows=20,000 loops=1)

2.          

CTE tl_useable

3. 0.000 0.000 ↓ 0.0 360,000 1

Nested Loop (cost=0..0 rows=0 width=0) (actual rows=360,000 loops=1)

4. 0.000 0.000 ↓ 0.0 360,000 1

Nested Loop (cost=0..0 rows=0 width=0) (actual rows=360,000 loops=1)

5. 0.000 0.000 ↓ 0.0 60,000 1

HashAggregate (cost=0..0 rows=0 width=0) (actual rows=60,000 loops=1)

  • Group Key: "TransactionLabels"."TransactionId", "TransactionLabels"."LabellerId"
  • Filter: (count(*) = 1)
6. 0.000 0.000 ↓ 0.0 60,000 1

Bitmap Heap Scan on TransactionLabels TransactionLabels (cost=0..0 rows=0 width=0) (actual rows=60,000 loops=1)

  • Heap Blocks: exact=4,872
7. 0.000 0.000 ↓ 0.0 60,000 1

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

  • Index Cond: ("ClassifierId" = 'decision_network'::text)
8. 0.000 0.000 ↓ 0.0 6 60,000

Index Scan using "IDX_TRANSACTIONID" on TransactionLabels tl_1 (cost=0..0 rows=0 width=0) (actual rows=6 loops=60,000)

  • Index Cond: ("TransactionId" = "TransactionLabels"."TransactionId")
  • Filter: ("TransactionLabels"."LabellerId" = "LabellerId")
9. 0.000 0.000 ↓ 0.0 1 360,000

Index Scan using "IX_LabelSets_LabelSetId" on LabelSets label_sets (cost=0..0 rows=0 width=0) (actual rows=1 loops=360,000)

  • Index Cond: ("LabelSetId" = tl_1."LabelSetId")
  • Filter: "Completed"
10.          

CTE tl_with_counts

11. 0.000 0.000 ↓ 0.0 60,000 1

WindowAgg (cost=0..0 rows=0 width=0) (actual rows=60,000 loops=1)

12. 0.000 0.000 ↓ 0.0 60,000 1

Sort (cost=0..0 rows=0 width=0) (actual rows=60,000 loops=1)

  • Sort Key: tl_useable."TransactionId", tl_useable."Label"
  • Sort Method: external merge Disk: 5,288kB
13. 0.000 0.000 ↓ 0.0 60,000 1

WindowAgg (cost=0..0 rows=0 width=0) (actual rows=60,000 loops=1)

14. 0.000 0.000 ↓ 0.0 60,000 1

Sort (cost=0..0 rows=0 width=0) (actual rows=60,000 loops=1)

  • Sort Key: tl_useable."TransactionId"
  • Sort Method: external merge Disk: 4,536kB
15. 0.000 0.000 ↓ 0.0 60,000 1

CTE Scan on tl_useable tl_useable (cost=0..0 rows=0 width=0) (actual rows=60,000 loops=1)

  • Filter: ("ClassifierId" = 'decision_network'::text)
16.          

CTE tl_with_consensus

17. 0.000 0.000 ↓ 0.0 60,000 1

CTE Scan on tl_with_counts tl_with_counts (cost=0..0 rows=0 width=0) (actual rows=60,000 loops=1)

18.          

CTE tl_consensus_filtered

19. 0.000 0.000 ↓ 0.0 45,063 1

CTE Scan on tl_with_consensus tl_with_consensus (cost=0..0 rows=0 width=0) (actual rows=45,063 loops=1)

  • Filter: ("Consensus" > 0.5)
20.          

CTE consensus

21. 0.000 0.000 ↓ 0.0 20,000 1

Group (cost=0..0 rows=0 width=0) (actual rows=20,000 loops=1)

  • Group Key: tl_consensus_filtered."TransactionId", tl_consensus_filtered."CaseId", tl_consensus_filtered."Label", tl_consensus_filtered."ClassifierId", tl_consensus_filtered."Consensus"
22. 0.000 0.000 ↓ 0.0 45,063 1

Sort (cost=0..0 rows=0 width=0) (actual rows=45,063 loops=1)

  • Sort Key: tl_consensus_filtered."TransactionId", tl_consensus_filtered."CaseId", tl_consensus_filtered."Label", tl_consensus_filtered."ClassifierId", tl_consensus_filtered."Consensus"
  • Sort Method: external merge Disk: 2,528kB
23. 0.000 0.000 ↓ 0.0 45,063 1

CTE Scan on tl_consensus_filtered tl_consensus_filtered (cost=0..0 rows=0 width=0) (actual rows=45,063 loops=1)

24.          

CTE tl_for_other_classifiers

25. 0.000 0.000 ↓ 0.0 225,315 1

Sort (cost=0..0 rows=0 width=0) (actual rows=225,315 loops=1)

  • Sort Key: tl_2."Id"
  • Sort Method: external merge Disk: 13,656kB
26. 0.000 0.000 ↓ 0.0 225,315 1

WindowAgg (cost=0..0 rows=0 width=0) (actual rows=225,315 loops=1)

27. 0.000 0.000 ↓ 0.0 225,315 1

Sort (cost=0..0 rows=0 width=0) (actual rows=225,315 loops=1)

  • Sort Key: tl_2."TransactionId", tl_2."Label", tl_2."ClassifierId", tl_2."LabellerId"
  • Sort Method: external merge Disk: 13,856kB
28. 0.000 0.000 ↓ 0.0 225,315 1

Nested Loop (cost=0..0 rows=0 width=0) (actual rows=225,315 loops=1)

29. 0.000 0.000 ↓ 0.0 45,063 1

CTE Scan on tl_consensus_filtered tl_consensus_filtered_1 (cost=0..0 rows=0 width=0) (actual rows=45,063 loops=1)

30. 0.000 0.000 ↓ 0.0 5 45,063

Index Scan using "IDX_TRANSACTIONID" on TransactionLabels tl_2 (cost=0..0 rows=0 width=0) (actual rows=5 loops=45,063)

  • Index Cond: ("TransactionId" = tl_consensus_filtered_1."TransactionId")
  • Filter: (("ClassifierId" <> 'decision_network'::text) AND (tl_consensus_filtered_1."LabellerId" = "LabellerId") AND (tl_consensus_filtered_1."LabelSetId" = "LabelSetId"))
31.          

CTE tl_for_other_classifiers_unique_aggregated

32. 0.000 0.000 ↓ 0.0 20,000 1

GroupAggregate (cost=0..0 rows=0 width=0) (actual rows=20,000 loops=1)

  • Group Key: tl_3."TransactionId"
33. 0.000 0.000 ↓ 0.0 188,651 1

Sort (cost=0..0 rows=0 width=0) (actual rows=188,651 loops=1)

  • Sort Key: tl_3."TransactionId"
  • Sort Method: external merge Disk: 3,344kB
34. 0.000 0.000 ↓ 0.0 188,651 1

CTE Scan on tl_for_other_classifiers tl_3 (cost=0..0 rows=0 width=0) (actual rows=188,651 loops=1)

  • Filter: (NOT "IsDuplicateRow")
35. 0.000 0.000 ↓ 0.0 20,000 1

CTE Scan on consensus consensus (cost=0..0 rows=0 width=0) (actual rows=20,000 loops=1)

36. 0.000 0.000 ↓ 0.0 20,000 20,000

CTE Scan on tl_for_other_classifiers_unique_aggregated tl (cost=0..0 rows=0 width=0) (actual rows=20,000 loops=20,000)