explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dkwK

Settings

Optimization(s) for this plan:

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

HashAggregate (cost=1,107,405.34..1,107,448.03 rows=3,415 width=682) (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 sel_recepcion_unit

3. 0.000 0.000 ↓ 0.0

Limit (cost=1,092,458.99..1,092,496.41 rows=20 width=106) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Unique (cost=1,092,458.99..1,168,768.69 rows=40,785 width=106) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Sort (cost=1,092,458.99..1,100,089.96 rows=3,052,388 width=106) (actual rows= loops=)

  • Sort Key: ru_1.id, ru_1.code, ru_1.description, ru_1.latitude, ru_1.longitude, ru_1.customer_id, ru_1.payment_condition_id, ru_1.address_id, ru_1.discount_id
6. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=36,937.40..408,968.81 rows=3,052,388 width=106) (actual rows= loops=)

  • Hash Cond: (rudc_1.reception_unit_id = ru_1.id)
7. 0.000 0.000 ↓ 0.0

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

8. 0.000 0.000 ↓ 0.0

Hash (cost=34,976.54..34,976.54 rows=67,349 width=106) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=23,856.25..34,976.54 rows=67,349 width=106) (actual rows= loops=)

  • Hash Cond: (p_1.customer_id = cust_1.id)
10. 0.000 0.000 ↓ 0.0

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

11. 0.000 0.000 ↓ 0.0

Hash (cost=22,589.44..22,589.44 rows=40,785 width=122) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=9,042.69..22,589.44 rows=40,785 width=122) (actual rows= loops=)

  • Hash Cond: (ru_1.customer_id = cust_1.id)
13. 0.000 0.000 ↓ 0.0

Seq Scan on reception_units ru_1 (cost=0.00..10,723.95 rows=40,785 width=106) (actual rows= loops=)

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

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

15. 0.000 0.000 ↓ 0.0

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

16.          

CTE sum_total_orders_pending

17. 0.000 0.000 ↓ 0.0

HashAggregate (cost=564.58..564.60 rows=2 width=24) (actual rows= loops=)

  • Group Key: o.reception_unit_id
18. 0.000 0.000 ↓ 0.0

Nested Loop (cost=4.47..564.57 rows=2 width=24) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

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

20. 0.000 0.000 ↓ 0.0

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

  • Recheck Cond: (reception_unit_id = ru_2.id)
  • Filter: (((order_type)::text = 'ORDER'::text) AND ((status)::text = ANY ('{NEW,NOTIFIED,STANDBY,REASIGNABLE}'::text[])))
21. 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_2.id)
22. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=13,994.29..14,199.19 rows=3,415 width=682) (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, a.external_number, a.internal_number, a.colony, st.total
23. 0.000 0.000 ↓ 0.0

Sort (cost=13,994.29..14,002.83 rows=3,415 width=682) (actual rows= loops=)

  • Sort Key: ru.id, ru.code, ru.description, cust.national_fiscal_identity, cust.name, a.street, ru.latitude, ru.longitude, pay.reference, dis.amount, a.external_number, a.internal_number, a.colony, st.total
24. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=16.94..13,793.87 rows=3,415 width=682) (actual rows= loops=)

  • Hash Cond: (ru.id = st.reception_unit_id)
25. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=16.87..13,777.58 rows=3,415 width=674) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=8.23..621.75 rows=36 width=662) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=3.79..375.49 rows=20 width=670) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=3.37..206.49 rows=20 width=651) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=2.95..37.54 rows=20 width=622) (actual rows= loops=)

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

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

31. 0.000 0.000 ↓ 0.0

Hash (cost=2.70..2.70 rows=20 width=630) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=0.65..2.70 rows=20 width=630) (actual rows= loops=)

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

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

34. 0.000 0.000 ↓ 0.0

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

35. 0.000 0.000 ↓ 0.0

CTE Scan on sel_recepcion_unit ru (cost=0.00..0.40 rows=20 width=636) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Index Scan using customers_pkey on customers cust (cost=0.42..8.44 rows=1 width=45) (actual rows= loops=)

  • Index Cond: (id = ru.customer_id)
37. 0.000 0.000 ↓ 0.0

Index Scan using address_pkey on address a (cost=0.42..8.44 rows=1 width=51) (actual rows= loops=)

  • Index Cond: (ru.address_id = id)
38. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on phones p (cost=4.44..12.29 rows=2 width=24) (actual rows= loops=)

  • Recheck Cond: (customer_id = ru.customer_id)
39. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_customer_id (cost=0.00..4.44 rows=2 width=0) (actual rows= loops=)

  • Index Cond: (customer_id = ru.customer_id)
40. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=8.64..372.90 rows=94 width=28) (actual rows= loops=)

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

Bitmap Heap Scan on reception_units_distribution_centers rudc (cost=5.29..368.26 rows=94 width=32) (actual rows= loops=)

  • Recheck Cond: (ru.id = reception_unit_id)
42. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on reception_units_distribution_centers_pkey (cost=0.00..5.26 rows=94 width=0) (actual rows= loops=)

  • Index Cond: (ru.id = reception_unit_id)
43. 0.000 0.000 ↓ 0.0

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

44. 0.000 0.000 ↓ 0.0

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

45. 0.000 0.000 ↓ 0.0

Hash (cost=0.04..0.04 rows=2 width=24) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

CTE Scan on sum_total_orders_pending st (cost=0.00..0.04 rows=2 width=24) (actual rows= loops=)