explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GWed : demo

Settings
# exclusive inclusive rows x rows loops node
1. 36.744 28,940.259 ↓ 1.8 33,662 1

Sort (cost=5,630,512.23..5,630,557.91 rows=18,272 width=16) (actual time=28,929.964..28,940.259 rows=33,662 loops=1)

  • Sort Key: o.create_time
  • Sort Method: quicksort Memory: 2,944kB
2. 28.797 28,903.515 ↓ 1.8 33,662 1

Hash Left Join (cost=152.64..5,629,218.81 rows=18,272 width=16) (actual time=22.473..28,903.515 rows=33,662 loops=1)

  • Hash Cond: (gm.group_membership_id = gmi.group_membership_id)
3. 31.786 28,872.522 ↓ 2.2 19,926 1

Nested Loop Left Join (cost=46.24..5,628,477.94 rows=9,035 width=20) (actual time=20.268..28,872.522 rows=19,926 loops=1)

4. 26.732 28,820.810 ↓ 2.2 19,926 1

Nested Loop Left Join (cost=45.96..5,625,549.56 rows=9,035 width=20) (actual time=20.264..28,820.810 rows=19,926 loops=1)

5. 20.811 28,754.226 ↓ 2.2 19,926 1

Hash Left Join (cost=45.68..5,622,646.57 rows=9,035 width=20) (actual time=20.257..28,754.226 rows=19,926 loops=1)

  • Hash Cond: (gmr.group_membership_request_id = gmri.group_membership_request_id)
6. 27.065 28,732.449 ↓ 2.1 18,993 1

Nested Loop Left Join (cost=0.85..5,622,464.94 rows=9,035 width=24) (actual time=19.276..28,732.449 rows=18,993 loops=1)

7. 36.760 28,667.398 ↓ 2.1 18,993 1

Nested Loop Left Join (cost=0.57..5,619,554.18 rows=9,035 width=20) (actual time=19.272..28,667.398 rows=18,993 loops=1)

8. 63.024 28,565.550 ↓ 2.0 10,848 1

Nested Loop Left Join (cost=0.29..5,617,296.86 rows=5,470 width=16) (actual time=19.265..28,565.550 rows=10,848 loops=1)

  • Filter: ((o.party_id = 747,000) OR (i.client_id = 747,000) OR (SubPlan 1))
9. 4.176 4.176 ↑ 1.0 7,926 1

Seq Scan on "order" o (cost=0.00..154.26 rows=7,926 width=24) (actual time=0.001..4.176 rows=7,926 loops=1)

10. 23.778 23.778 ↑ 1.0 1 7,926

Index Only Scan using invoice_order_id_idx on invoice i (cost=0.29..0.32 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=7,926)

  • Index Cond: (order_id = o.order_id)
  • Heap Fetches: 0
11.          

SubPlan (for Nested Loop Left Join)

12. 17,361.479 28,474.572 ↑ 1.0 1 10,794

Hash Join (cost=619.22..708.36 rows=1 width=0) (actual time=2.638..2.638 rows=1 loops=10,794)

  • Hash Cond: (gmi_1.group_membership_id = gm_1.group_membership_id)
  • Join Filter: (((oi_1.order_id = o.order_id) AND (poi.beneficiary_person_id = 747,000)) OR ((gmri_1.beneficiary_client_id = 747,000) AND (o.order_status_id = ANY ('{1,3,4,6}'::integer[]))) OR ((gmi_1.client_id = 747,000) AND gmi_1.active AND (o.order_status_id = ANY ('{5,7}'::integer[]))))
  • Rows Removed by Join Filter: 2,318
13. 11,096.232 11,096.232 ↑ 1.0 3,375 10,794

Seq Scan on group_membership_item gmi_1 (cost=0.00..63.40 rows=3,440 width=9) (actual time=0.001..1.028 rows=3,375 loops=10,794)

14. 0.575 16.861 ↓ 4.7 1,092 1

