explain.depesz.com

PostgreSQL's explain analyze made readable

Result: un21

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 96,219.235 ↑ 1.0 1 1

Aggregate (cost=389,761.96..389,761.97 rows=1 width=8) (actual time=96,219.235..96,219.235 rows=1 loops=1)

2. 7.338 96,219.230 ↑ 1,372.0 3 1

Nested Loop Semi Join (cost=1.00..389,751.67 rows=4,116 width=0) (actual time=91,913.351..96,219.230 rows=3 loops=1)

3. 41.751 53.900 ↓ 2.0 8,232 1

Index Scan using line_item_client_id_indx on base_invoice bin (cost=0.29..386,434.10 rows=4,116 width=16) (actual time=10.545..53.900 rows=8,232 loops=1)

  • Index Cond: (client_id = 1005)
  • Filter: ((NOT deleted) AND (NOT saved_as_draft) AND COALESCE(NULLIF((alternatives: SubPlan 1 or hashed SubPlan 2), false), NULLIF((alternatives: SubPlan 3 or hashed SubPlan 4), false), NULLIF((alternatives: SubPlan 5 or hashed SubPlan 6), false), NULLIF((alternatives: SubPlan 7 or hashed SubPlan 8), false)))
  • Rows Removed by Filter: 133
4.          

SubPlan (forIndex Scan)

5. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..9.56 rows=1 width=0) (never executed)

  • Join Filter: (sda.data_id = ed.id)
6. 0.000 0.000 ↓ 0.0 0

Seq Scan on secondary_data_access sda (cost=0.00..1.11 rows=1 width=4) (never executed)

  • Filter: (user_id = 1257)
7. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_entity_data_entity_type_id_entity_id on entity_data ed (cost=0.42..8.44 rows=1 width=4) (never executed)

  • Index Cond: ((bin.entity_type_id = entity_type_id) AND (bin.entity_id = entity_id))
8. 0.001 0.008 ↓ 0.0 0 1

Nested Loop (cost=0.42..9.55 rows=1 width=8) (actual time=0.007..0.008 rows=0 loops=1)

9. 0.007 0.007 ↓ 0.0 0 1

Seq Scan on secondary_data_access sda_1 (cost=0.00..1.11 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=1)

  • Filter: (user_id = 1257)
  • Rows Removed by Filter: 9
10. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_entity_data on entity_data ed_1 (cost=0.42..8.44 rows=1 width=12) (never executed)

  • Index Cond: (id = sda_1.data_id)
11. 0.000 0.000 ↓ 0.0 0

Index Scan using user_data_read_access_1 on user_data_read_access udr (cost=0.42..8.28 rows=1 width=0) (never executed)

  • Index Cond: ((user_id = 1257) AND (entity_type_id = 67))
  • Filter: (entity_id = bin.id)
12. 0.007 0.007 ↓ 0.0 0 1

Index Scan using user_data_read_access_1 on user_data_read_access udr_1 (cost=0.42..8.28 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: ((user_id = 1257) AND (entity_type_id = 67))
13. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.71..16.82 rows=1 width=0) (never executed)

14. 0.000 0.000 ↓ 0.0 0

Index Scan using base_invoice_pkey on base_invoice et (cost=0.29..8.30 rows=1 width=4) (never executed)

  • Index Cond: (id = bin.id)
15. 0.000 0.000 ↓ 0.0 0

Index Only Scan using user_data_read_access_uk1 on user_data_read_access udr_2 (cost=0.42..8.45 rows=1 width=4) (never executed)

  • Index Cond: ((user_id = 1257) AND (entity_type_id = 61) AND (entity_id = et.contract_id))
  • Heap Fetches: 0
16. 5.510 7.571 ↓ 6.6 8,247 1

Hash Join (cost=15.74..880.62 rows=1,252 width=4) (actual time=0.415..7.571 rows=8,247 loops=1)

  • Hash Cond: (et_1.contract_id = udr_3.entity_id)
