explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eTtD

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 17,268.799 ↑ 73.0 1 1

HashAggregate (cost=803,082.26..805,142.59 rows=73 width=144) (actual time=17,268.799..17,268.799 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 selected_recepcion_unit_list

3. 0.001 286.708 ↑ 1.0 1 1

Limit (cost=11,968.58..11,968.59 rows=1 width=16) (actual time=286.708..286.708 rows=1 loops=1)

4. 0.007 286.707 ↑ 1.0 1 1

HashAggregate (cost=11,968.58..11,968.59 rows=1 width=16) (actual time=286.707..286.707 rows=1 loops=1)

  • Group Key: ru_1.id
5. 0.006 286.700 ↓ 2.0 2 1

Nested Loop (cost=2.25..11,968.58 rows=1 width=16) (actual time=239.234..286.700 rows=2 loops=1)

6. 0.065 0.065 ↑ 1.0 1 1

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

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

Nested Loop (cost=2.25..11,965.67 rows=1 width=32) (actual time=239.182..286.629 rows=2 loops=1)

8. 0.003 286.614 ↓ 2.0 2 1

Nested Loop Semi Join (cost=1.69..11,953.06 rows=1 width=16) (actual time=239.172..286.614 rows=2 loops=1)

  • Join Filter: (cust_1.id = phones.customer_id)
9. 0.003 286.601 ↑ 1.0 2 1

Nested Loop (cost=1.27..11,951.91 rows=2 width=64) (actual time=239.162..286.601 rows=2 loops=1)

10. 0.003 286.591 ↑ 1.0 1 1

Nested Loop (cost=0.84..11,943.96 rows=1 width=48) (actual time=239.153..286.591 rows=1 loops=1)

11. 0.004 286.571 ↑ 1.0 1 1

Nested Loop (cost=0.42..11,935.49 rows=1 width=64) (actual time=239.135..286.571 rows=1 loops=1)

12. 286.546 286.546 ↑ 20.0 1 1

Seq Scan on reception_units ru_1 (cost=0.00..11,766.34 rows=20 width=48) (actual time=239.110..286.546 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) ~~ '%1 (...)
  • Rows Removed by Filter: 208477
13. 0.021 0.021 ↑ 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.021..0.021 rows=1 loops=1)

  • Index Cond: (id = ru_1.customer_id)
  • Filter: ((upper((name)::text) ~~ '%ROSA%'::text) AND (upper((national_fiscal_identity)::text) ~~ '%%'::text))
14. 0.017 0.017 ↑ 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.016..0.017 rows=1 loops=1)

  • Index Cond: (id = ru_1.address_id)
  • Filter: ((upper((internal_number)::text) ~~ '%%'::text) AND (upper((colony)::text) ~~ '%%'::text) AND (upper((street)::text) ~~ '%%'::text) AND (upper((external_number)::text) ~~ '%464%'::text))
15. 0.007 0.007 ↑ 1.0 2 1

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

  • Index Cond: (customer_id = cust_1.id)
  • Heap Fetches: 2
16. 0.010 0.010 ↑ 1.0 1 2

Index Scan using idx_customer_id on phones (cost=0.42..0.52 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=2)

  • Index Cond: (customer_id = p_1.customer_id)
  • Filter: (upper((phone_number)::text) ~~ '%8125701135%'::text)
17. 0.012 0.012 ↑ 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.005..0.006 rows=1 loops=2)

  • Index Cond: ((reception_unit_id = ru_1.id) AND (distribution_center_id = dc_1.id))
  • Heap Fetches: 2
18. 0.271 17,268.791 ↑ 73.0 1 1

GroupAggregate (cost=789,047.67..791,110.56 rows=73 width=144) (actual time=17,268.791..17,268.791 rows=1 loops=1)

  • Group Key: ru.id, cust.national_fiscal_identity, cust.name, a.street, a.external_number, a.internal_number, a.colony, pay.reference, dis.amount
19. 0.173 17,268.477 ↓ 1.6 116 1

Sort (cost=789,047.67..789,047.86 rows=73 width=144) (actual time=17,268.467..17,268.477 rows=116 loops=1)

  • Sort Key: ru.id, cust.national_fiscal_identity, cust.name, a.street, a.external_number, a.internal_number, a.colony, pay.reference, dis.amount
  • Sort Method: quicksort Memory: 55kB
20. 1,315.153 17,268.304 ↓ 1.6 116 1

Hash Join (cost=102,307.13..789,045.41 rows=73 width=144) (actual time=10,481.196..17,268.304 rows=116 loops=1)

  • Hash Cond: (ru.id = list.reception_unit_id)
