explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iBeQ : purchases

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.167 2,891.871 ↑ 1.0 30 1

Limit (cost=279,550.55..279,550.63 rows=30 width=3,798) (actual time=2,890.704..2,891.871 rows=30 loops=1)

2. 284.484 2,890.704 ↑ 3,036.7 30 1

Sort (cost=279,550.55..279,778.31 rows=91,102 width=3,798) (actual time=2,890.702..2,890.704 rows=30 loops=1)

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

Hash Left Join (cost=1,321.61..276,859.91 rows=91,102 width=3,798) (actual time=14.442..2,606.220 rows=154,916 loops=1)

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

Hash Left Join (cost=1,319.87..276,516.42 rows=91,102 width=3,706) (actual time=14.384..2,415.738 rows=154,916 loops=1)

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

Merge Left Join (cost=1,088.36..275,032.26 rows=91,102 width=3,706) (actual time=10.637..2,184.948 rows=154,916 loops=1)

  • Merge Cond: (p.product_id = purchase_items.product)
6. 164.842 2,011.845 ↓ 1.7 154,916 1

Merge Left Join (cost=1,001.86..274,717.93 rows=91,102 width=3,660) (actual time=10.586..2,011.845 rows=154,916 loops=1)

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

Merge Left Join (cost=1,001.57..274,396.64 rows=91,102 width=3,518) (actual time=10.561..1,846.983 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 987.206 ↓ 1.4 155,189 1

Gather Merge (cost=1,001.15..106,563.13 rows=110,131 width=2,022) (actual time=10.409..987.206 rows=155,189 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 329.266 2,376.363 ↓ 1.1 51,730 3

Nested Loop (cost=1.13..92,851.26 rows=45,888 width=2,022) (actual time=0.744..792.121 rows=51,730 loops=3)

10. 258.184 1,426.341 ↓ 1.1 51,730 3

Nested Loop (cost=0.70..60,312.06 rows=45,962 width=1,829) (actual time=0.685..475.447 rows=51,730 loops=3)

11. 856.653 856.653 ↓ 1.1 51,917 3

Parallel Index Scan using products_product_id_idx on products p (cost=0.42..45,674.22 rows=47,760 width=1,088) (actual time=0.640..285.551 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. 464.796 464.796 ↑ 1.0 270,498 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.026..464.796 rows=270,498 loops=1)

15. 0.012 0.020 ↓ 0.0 0 1

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

16. 0.001 0.008 ↓ 0.0 0 1

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

17. 0.007 0.007 ↓ 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.007..0.007 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.011 0.045 ↓ 0.0 0 1

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

  • Sort Key: purchase_items.product
  • Sort Method: quicksort Memory: 25kB
20. 0.034 0.034 ↓ 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.034..0.034 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.565 3.662 ↑ 1.0 4,867 1

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

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

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

26. 0.013 0.032 ↑ 1.0 33 1

Hash (cost=1.33..1.33 rows=33 width=25) (actual time=0.032..0.032 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)