explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oVIn

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 34,142.204 ↑ 1.0 10 1

Limit (cost=416,685,517.00..416,686,653.75 rows=10 width=352) (actual time=34,138.926..34,142.204 rows=10 loops=1)

2. 5.269 34,142.197 ↑ 819,491.5 10 1

Group (cost=416,685,517.00..1,348,241,925.21 rows=8,194,915 width=352) (actual time=34,138.926..34,142.197 rows=10 loops=1)

  • Group Key: c.id, (COALESCE(c.email, ''::character varying)), (COALESCE(c.phone, ''::character varying)), l.iso_value, ((SubPlan 4)), ((SubPlan 5))
3. 7,204.043 34,136.828 ↑ 819,491.5 10 1

Sort (cost=416,685,517.00..416,706,004.29 rows=8,194,915 width=166) (actual time=34,136.823..34,136.828 rows=10 loops=1)

  • Sort Key: c.id, (COALESCE(c.email, ''::character varying)), (COALESCE(c.phone, ''::character varying)), l.iso_value, ((SubPlan 4)), ((SubPlan 5))
  • Sort Method: external merge Disk: 546800kB
4. 5,470.835 26,932.785 ↓ 1.0 8,196,622 1

Hash Left Join (cost=6.62..413,727,758.22 rows=8,194,915 width=166) (actual time=0.088..26,932.785 rows=8,196,622 loops=1)

  • Hash Cond: (c.language_id = l.id)
5. 2,968.743 5,068.662 ↓ 1.0 8,196,622 1

Merge Left Join (cost=0.86..811,366.00 rows=8,194,915 width=91) (actual time=0.021..5,068.662 rows=8,196,622 loops=1)

  • Merge Cond: (c.id = cs.customer_id)
6. 1,381.274 1,381.274 ↑ 1.0 8,194,915 1

Index Scan using customer_custom_id_pkey on customers_custom c (cost=0.43..440,668.82 rows=8,194,915 width=91) (actual time=0.008..1,381.274 rows=8,194,915 loops=1)

7. 718.645 718.645 ↓ 1.0 8,175,298 1

Index Only Scan using ix_cs_customers_custom_customer_id on customer_sellers cs (cost=0.43..248,181.50 rows=8,162,271 width=4) (actual time=0.009..718.645 rows=8,175,298 loops=1)

  • Heap Fetches: 0
8. 0.018 0.044 ↑ 1.0 167 1

Hash (cost=3.67..3.67 rows=167 width=7) (actual time=0.044..0.044 rows=167 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
9. 0.026 0.026 ↑ 1.0 167 1

Seq Scan on languages l (cost=0.00..3.67 rows=167 width=7) (actual time=0.009..0.026 rows=167 loops=1)

10.          

SubPlan (forHash Left Join)

11. 0.000 8,196.622 ↓ 0.0 0 8,196,622

Limit (cost=25.19..25.19 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=8,196,622)

12. 0.000 8,196.622 ↓ 0.0 0 8,196,622

Sort (cost=25.19..25.19 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=8,196,622)

  • Sort Key: bsl.id DESC
  • Sort Method: quicksort Memory: 25kB
13. 8,196.622 8,196.622 ↓ 0.0 0 8,196,622

Index Scan using ix_brand_synchro_logs_entity_id on brand_synchro_logs bsl (cost=0.29..25.18 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=8,196,622)

  • Index Cond: (entity_id = c.id)
  • Filter: ((entity_type)::text = 'customer'::text)
  • Rows Removed by Filter: 0
14. 0.000 8,196.622 ↓ 0.0 0 8,196,622

Limit (cost=25.19..25.19 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=8,196,622)

15. 0.000 8,196.622 ↓ 0.0 0 8,196,622

Sort (cost=25.19..25.19 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=8,196,622)

  • Sort Key: bsl_1.id DESC
  • Sort Method: quicksort Memory: 25kB
16. 8,196.622 8,196.622 ↓ 0.0 0 8,196,622

Index Scan using ix_brand_synchro_logs_entity_id on brand_synchro_logs bsl_1 (cost=0.29..25.18 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=8,196,622)

  • Index Cond: (entity_id = c.id)
  • Filter: ((entity_type)::text = 'customer'::text)
  • Rows Removed by Filter: 0
17.          

SubPlan (forGroup)

18. 0.010 0.100 ↑ 1.0 1 10

Aggregate (cost=50.34..50.35 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=10)

19. 0.006 0.090 ↑ 2.0 1 10

Nested Loop Left Join (cost=0.71..50.33 rows=2 width=4) (actual time=0.007..0.009 rows=1 loops=10)

  • Filter: ((cfp.order_id IS NULL) OR (NOT o.deleted))
20. 0.060 0.060 ↑ 2.0 1 10

Index Scan using ix_customer_fidelity_points_customers_custom_customer_id on customer_fidelity_points cfp (cost=0.29..33.44 rows=2 width=8) (actual time=0.005..0.006 rows=1 loops=10)

  • Index Cond: (customer_id = c.id)
  • Filter: ((NOT is_deleted) AND (points <> 0))
  • Rows Removed by Filter: 0
21. 0.024 0.024 ↑ 1.0 1 8

Index Scan using order_id_pkey on orders o (cost=0.42..8.44 rows=1 width=5) (actual time=0.003..0.003 rows=1 loops=8)

  • Index Cond: (id = cfp.order_id)
22.          

SubPlan (forLimit)

23. 0.010 0.170 ↑ 1.0 1 10

Limit (cost=0.43..12.65 rows=1 width=3) (actual time=0.017..0.017 rows=1 loops=10)

24. 0.046 0.160 ↑ 2.0 1 10

Nested Loop Left Join (cost=0.43..24.87 rows=2 width=3) (actual time=0.016..0.016 rows=1 loops=10)

  • Join Filter: (co.id = ad.country_id)
  • Rows Removed by Join Filter: 107
25. 0.030 0.030 ↑ 2.0 1 10

Index Scan using "IX_ADDRESSES_CUSTOMERS_CUSTOM_CUSTOMER_ID" on addresses ad (cost=0.43..11.63 rows=2 width=4) (actual time=0.003..0.003 rows=1 loops=10)

  • Index Cond: (customer_id = c.id)
  • Filter: (address_type = 3)
26. 0.052 0.084 ↑ 1.6 153 7

Materialize (cost=0.00..6.62 rows=241 width=7) (actual time=0.002..0.012 rows=153 loops=7)

27. 0.032 0.032 ↑ 1.0 241 1

Seq Scan on country co (cost=0.00..5.41 rows=241 width=7) (actual time=0.009..0.032 rows=241 loops=1)

28. 5.040 5.040 ↑ 1.0 1 10

Result (cost=0.00..0.26 rows=1 width=8) (actual time=0.504..0.504 rows=1 loops=10)

Planning time : 2.271 ms
Execution time : 34,576.104 ms