explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tQmN

Settings
# exclusive inclusive rows x rows loops node
1. 0.050 17,205.015 ↑ 73.2 20 1

HashAggregate (cost=831,046.54..872,366.05 rows=1,464 width=144) (actual time=17,205.007..17,205.015 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 selected_recepcion_unit_list

3. 0.005 2.498 ↑ 1.0 20 1

Limit (cost=1.82..476.65 rows=20 width=16) (actual time=0.148..2.498 rows=20 loops=1)

4. 0.205 2.493 ↑ 2,039.2 20 1

Unique (cost=1.82..968,303.43 rows=40,785 width=16) (actual time=0.148..2.493 rows=20 loops=1)

5. 0.231 2.288 ↑ 1,805.1 1,691 1

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

6. 0.019 1.042 ↑ 1,924.3 35 1

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

7. 0.034 0.883 ↑ 2,039.2 20 1

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

8. 0.709 0.709 ↑ 2,039.2 20 1

Index Scan using reception_units_pkey on reception_units ru_1 (cost=0.42..31,522.76 rows=40,785 width=48) (actual time=0.100..0.709 rows=20 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: 250
9. 0.140 0.140 ↑ 1.0 1 20

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

  • Index Cond: (id = ru_1.customer_id)
  • Heap Fetches: 20
10. 0.140 0.140 ↑ 1.0 2 20

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

  • Index Cond: (customer_id = cust_1.id)
  • Heap Fetches: 34
11. 1.015 1.015 ↑ 2.0 48 35

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 time=0.006..0.029 rows=48 loops=35)

  • Index Cond: (reception_unit_id = ru_1.id)
  • Heap Fetches: 1691
12. 3.397 17,204.965 ↑ 73.2 20 1

GroupAggregate (cost=789,136.92..830,507.66 rows=1,464 width=144) (actual time=17,201.475..17,204.965 rows=20 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
13. 3.305 17,201.388 ↓ 1.2 1,808 1

Sort (cost=789,136.92..789,140.58 rows=1,464 width=144) (actual time=17,201.181..17,201.388 rows=1,808 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: 529kB
14. 1,310.777 17,198.083 ↓ 1.2 1,808 1

Hash Join (cost=102,307.74..789,059.94 rows=1,464 width=144) (actual time=9,144.130..17,198.083 rows=1,808 loops=1)

  • Hash Cond: (ru.id = list.reception_unit_id)
15. 10,180.977 15,884.781 ↓ 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,823.922..15,884.781 rows=16,575,178 loops=1)

  • Hash Cond: (rudc.reception_unit_id = ru.id)
16. 2,699.517 3,880.134 ↑ 1.0 9,448,689 1

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

  • Hash Cond: (rudc.distribution_center_id = dc.id)
17. 1,180.588 1,180.588 ↑ 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.022..1,180.588 rows=9,448,689 loops=1)

18. 0.012 0.029 ↑ 1.0 60 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
19. 0.017 0.017 ↑ 1.0 60 1

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

20. 198.980 1,823.670 ↑ 1.0 335,833 1

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

  • Buckets: 32768 Batches: 16 Memory Usage: 4025kB
21. 359.500 1,624.690 ↑ 1.0 335,833 1

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

  • Hash Cond: (ru.customer_id = cust.id)
22. 333.544 796.970 ↓ 1.0 204,121 1

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

  • Hash Cond: (ru.address_id = a.id)
23. 51.332 205.005 ↓ 1.0 204,121 1

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

  • Hash Cond: (ru.discount_id = dis.id)
24. 77.721 153.082 ↓ 1.0 204,121 1

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

  • Hash Cond: (ru.payment_condition_id = pay.id)
25. 75.315 75.315 ↓ 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.050..75.315 rows=204,121 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
27. 0.025 0.025 ↑ 1.0 62 1

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

28. 0.315 0.591 ↑ 1.0 1,410 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 94kB
29. 0.276 0.276 ↑ 1.0 1,410 1

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

30. 149.193 258.421 ↑ 1.0 393,101 1

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

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

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

32. 93.659 468.220 ↓ 1.0 314,734 1

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

  • Buckets: 65536 Batches: 8 Memory Usage: 3879kB
33. 229.477 374.561 ↓ 1.0 314,734 1

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

  • Hash Cond: (p.customer_id = cust.id)
34. 50.935 50.935 ↑ 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..50.935 rows=306,240 loops=1)

35. 53.050 94.149 ↑ 1.0 185,453 1

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

  • Buckets: 65536 Batches: 8 Memory Usage: 2305kB
36. 41.099 41.099 ↑ 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..41.099 rows=185,453 loops=1)

37. 0.008 2.525 ↑ 1.0 20 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
38. 2.517 2.517 ↑ 1.0 20 1

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

39.          

SubPlan (forGroupAggregate)

40. 0.040 0.180 ↑ 1.0 1 20

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

41. 0.060 0.140 ↓ 0.0 0 20

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

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

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

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