explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qlDO

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 456.364 ↑ 171.0 1 1

HashAggregate (cost=12,808.07..12,810.21 rows=171 width=682) (actual time=456.363..456.364 rows=1 loops=1)

  • 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.002 454.958 ↑ 1.0 1 1

Limit (cost=11,967.45..11,967.46 rows=1 width=106) (actual time=454.957..454.958 rows=1 loops=1)

4. 0.015 454.956 ↑ 1.0 1 1

HashAggregate (cost=11,967.45..11,967.46 rows=1 width=106) (actual time=454.956..454.956 rows=1 loops=1)

  • Group 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
5. 0.005 454.941 ↓ 2.0 2 1

Nested Loop (cost=1.83..11,967.43 rows=1 width=106) (actual time=367.845..454.941 rows=2 loops=1)

6. 0.102 0.102 ↑ 1.0 1 1

Seq Scan on distribution_centers dc_1 (cost=0.00..2.90 rows=1 width=16) (actual time=0.073..0.102 rows=1 loops=1)

  • Filter: (upper((name)::text) ~~ '%ABASOLO%'::text)
  • Rows Removed by Filter: 59
7. 0.005 454.834 ↓ 2.0 2 1

Nested Loop (cost=1.83..11,964.52 rows=1 width=122) (actual time=367.767..454.834 rows=2 loops=1)

8. 0.003 454.807 ↓ 2.0 2 1

Nested Loop (cost=1.27..11,951.91 rows=1 width=106) (actual time=367.748..454.807 rows=2 loops=1)

9. 0.004 454.786 ↑ 1.0 1 1

Nested Loop (cost=0.84..11,943.95 rows=1 width=122) (actual time=367.730..454.786 rows=1 loops=1)

10. 0.006 454.757 ↑ 1.0 1 1

Nested Loop (cost=0.42..11,935.49 rows=1 width=122) (actual time=367.703..454.757 rows=1 loops=1)

11. 454.717 454.717 ↑ 20.0 1 1

Seq Scan on reception_units ru_1 (cost=0.00..11,766.34 rows=20 width=106) (actual time=367.664..454.717 rows=1 loops=1)

  • Filter: ((id <> 'db7ddcb0-255a-11e3-8224-0800200c9a66'::uuid) AND ((type)::text <> 'MOVILE'::text) AND (upper((description)::text) ~~ '%%'::text) AND (upper(((code)::character varying)::text) ~~ '%1533112 (...)
  • Rows Removed by Filter: 208477
12. 0.034 0.034 ↑ 1.0 1 1

Index Scan using customers_pkey on customers cust_1 (cost=0.42..8.45 rows=1 width=16) (actual time=0.034..0.034 rows=1 loops=1)

  • Index Cond: (id = ru_1.customer_id)
  • Filter: ((upper((name)::text) ~~ '%ROSA%'::text) AND (upper((national_fiscal_identity)::text) ~~ '%%'::text))
13. 0.025 0.025 ↑ 1.0 1 1

Index Scan using address_pkey on address a_1 (cost=0.42..8.46 rows=1 width=16) (actual time=0.024..0.025 rows=1 loops=1)

  • Index Cond: (id = ru_1.address_id)
  • Filter: ((upper((colony)::text) ~~ '%%'::text) AND (upper((street)::text) ~~ '%%'::text) AND (upper((external_number)::text) ~~ '%464%'::text))
14. 0.018 0.018 ↓ 2.0 2 1

Index Scan using idx_customer_id on phones p_1 (cost=0.42..7.94 rows=1 width=16) (actual time=0.015..0.018 rows=2 loops=1)

  • Index Cond: (customer_id = cust_1.id)
  • Filter: (upper((phone_number)::text) ~~ '%8125701135%'::text)
15. 0.022 0.022 ↑ 2.0 1 2

Index Only Scan using reception_units_distribution_centers_pkey on reception_units_distribution_centers rudc_1 (cost=0.56..12.59 rows=2 width=32) (actual time=0.010..0.011 rows=1 loops=2)

  • Index Cond: ((reception_unit_id = ru_1.id) AND (distribution_center_id = dc_1.id))
  • Heap Fetches: 2
16.          

CTE sum_total_orders_pending

17. 0.010 0.053 ↑ 1.0 1 1

HashAggregate (cost=28.23..28.24 rows=1 width=24) (actual time=0.053..0.053 rows=1 loops=1)

  • Group Key: o.reception_unit_id
18. 0.003 0.043 ↓ 5.0 5 1

Nested Loop (cost=4.47..28.23 rows=1 width=24) (actual time=0.031..0.043 rows=5 loops=1)

19. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on sel_recepcion_unit ru_2 (cost=0.00..0.02 rows=1 width=16) (actual time=0.001..0.002 rows=1 loops=1)

20. 0.027 0.038 ↓ 5.0 5 1

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

  • Recheck Cond: (reception_unit_id = ru_2.id)
  • Filter: (((order_type)::text = 'ORDER'::text) AND ((status)::text = ANY ('{NEW,NOTIFIED,STANDBY,REASIGNABLE}'::text[])))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=6
21. 0.011 0.011 ↑ 1.0 6 1

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

  • Index Cond: (reception_unit_id = ru_2.id)
22. 0.514 456.353 ↑ 171.0 1 1

GroupAggregate (cost=794.84..805.10 rows=171 width=682) (actual time=456.353..456.353 rows=1 loops=1)

  • 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.432 455.839 ↑ 1.5 116 1

Sort (cost=794.84..795.27 rows=171 width=682) (actual time=455.815..455.839 rows=116 loops=1)

  • 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
  • Sort Method: quicksort Memory: 55kB
24. 0.059 455.407 ↑ 1.5 116 1

Hash Left Join (cost=14.26..788.50 rows=171 width=682) (actual time=455.215..455.407 rows=116 loops=1)

  • Hash Cond: (ru.id = st.reception_unit_id)
25. 0.030 455.291 ↑ 1.5 116 1

Nested Loop Left Join (cost=14.23..786.12 rows=171 width=674) (actual time=455.152..455.291 rows=116 loops=1)

26. 0.008 455.073 ↑ 1.0 2 1

Nested Loop Left Join (cost=5.59..39.41 rows=2 width=662) (actual time=455.064..455.073 rows=2 loops=1)

27. 0.003 455.051 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.15..27.10 rows=1 width=670) (actual time=455.042..455.051 rows=1 loops=1)

28. 0.003 455.038 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.73..18.65 rows=1 width=651) (actual time=455.030..455.038 rows=1 loops=1)

