explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QgbN : Optimization for: Optimization for: purchases; plan #iBeQ; plan #UShg

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.021 346.630 ↑ 1.0 30 1

Limit (cost=3.67..108.89 rows=30 width=3,798) (actual time=0.321..346.630 rows=30 loops=1)

2. 0.080 346.609 ↑ 3,036.7 30 1

Merge Left Join (cost=3.67..319,508.98 rows=91,101 width=3,798) (actual time=0.320..346.609 rows=30 loops=1)

  • Merge Cond: (p.product_id = srv.product)
3. 0.105 346.524 ↑ 3,036.7 30 1

Nested Loop Left Join (cost=3.53..319,268.40 rows=91,101 width=3,706) (actual time=0.308..346.524 rows=30 loops=1)

4. 0.068 15.069 ↑ 3,036.7 30 1

Nested Loop Left Join (cost=3.25..290,438.93 rows=91,101 width=3,706) (actual time=0.262..15.069 rows=30 loops=1)

  • Join Filter: (purchase_items.product = p.product_id)
5. 0.064 14.971 ↑ 3,036.7 30 1

Merge Left Join (cost=2.96..279,420.41 rows=91,101 width=3,660) (actual time=0.237..14.971 rows=30 loops=1)

  • Merge Cond: (p.product_id = r.product_id)
6. 0.108 14.902 ↑ 3,036.7 30 1

Nested Loop (cost=2.81..279,054.44 rows=91,101 width=3,518) (actual time=0.227..14.902 rows=30 loops=1)

7. 0.597 14.644 ↑ 3,155.5 30 1

Merge Left Join (cost=2.53..250,041.08 rows=94,664 width=2,777) (actual time=0.200..14.644 rows=30 loops=1)

  • Merge Cond: (p.product_id = eb.product_id)
  • Filter: ((eb.publication_date IS NULL) OR (eb.publication_date <= now()))
8. 0.344 9.463 ↑ 3,814.6 30 1

Merge Join (cost=2.11..82,132.18 rows=114,437 width=1,281) (actual time=0.134..9.463 rows=30 loops=1)

  • Merge Cond: (p.product_id = pp.product)
9. 1.226 1.226 ↑ 3,820.6 30 1

Index Scan using products_product_id_idx on products p (cost=0.42..48,041.40 rows=114,619 width=1,088) (actual time=0.033..1.226 rows=30 loops=1)

  • Filter: (active AND approved AND (regions && '{ALL,ZAR}'::character varying[]) AND (markets && '{2}'::integer[]))
  • Rows Removed by Filter: 1207
10. 7.893 7.893 ↑ 224.9 1,201 1

Index Scan using product_prices_product_idx on product_prices pp (cost=0.42..31,985.42 rows=270,116 width=193) (actual time=0.032..7.893 rows=1,201 loops=1)

11. 4.584 4.584 ↑ 218.7 1,237 1

Index Scan using idx_ebooks_product_id on ebooks eb (cost=0.42..165,235.21 rows=270,498 width=1,496) (actual time=0.008..4.584 rows=1,237 loops=1)

12. 0.150 0.150 ↑ 1.0 1 30

Index Scan using publishers_publisher_id_idx on publishers pub (cost=0.28..0.31 rows=1 width=741) (actual time=0.005..0.005 rows=1 loops=30)

  • Index Cond: (publisher_id = p.publisher)
13. 0.002 0.005 ↓ 0.0 0 1

Materialize (cost=0.14..136.98 rows=210 width=146) (actual time=0.004..0.005 rows=0 loops=1)

14. 0.000 0.003 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.14..136.45 rows=210 width=146) (actual time=0.003..0.003 rows=0 loops=1)

  • Join Filter: (rt.resource_type_id = r.resource_type_id)
15. 0.003 0.003 ↓ 0.0 0 1

Index Scan using resources_product_id_idx on resources r (cost=0.14..47.30 rows=210 width=141) (actual time=0.002..0.003 rows=0 loops=1)

16. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.42 rows=28 width=9) (never executed)

17. 0.000 0.000 ↓ 0.0 0

Seq Scan on resource_types rt (cost=0.00..1.28 rows=28 width=9) (never executed)

18. 0.011 0.030 ↓ 0.0 0 30

Materialize (cost=0.29..86.42 rows=8 width=46) (actual time=0.001..0.001 rows=0 loops=30)

19. 0.019 0.019 ↓ 0.0 0 1

Index Scan using purchase_items_bought_for_idx on purchase_items (cost=0.29..86.38 rows=8 width=46) (actual time=0.019..0.019 rows=0 loops=1)

  • Index Cond: (bought_for = 644154)
  • Filter: ((SubPlan 1) IS NOT NULL)
20.          

SubPlan (forIndex Scan)

21. 0.000 0.000 ↓ 0.0 0

Result (cost=0.29..8.31 rows=1 width=4) (never executed)

  • One-Time Filter: (NOT purchase_items.revoked)
22. 0.000 0.000 ↓ 0.0 0

Index Scan using empathy_purchases_pk on purchases (cost=0.29..8.31 rows=1 width=4) (never executed)

  • Index Cond: (purchase_items.purchase = purchase_id)
  • Filter: ((status = 4) OR (status = 6) OR (status = 8))
23. 331.350 331.350 ↑ 1.0 1 30

Index Only Scan using subjects_subject_index on subjects s (cost=0.28..0.31 rows=1 width=39) (actual time=11.045..11.045 rows=1 loops=30)

  • Index Cond: (subject = (p.subject)::text)
  • Heap Fetches: 30
24. 0.005 0.005 ↑ 33.0 1 1

Index Scan using services_product_idx on services srv (cost=0.14..12.63 rows=33 width=25) (actual time=0.005..0.005 rows=1 loops=1)