explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 6.626 2,945.267 ↑ 1.0 30 1

Limit (cost=279,548.72..279,548.80 rows=30 width=3,798) (actual time=2,938.642..2,945.267 rows=30 loops=1)

2. 294.789 2,938.641 ↑ 3,036.7 30 1

Sort (cost=279,548.72..279,776.47 rows=91,101 width=3,798) (actual time=2,938.640..2,938.641 rows=30 loops=1)

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

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

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

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

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

Merge Left Join (cost=1,088.36..275,030.47 rows=91,101 width=3,706) (actual time=10.322..2,210.151 rows=154,916 loops=1)

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

Merge Left Join (cost=1,001.86..274,716.15 rows=91,101 width=3,660) (actual time=10.276..2,029.749 rows=154,916 loops=1)

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

Merge Left Join (cost=1,001.57..274,394.86 rows=91,101 width=3,518) (actual time=10.259..1,859.615 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 994.352 ↓ 1.4 155,189 1

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

  • Workers Planned: 2
  • Workers Launched: 2
9. 348.634 2,441.802 ↓ 1.1 51,730 3

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

10. 272.845 1,472.412 ↓ 1.1 51,730 3

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

11. 888.063 888.063 ↓ 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.442..296.021 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. 462.033 462.033 ↑ 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.029..462.033 rows=270,498 loops=1)

15. 0.002 0.008 ↓ 0.0 0 1

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

16. 0.001 0.006 ↓ 0.0 0 1

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

17. 0.005 0.005 ↓ 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.005..0.005 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.007 0.036 ↓ 0.0 0 1

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

  • Sort Key: purchase_items.product
  • Sort Method: quicksort Memory: 25kB
20. 0.029 0.029 ↓ 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.029..0.029 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. 2.289 4.849 ↑ 1.0 4,867 1

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

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

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

26. 0.029 0.053 ↑ 1.0 33 1

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

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

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