21. 10,274.072 15,666.435 ↓ 1.1 16,575,178 1

Hash Right Join (cost=102,307.09..731,812.36 rows=15,261,944 width=144) (actual time=1,614.263..15,666.435 rows=16,575,178 loops=1)

  • Hash Cond: (rudc.reception_unit_id = ru.id)
22. 2,629.066 3,778.332 ↑ 1.0 9,448,689 1

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

  • Hash Cond: (rudc.distribution_center_id = dc.id)
23. 1,149.236 1,149.236 ↑ 1.0 9,448,689 1

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

24. 0.012 0.030 ↑ 1.0 60 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
25. 0.018 0.018 ↑ 1.0 60 1

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

26. 185.701 1,614.031 ↑ 1.0 335,833 1

Hash (cost=91,516.46..91,516.46 rows=336,743 width=132) (actual time=1,614.031..1,614.031 rows=335,833 loops=1)

  • Buckets: 32768 Batches: 16 Memory Usage: 4025kB
27. 312.777 1,428.330 ↑ 1.0 335,833 1

Hash Left Join (cost=50,544.79..91,516.46 rows=336,743 width=132) (actual time=644.486..1,428.330 rows=335,833 loops=1)

  • Hash Cond: (ru.customer_id = cust.id)
28. 271.414 668.298 ↓ 1.0 204,121 1

Hash Left Join (cost=18,978.89..45,800.51 rows=203,925 width=111) (actual time=197.054..668.298 rows=204,121 loops=1)

  • Hash Cond: (ru.address_id = a.id)
29. 51.833 200.623 ↓ 1.0 204,121 1

Hash Left Join (cost=49.12..14,296.23 rows=203,925 width=92) (actual time=0.420..200.623 rows=204,121 loops=1)

  • Hash Cond: (ru.discount_id = dis.id)
30. 77.370 148.422 ↓ 1.0 204,121 1

Hash Left Join (cost=2.40..11,445.53 rows=203,925 width=100) (actual time=0.047..148.422 rows=204,121 loops=1)

  • Hash Cond: (ru.payment_condition_id = pay.id)
31. 71.030 71.030 ↓ 1.0 204,121 1

Seq Scan on reception_units ru (cost=0.00..8,639.17 rows=203,925 width=106) (actual time=0.018..71.030 rows=204,121 loops=1)

  • Filter: ((id <> 'db7ddcb0-255a-11e3-8224-0800200c9a66'::uuid) AND ((type)::text <> 'MOVILE'::text))
  • Rows Removed by Filter: 4357
32. 0.007 0.022 ↑ 1.0 62 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
33. 0.015 0.015 ↑ 1.0 62 1

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

34. 0.186 0.368 ↑ 1.0 1,410 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 94kB
35. 0.182 0.182 ↑ 1.0 1,410 1

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

36. 117.216 196.261 ↑ 1.0 393,101 1

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

  • Buckets: 65536 Batches: 16 Memory Usage: 2561kB
37. 79.045 79.045 ↑ 1.0 393,101 1

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

38. 93.732 447.255 ↓ 1.0 314,734 1

Hash (cost=24,746.89..24,746.89 rows=306,240 width=53) (actual time=447.255..447.255 rows=314,734 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 3879kB
39. 219.417 353.523 ↓ 1.0 314,734 1

Hash Right Join (cost=9,766.69..24,746.89 rows=306,240 width=53) (actual time=85.069..353.523 rows=314,734 loops=1)

  • Hash Cond: (p.customer_id = cust.id)
40. 49.252 49.252 ↑ 1.0 306,240 1

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

41. 48.919 84.854 ↑ 1.0 185,453 1

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

  • Buckets: 65536 Batches: 8 Memory Usage: 2305kB
42. 35.935 35.935 ↑ 1.0 185,453 1

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

43. 0.004 286.716 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=16) (actual time=286.716..286.716 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
44. 286.712 286.712 ↑ 1.0 1 1

CTE Scan on selected_recepcion_unit_list list (cost=0.00..0.02 rows=1 width=16) (actual time=286.711..286.712 rows=1 loops=1)

45.          

SubPlan (forGroupAggregate)

46. 0.006 0.043 ↑ 1.0 1 1

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

47. 0.024 0.037 ↓ 5.0 5 1

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

  • Recheck Cond: (reception_unit_id = ru.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
48. 0.013 0.013 ↑ 1.0 6 1

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

  • Index Cond: (reception_unit_id = ru.id)
Planning time : 8.983 ms
Execution time : 17,269.155 ms