29. 0.008 455.028 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.45..10.34 rows=1 width=659) (actual time=455.021..455.028 rows=1 loops=1)

30. 0.025 455.009 ↑ 1.0 1 1

Hash Right Join (cost=0.03..1.90 rows=1 width=630) (actual time=455.004..455.009 rows=1 loops=1)

  • Hash Cond: (pay.id = ru.payment_condition_id)
31. 0.016 0.016 ↑ 1.0 62 1

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

32. 0.005 454.968 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=636) (actual time=454.968..454.968 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
33. 454.963 454.963 ↑ 1.0 1 1

CTE Scan on sel_recepcion_unit ru (cost=0.00..0.02 rows=1 width=636) (actual time=454.962..454.963 rows=1 loops=1)

34. 0.011 0.011 ↑ 1.0 1 1

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

  • Index Cond: (id = ru.customer_id)
35. 0.007 0.007 ↑ 1.0 1 1

Index Scan using discounts_pkey on discounts dis (cost=0.28..8.29 rows=1 width=24) (actual time=0.007..0.007 rows=1 loops=1)

  • Index Cond: (id = ru.discount_id)
36. 0.010 0.010 ↑ 1.0 1 1

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

  • Index Cond: (ru.address_id = id)
37. 0.006 0.014 ↑ 1.0 2 1

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

  • Recheck Cond: (customer_id = ru.customer_id)
  • Heap Blocks: exact=1
38. 0.008 0.008 ↑ 1.0 2 1

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

  • Index Cond: (customer_id = ru.customer_id)
39. 0.075 0.188 ↑ 1.6 58 2

Hash Left Join (cost=8.64..376.73 rows=94 width=28) (actual time=0.053..0.094 rows=58 loops=2)

  • Hash Cond: (rudc.distribution_center_id = dc.id)
40. 0.038 0.072 ↑ 1.6 58 2

Bitmap Heap Scan on reception_units_distribution_centers rudc (cost=5.29..372.09 rows=94 width=32) (actual time=0.022..0.036 rows=58 loops=2)

  • Recheck Cond: (ru.id = reception_unit_id)
  • Heap Blocks: exact=6
41. 0.034 0.034 ↑ 1.6 58 2

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

  • Index Cond: (ru.id = reception_unit_id)
42. 0.012 0.041 ↑ 1.0 60 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
43. 0.029 0.029 ↑ 1.0 60 1

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

44. 0.003 0.057 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=24) (actual time=0.057..0.057 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
45. 0.054 0.054 ↑ 1.0 1 1

CTE Scan on sum_total_orders_pending st (cost=0.00..0.02 rows=1 width=24) (actual time=0.054..0.054 rows=1 loops=1)

Planning time : 7.405 ms
Execution time : 456.774 ms