explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lNTU

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

Hash Join (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

Seq Scan on TransactionLabels tl_1 (cost=0..0 rows=0 width=0) (actual rows=360,000 loops=1)

4.          

CTE tl_with_counts

5. 0.000 0.000 ↓ 0.0 60,000 1

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

6. 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
7. 0.000 0.000 ↓ 0.0 60,000 1

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

8. 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
9. 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)
10.          

CTE tl_with_consensus

11. 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)

12.          

CTE tl_consensus_filtered

13. 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)
14.          

CTE consensus

15. 0.000 0.000 ↓ 0.0 20,000 1

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

  • Sort Key: tl_consensus_filtered."TransactionId"
  • Sort Method: quicksort Memory: 2,796kB
16. 0.000 0.000 ↓ 0.0 20,000 1

HashAggregate (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"
17. 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)

18.          

CTE tl_for_other_classifiers

19. 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
20. 0.000 0.000 ↓ 0.0 225,315 1

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

21. 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
22. 0.000 0.000 ↓ 0.0 225,315 1

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

23. 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)

24. 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"))
25.          

CTE tl_for_other_classifiers_unique_aggregated

26. 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"
27. 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
28. 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")
29. 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)

30. 0.000 0.000 ↓ 0.0 20,000 1

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

31. 0.000 0.000 ↓ 0.0 20,000 1

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