17. 1.657 1.657 ↑ 1.0 8,460 1

Seq Scan on base_invoice et_1 (cost=0.00..842.60 rows=8,460 width=8) (actual time=0.003..1.657 rows=8,460 loops=1)

18. 0.163 0.404 ↓ 4.3 971 1

Hash (cost=12.93..12.93 rows=225 width=4) (actual time=0.404..0.404 rows=971 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 43kB
19. 0.241 0.241 ↓ 4.3 971 1

Index Only Scan using user_data_read_access_uk1 on user_data_read_access udr_3 (cost=0.42..12.93 rows=225 width=4) (actual time=0.020..0.241 rows=971 loops=1)

  • Index Cond: ((user_id = 1257) AND (entity_type_id = 61))
  • Heap Fetches: 1
20. 0.000 0.000 ↓ 0.0 0

Merge Join (cost=8.98..11.38 rows=1 width=0) (never executed)

  • Merge Cond: (et_2.relation_id = udr_4.entity_id)
21. 0.000 0.000 ↓ 0.0 0

Sort (cost=8.31..8.32 rows=1 width=4) (never executed)

  • Sort Key: et_2.relation_id
22. 0.000 0.000 ↓ 0.0 0

Index Scan using base_invoice_pkey on base_invoice et_2 (cost=0.29..8.30 rows=1 width=4) (never executed)

  • Index Cond: (id = bin.id)
23. 0.000 0.000 ↓ 0.0 0

Index Only Scan using user_data_read_access_uk1 on user_data_read_access udr_4 (cost=0.42..9.17 rows=37 width=4) (never executed)

  • Index Cond: ((user_id = 1257) AND (entity_type_id = 1))
  • Heap Fetches: 0
24. 3.427 4.563 ↓ 45.6 8,353 1

Hash Join (cost=9.63..874.50 rows=183 width=4) (actual time=0.091..4.563 rows=8,353 loops=1)

  • Hash Cond: (et_3.relation_id = udr_5.entity_id)
25. 1.060 1.060 ↑ 1.0 8,460 1

Seq Scan on base_invoice et_3 (cost=0.00..842.60 rows=8,460 width=8) (actual time=0.005..1.060 rows=8,460 loops=1)

26. 0.031 0.076 ↓ 7.1 262 1

Hash (cost=9.17..9.17 rows=37 width=4) (actual time=0.076..0.076 rows=262 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
27. 0.045 0.045 ↓ 7.1 262 1

Index Only Scan using user_data_read_access_uk1 on user_data_read_access udr_5 (cost=0.42..9.17 rows=37 width=4) (actual time=0.011..0.045 rows=262 loops=1)

  • Index Cond: ((user_id = 1257) AND (entity_type_id = 1))
  • Heap Fetches: 1
28. 0.000 96,157.992 ↓ 0.0 0 8,232

Nested Loop (cost=0.72..197.94 rows=19,070 width=4) (actual time=11.681..11.681 rows=0 loops=8,232)

29. 353.976 353.976 ↓ 1.2 220 8,232

Index Only Scan using idx_work_flow_entity_lead_time_1 on work_flow_entity_lead_time wfelt (cost=0.43..7.68 rows=182 width=8) (actual time=0.008..0.043 rows=220 loops=8,232)

  • Index Cond: ((entity_type_id = 67) AND (entity_id = bin.id))
  • Heap Fetches: 0
30. 95,841.967 95,841.967 ↓ 0.0 0 1,808,339

Index Scan using work_flow_task_lead_time__index on work_flow_task_lead_time wftlt (cost=0.29..0.91 rows=14 width=4) (actual time=0.053..0.053 rows=0 loops=1,808,339)

  • Index Cond: (lead_time_id = wfelt.lead_time_id)
  • Filter: (lead_time_type = 3)
  • Rows Removed by Filter: 263