explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GhVe

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

HashAggregate (cost=831,046.54..872,366.05 rows=1,464 width=144) (actual rows= loops=)

  • Group Key: ru.id, ru.code, ru.description, cust.national_fiscal_identity, cust.name, a.street, ru.latitude, ru.longitude, pay.reference, dis.amount, ru.longitude, a.external_number, a.internal_number, a.colony, string_agg(DISTINCT (p.phone_number)::text, (...)
2.          

CTE selected_recepcion_unit_list

3. 0.000 0.000 ↓ 0.0

Limit (cost=1.82..476.65 rows=20 width=16) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Unique (cost=1.82..968,303.43 rows=40,785 width=16) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.82..960,672.46 rows=3,052,388 width=16) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.26..91,200.44 rows=67,349 width=16) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.84..68,498.04 rows=40,785 width=32) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Index Scan using reception_units_pkey on reception_units ru_1 (cost=0.42..31,522.76 rows=40,785 width=48) (actual rows= loops=)

  • Filter: ((id <> 'db7ddcb0-255a-11e3-8224-0800200c9a66'::uuid) AND ((type)::text <> 'MOVILE'::text) AND (upper(((code)::character varying)::text) ~~ '%43%'::text))
9. 0.000 0.000 ↓ 0.0

Index Only Scan using customers_pkey on customers cust_1 (cost=0.42..0.90 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = ru_1.customer_id)
10. 0.000 0.000 ↓ 0.0

Index Only Scan using idx_customer_id on phones p_1 (cost=0.42..0.54 rows=2 width=16) (actual rows= loops=)

  • Index Cond: (customer_id = cust_1.id)
11. 0.000 0.000 ↓ 0.0

Index Only Scan using reception_units_distribution_centers_pkey on reception_units_distribution_centers rudc_1 (cost=0.56..11.97 rows=94 width=32) (actual rows= loops=)

  • Index Cond: (reception_unit_id = ru_1.id)
12. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=789,136.92..830,507.66 rows=1,464 width=144) (actual rows= loops=)

  • Group Key: ru.id, cust.national_fiscal_identity, cust.name, a.street, a.external_number, a.internal_number, a.colony, pay.reference, dis.amount
13. 0.000 0.000 ↓ 0.0

Sort (cost=789,136.92..789,140.58 rows=1,464 width=144) (actual rows= loops=)

  • Sort Key: ru.id, cust.national_fiscal_identity, cust.name, a.street, a.external_number, a.internal_number, a.colony, pay.reference, dis.amount
14. 0.000 0.000 ↓ 0.0

Hash Join (cost=102,307.74..789,059.94 rows=1,464 width=144) (actual rows= loops=)

  • Hash Cond: (ru.id = list.reception_unit_id)
15. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=102,307.09..731,812.36 rows=15,261,944 width=144) (actual rows= loops=)

  • Hash Cond: (rudc.reception_unit_id = ru.id)
16. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=3.35..293,885.71 rows=9,448,689 width=28) (actual rows= loops=)

  • Hash Cond: (rudc.distribution_center_id = dc.id)
17. 0.000 0.000 ↓ 0.0

Seq Scan on reception_units_distribution_centers rudc (cost=0.00..163,962.89 rows=9,448,689 width=32) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Hash (cost=2.60..2.60 rows=60 width=28) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Seq Scan on distribution_centers dc (cost=0.00..2.60 rows=60 width=28) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash (cost=91,516.46..91,516.46 rows=336,743 width=132) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=50,544.79..91,516.46 rows=336,743 width=132) (actual rows= loops=)

  • Hash Cond: (ru.customer_id = cust.id)
22. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=18,978.89..45,800.51 rows=203,925 width=111) (actual rows= loops=)

  • Hash Cond: (ru.address_id = a.id)
23. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=49.12..14,296.23 rows=203,925 width=92) (actual rows= loops=)

  • Hash Cond: (ru.discount_id = dis.id)
24. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2.40..11,445.53 rows=203,925 width=100) (actual rows= loops=)

  • Hash Cond: (ru.payment_condition_id = pay.id)
25. 0.000 0.000 ↓ 0.0

Seq Scan on reception_units ru (cost=0.00..8,639.17 rows=203,925 width=106) (actual rows= loops=)

  • Filter: ((id <> 'db7ddcb0-255a-11e3-8224-0800200c9a66'::uuid) AND ((type)::text <> 'MOVILE'::text))
26. 0.000 0.000 ↓ 0.0

Hash (cost=1.62..1.62 rows=62 width=26) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Seq Scan on payment_conditions pay (cost=0.00..1.62 rows=62 width=26) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Hash (cost=29.10..29.10 rows=1,410 width=24) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Seq Scan on discounts dis (cost=0.00..29.10 rows=1,410 width=24) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Hash (cost=10,177.01..10,177.01 rows=393,101 width=51) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Seq Scan on address a (cost=0.00..10,177.01 rows=393,101 width=51) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Hash (cost=24,746.89..24,746.89 rows=306,240 width=53) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=9,766.69..24,746.89 rows=306,240 width=53) (actual rows= loops=)

  • Hash Cond: (p.customer_id = cust.id)
34. 0.000 0.000 ↓ 0.0

Seq Scan on phones p (cost=0.00..5,549.40 rows=306,240 width=24) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Hash (cost=5,818.53..5,818.53 rows=185,453 width=45) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Seq Scan on customers cust (cost=0.00..5,818.53 rows=185,453 width=45) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Hash (cost=0.40..0.40 rows=20 width=16) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

CTE Scan on selected_recepcion_unit_list list (cost=0.00..0.40 rows=20 width=16) (actual rows= loops=)

39.          

SubPlan (forGroupAggregate)

40. 0.000 0.000 ↓ 0.0

Aggregate (cost=28.20..28.21 rows=1 width=8) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on orders o (cost=4.47..28.20 rows=1 width=8) (actual rows= loops=)

  • Recheck Cond: (reception_unit_id = ru.id)
  • Filter: (((order_type)::text = 'ORDER'::text) AND ((status)::text = ANY ('{NEW,NOTIFIED,STANDBY,REASIGNABLE}'::text[])))
42. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_reception_unit_id (cost=0.00..4.47 rows=6 width=0) (actual rows= loops=)

  • Index Cond: (reception_unit_id = ru.id)