explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wb8k : Optimization for: Optimization for: Optimization for: Optimization for: purchases; plan #iBeQ; plan #UShg; plan #QgbN; plan #7qTg

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.523 2,842.718 ↑ 1.0 30 1

Limit (cost=279,440.90..279,440.97 rows=30 width=3,798) (actual time=2,841.195..2,842.718 rows=30 loops=1)

2. 285.037 2,841.195 ↑ 3,036.7 30 1

Sort (cost=279,440.90..279,668.65 rows=91,101 width=3,798) (actual time=2,841.193..2,841.195 rows=30 loops=1)

  • Sort Key: p.educational_book DESC, p.created DESC
  • Sort Method: top-N heapsort Memory: 203kB
3. 189.898 2,556.158 ↓ 1.7 154,916 1

Hash Left Join (cost=1,321.61..276,750.28 rows=91,101 width=3,798) (actual time=17.690..2,556.158 rows=154,916 loops=1)

  • Hash Cond: (p.product_id = srv.product)
4. 224.370 2,366.232 ↓ 1.7 154,916 1

Hash Left Join (cost=1,319.87..276,406.80 rows=91,101 width=3,706) (actual time=17.644..2,366.232 rows=154,916 loops=1)

  • Hash Cond: ((p.subject)::text = (s.subject)::text)
5. 176.454 2,135.798 ↓ 1.7 154,916 1

Merge Left Join (cost=1,088.36..274,922.65 rows=91,101 width=3,706) (actual time=11.545..2,135.798 rows=154,916 loops=1)

  • Merge Cond: (p.product_id = purchase_items.product)
6. 162.712 1,959.316 ↓ 1.7 154,916 1

Merge Left Join (cost=1,001.86..274,608.32 rows=91,101 width=3,660) (actual time=11.512..1,959.316 rows=154,916 loops=1)

  • Merge Cond: (p.product_id = r.product_id)
7. 391.160 1,796.598 ↓ 1.7 154,916 1

Merge Left Join (cost=1,001.57..274,287.04 rows=91,101 width=3,518) (actual time=11.501..1,796.598 rows=154,916 loops=1)

  • Merge Cond: (p.product_id = eb.product_id)
  • Filter: ((eb.publication_date IS NULL) OR (eb.publication_date <= now()))
  • Rows Removed by Filter: 273
8. 0.000 957.231 ↓ 1.4 155,189 1

Gather Merge (cost=1,001.15..106,561.32 rows=110,130 width=2,022) (actual time=11.250..957.231 rows=155,189 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 323.917 2,377.353 ↓ 1.1 51,730 3

Nested Loop (cost=1.13..92,849.56 rows=45,888 width=2,022) (actual time=0.793..792.451 rows=51,730 loops=3)

10. 256.864 1,432.680 ↓ 1.1 51,730 3

Nested Loop (cost=0.70..60,311.37 rows=45,960 width=1,829) (actual time=0.738..477.560 rows=51,730 loops=3)

11. 864.312 864.312 ↓ 1.1 51,917 3

Parallel Index Scan using products_product_id_idx on products p (cost=0.42..45,674.13 rows=47,758 width=1,088) (actual time=0.688..288.104 rows=51,917 loops=3)

  • Filter: (active AND approved AND (regions && '{ALL,ZAR}'::character varying[]) AND (markets && '{2}'::integer[]))
  • Rows Removed by Filter: 38264
12. 311.504 311.504 ↑ 1.0 1 155,752

Index Scan using publishers_publisher_id_idx on publishers pub (cost=0.28..0.31 rows=1 width=741) (actual time=0.002..0.002 rows=1 loops=155,752)

  • Index Cond: (publisher_id = p.publisher)
13. 620.756 620.756 ↑ 1.0 1 155,189

Index Scan using product_prices_product_idx on product_prices pp (cost=0.42..0.70 rows=1 width=193) (actual time=0.004..0.004 rows=1 loops=155,189)

  • Index Cond: (product = p.product_id)
14. 448.207 448.207 ↑ 1.0 270,498 1

Index Scan using ebooks_product_id_publication_date_idx on ebooks eb (cost=0.42..165,127.38 rows=270,498 width=1,496) (actual time=0.161..448.207 rows=270,498 loops=1)

15. 0.002 0.006 ↓ 0.0 0 1

Materialize (cost=0.28..92.30 rows=210 width=146) (actual time=0.006..0.006 rows=0 loops=1)

16. 0.001 0.004 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.28..91.78 rows=210 width=146) (actual time=0.004..0.004 rows=0 loops=1)

17. 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.003..0.003 rows=0 loops=1)

18. 0.000 0.000 ↓ 0.0 0

Index Scan using resource_types_pk on resource_types rt (cost=0.14..0.21 rows=1 width=9) (never executed)

  • Index Cond: (resource_type_id = r.resource_type_id)
19. 0.006 0.028 ↓ 0.0 0 1

Sort (cost=86.50..86.52 rows=8 width=46) (actual time=0.028..0.028 rows=0 loops=1)

  • Sort Key: purchase_items.product
  • Sort Method: quicksort Memory: 25kB
20. 0.022 0.022 ↓ 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.022..0.022 rows=0 loops=1)

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

SubPlan (forIndex Scan)

22. 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)
23. 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))
24. 1.314 6.064 ↑ 1.0 4,867 1

Hash (cost=170.67..170.67 rows=4,867 width=39) (actual time=6.064..6.064 rows=4,867 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 405kB
25. 4.750 4.750 ↑ 1.0 4,867 1

Seq Scan on subjects s (cost=0.00..170.67 rows=4,867 width=39) (actual time=0.014..4.750 rows=4,867 loops=1)

26. 0.009 0.028 ↑ 1.0 33 1

Hash (cost=1.33..1.33 rows=33 width=25) (actual time=0.028..0.028 rows=33 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
27. 0.019 0.019 ↑ 1.0 33 1

Seq Scan on services srv (cost=0.00..1.33 rows=33 width=25) (actual time=0.010..0.019 rows=33 loops=1)