explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FaIV

Settings
# exclusive inclusive rows x rows loops node
1. 0.056 12,034.811 ↑ 170.8 20 1

HashAggregate (cost=1,107,405.34..1,107,448.03 rows=3,415 width=682) (actual time=12,034.800..12,034.811 rows=20 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.005 12,016.457 ↑ 1.0 20 1

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

4. 0.748 12,016.452 ↑ 2,039.2 20 1

Unique (cost=1,092,458.99..1,168,768.69 rows=40,785 width=106) (actual time=12,014.695..12,016.452 rows=20 loops=1)

5. 6,898.100 12,015.704 ↑ 1,805.1 1,691 1

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

  • 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
  • Sort Method: external merge Disk: 147872kB
6. 3,473.256 5,117.604 ↑ 2.4 1,294,475 1

Hash Right Join (cost=36,937.40..408,968.81 rows=3,052,388 width=106) (actual time=642.127..5,117.604 rows=1,294,475 loops=1)

  • Hash Cond: (rudc_1.reception_unit_id = ru_1.id)
7. 1,002.425 1,002.425 ↑ 1.0 9,448,689 1

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

8. 15.276 641.923 ↑ 2.7 25,249 1

Hash (cost=34,976.54..34,976.54 rows=67,349 width=106) (actual time=641.923..641.923 rows=25,249 loops=1)

  • Buckets: 32768 Batches: 4 Memory Usage: 1126kB
9. 115.785 626.647 ↑ 2.7 25,249 1

Hash Right Join (cost=23,856.25..34,976.54 rows=67,349 width=106) (actual time=441.273..626.647 rows=25,249 loops=1)

  • Hash Cond: (p_1.customer_id = cust_1.id)
10. 69.741 69.741 ↑ 1.0 306,240 1

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

11. 12.370 441.121 ↑ 2.7 14,927 1

Hash (cost=22,589.44..22,589.44 rows=40,785 width=122) (actual time=441.121..441.121 rows=14,927 loops=1)

  • Buckets: 32768 Batches: 2 Memory Usage: 1402kB
12. 63.041 428.751 ↑ 2.7 14,927 1

Hash Left Join (cost=9,042.69..22,589.44 rows=40,785 width=122) (actual time=79.330..428.751 rows=14,927 loops=1)

  • Hash Cond: (ru_1.customer_id = cust_1.id)
13. 286.831 286.831 ↑ 2.7 14,927 1

Seq Scan on reception_units ru_1 (cost=0.00..10,723.95 rows=40,785 width=106) (actual time=0.064..286.831 rows=14,927 loops=1)

  • Filter: ((id <> 'db7ddcb0-255a-11e3-8224-0800200c9a66'::uuid) AND ((type)::text <> 'MOVILE'::text) AND (upper(((code)::character varying)::text) ~~ '%43%'::text))
  • Rows Removed by Filter: 193551
14. 44.828 78.879 ↑ 1.0 185,453 1

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

  • Buckets: 131072 Batches: 4 Memory Usage: 3215kB
15. 34.051 34.051 ↑ 1.0 185,453 1

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

16.          

CTE sum_total_orders_pending

17. 0.010 0.150 ↓ 1.5 3 1

HashAggregate (cost=564.58..564.60 rows=2 width=24) (actual time=0.150..0.150 rows=3 loops=1)

  • Group Key: o.reception_unit_id
18. 0.032 0.140 ↓ 2.5 5 1

Nested Loop (cost=4.47..564.57 rows=2 width=24) (actual time=0.062..0.140 rows=5 loops=1)

19. 0.008 0.008 ↑ 1.0 20 1

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

20. 0.040 0.100 ↓ 0.0 0 20

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

  • 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=24
21. 0.060 0.060 ↑ 6.0 1 20

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

  • Index Cond: (reception_unit_id = ru_2.id)
22. 4.014 12,034.755 ↑ 170.8 20 1

GroupAggregate (cost=13,994.29..14,199.19 rows=3,415 width=682) (actual time=12,030.870..12,034.755 rows=20 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. 11.050 12,030.741 ↑ 1.9 1,808 1

Sort (cost=13,994.29..14,002.83 rows=3,415 width=682) (actual time=12,030.549..12,030.741 rows=1,808 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: 529kB
24. 0.366 12,019.691 ↑ 1.9 1,808 1

Hash Left Join (cost=16.94..13,793.87 rows=3,415 width=682) (actual time=12,016.803..12,019.691 rows=1,808 loops=1)

  • Hash Cond: (ru.id = st.reception_unit_id)
25. 0.258 12,019.169 ↑ 1.9 1,808 1

Nested Loop Left Join (cost=16.87..13,777.58 rows=3,415 width=674) (actual time=12,016.642..12,019.169 rows=1,808 loops=1)

26. 0.050 12,017.471 ↑ 1.0 36 1

Nested Loop Left Join (cost=8.23..621.75 rows=36 width=662) (actual time=12,016.580..12,017.471 rows=36 loops=1)

27. 0.022 12,017.241 ↑ 1.0 20 1

Nested Loop Left Join (cost=3.79..375.49 rows=20 width=670) (actual time=12,016.565..12,017.241 rows=20 loops=1)

28. 0.022 12,017.099 ↑ 1.0 20 1

Nested Loop Left Join (cost=3.37..206.49 rows=20 width=651) (actual time=12,016.556..12,017.099 rows=20 loops=1)

29. 0.208 12,016.937 ↑ 1.0 20 1

Hash Right Join (cost=2.95..37.54 rows=20 width=622) (actual time=12,016.540..12,016.937 rows=20 loops=1)

  • Hash Cond: (dis.id = ru.discount_id)
30. 0.199 0.199 ↑ 1.0 1,410 1

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

31. 0.004 12,016.530 ↑ 1.0 20 1

Hash (cost=2.70..2.70 rows=20 width=630) (actual time=12,016.530..12,016.530 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
32. 0.034 12,016.526 ↑ 1.0 20 1

Hash Right Join (cost=0.65..2.70 rows=20 width=630) (actual time=12,016.500..12,016.526 rows=20 loops=1)

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

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

34. 0.013 12,016.482 ↑ 1.0 20 1

Hash (cost=0.40..0.40 rows=20 width=636) (actual time=12,016.482..12,016.482 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
35. 12,016.469 12,016.469 ↑ 1.0 20 1

CTE Scan on sel_recepcion_unit ru (cost=0.00..0.40 rows=20 width=636) (actual time=12,014.699..12,016.469 rows=20 loops=1)

36. 0.140 0.140 ↑ 1.0 1 20

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

  • Index Cond: (id = ru.customer_id)
37. 0.120 0.120 ↑ 1.0 1 20

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

  • Index Cond: (ru.address_id = id)
38. 0.060 0.180 ↑ 1.0 2 20

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

  • Recheck Cond: (customer_id = ru.customer_id)
  • Heap Blocks: exact=22
39. 0.120 0.120 ↑ 1.0 2 20

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

  • Index Cond: (customer_id = ru.customer_id)
40. 0.620 1.440 ↑ 1.9 50 36

Hash Left Join (cost=8.64..372.90 rows=94 width=28) (actual time=0.015..0.040 rows=50 loops=36)

  • Hash Cond: (rudc.distribution_center_id = dc.id)
41. 0.468 0.792 ↑ 1.9 50 36

Bitmap Heap Scan on reception_units_distribution_centers rudc (cost=5.29..368.26 rows=94 width=32) (actual time=0.012..0.022 rows=50 loops=36)

  • Recheck Cond: (ru.id = reception_unit_id)
  • Heap Blocks: exact=201
42. 0.324 0.324 ↑ 1.9 50 36

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
44. 0.016 0.016 ↑ 1.0 60 1

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

45. 0.003 0.156 ↓ 1.5 3 1

Hash (cost=0.04..0.04 rows=2 width=24) (actual time=0.156..0.156 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
46. 0.153 0.153 ↓ 1.5 3 1

CTE Scan on sum_total_orders_pending st (cost=0.00..0.04 rows=2 width=24) (actual time=0.152..0.153 rows=3 loops=1)

Planning time : 3.044 ms
Execution time : 12,062.668 ms