Hash (cost=616.33..616.33 rows=231 width=16) (actual time=16.861..16.861 rows=1,092 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 52kB
15. 1.130 16.286 ↓ 4.7 1,092 1

Nested Loop (cost=285.60..616.33 rows=231 width=16) (actual time=7.944..16.286 rows=1,092 loops=1)

16. 1.974 12.972 ↓ 5.4 1,092 1

Nested Loop (cost=285.31..545.15 rows=201 width=24) (actual time=7.931..12.972 rows=1,092 loops=1)

  • Join Filter: (gmr_1.order_item_id = oi_1.order_item_id)
17. 1.443 9.906 ↓ 1.7 1,092 1

Hash Join (cost=285.03..322.60 rows=629 width=16) (actual time=7.920..9.906 rows=1,092 loops=1)

  • Hash Cond: (gmri_1.group_membership_request_id = gmr_1.group_membership_request_id)
18. 0.553 0.553 ↑ 1.0 1,548 1

Seq Scan on group_membership_request_item gmri_1 (cost=0.00..25.48 rows=1,548 width=8) (actual time=0.001..0.553 rows=1,548 loops=1)

19. 0.621 7.910 ↓ 1.4 1,701 1

Hash (cost=269.73..269.73 rows=1,224 width=16) (actual time=7.910..7.910 rows=1,701 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 80kB
20. 1.586 7.289 ↓ 1.4 1,701 1

Hash Join (cost=175.32..269.73 rows=1,224 width=16) (actual time=4.856..7.289 rows=1,701 loops=1)

  • Hash Cond: (gmr_1.order_item_id = csoi_1.order_item_id)
21. 0.855 0.855 ↓ 1.0 3,015 1

Seq Scan on group_membership_request gmr_1 (cost=0.00..67.11 rows=3,011 width=8) (actual time=0.002..0.855 rows=3,015 loops=1)

22. 0.582 4.848 ↑ 1.0 1,701 1

Hash (cost=154.06..154.06 rows=1,701 width=8) (actual time=4.848..4.848 rows=1,701 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 67kB
23. 1.949 4.266 ↑ 1.0 1,701 1

Hash Join (cost=51.27..154.06 rows=1,701 width=8) (actual time=1.821..4.266 rows=1,701 loops=1)

  • Hash Cond: (csoi_1.client_service_id = gm_1.client_service_id)
24. 1.177 1.177 ↑ 1.0 4,185 1

Seq Scan on client_service_to_order_item csoi_1 (cost=0.00..64.85 rows=4,185 width=8) (actual time=0.001..1.177 rows=4,185 loops=1)

25. 0.560 1.140 ↑ 1.0 1,701 1

Hash (cost=30.01..30.01 rows=1,701 width=8) (actual time=1.140..1.140 rows=1,701 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 67kB
26. 0.580 0.580 ↑ 1.0 1,701 1

Seq Scan on group_membership gm_1 (cost=0.00..30.01 rows=1,701 width=8) (actual time=0.001..0.580 rows=1,701 loops=1)

27. 1.092 1.092 ↑ 1.0 1 1,092

Index Only Scan using order_item_order_item_id_idx on order_item oi_1 (cost=0.29..0.34 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1,092)

  • Index Cond: (order_item_id = csoi_1.order_item_id)
  • Heap Fetches: 0
28. 2.184 2.184 ↑ 1.0 1 1,092

Index Scan using product_order_item_pkey on product_order_item poi (cost=0.29..0.34 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1,092)

  • Index Cond: (order_item_id = oi_1.order_item_id)
29. 65.088 65.088 ↑ 1.0 2 10,848

Index Scan using order_item_order_id_idx on order_item oi (cost=0.29..0.39 rows=2 width=8) (actual time=0.005..0.006 rows=2 loops=10,848)

  • Index Cond: (o.order_id = order_id)
30. 37.986 37.986 ↓ 0.0 0 18,993

Index Scan using group_membership_request_order_item_id_ukey on group_membership_request gmr (cost=0.28..0.31 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=18,993)

  • Index Cond: (order_item_id = oi.order_item_id)
31. 0.504 0.966 ↑ 1.0 1,548 1

Hash (cost=25.48..25.48 rows=1,548 width=4) (actual time=0.966..0.966 rows=1,548 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 55kB
32. 0.462 0.462 ↑ 1.0 1,548 1

Seq Scan on group_membership_request_item gmri (cost=0.00..25.48 rows=1,548 width=4) (actual time=0.002..0.462 rows=1,548 loops=1)

33. 39.852 39.852 ↓ 0.0 0 19,926

Index Scan using client_service_to_order_item_order_item_id_ukey on client_service_to_order_item csoi (cost=0.28..0.31 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=19,926)

  • Index Cond: (order_item_id = oi.order_item_id)
34. 19.926 19.926 ↓ 0.0 0 19,926

Index Scan using group_membership_client_service_id_ukey on group_membership gm (cost=0.28..0.31 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=19,926)

  • Index Cond: (client_service_id = csoi.client_service_id)
35. 1.158 2.196 ↑ 1.0 3,440 1

Hash (cost=63.40..63.40 rows=3,440 width=4) (actual time=2.196..2.196 rows=3,440 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 121kB
36. 1.038 1.038 ↑ 1.0 3,440 1

Seq Scan on group_membership_item gmi (cost=0.00..63.40 rows=3,440 width=4) (actual time=0.001..1.038 rows=3,440 loops=1)