explain.depesz.com

PostgreSQL's explain analyze made readable

Result: o8rT

Settings
# exclusive inclusive rows x rows loops node
1. 0.375 541,964.747 ↓ 30.0 30 1

Limit (cost=208,500.08..208,500.09 rows=1 width=3,853) (actual time=541,964.373..541,964.747 rows=30 loops=1)

2. 1,049.135 541,964.372 ↓ 30.0 30 1

Sort (cost=208,500.08..208,500.09 rows=1 width=3,853) (actual time=541,964.371..541,964.372 rows=30 loops=1)

  • Sort Key: p.educational_book DESC NULLS LAST, p.created DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 245kB
3. 2,080.475 540,915.237 ↓ 351,697.0 351,697 1

Nested Loop Left Join (cost=1,001.70..208,500.07 rows=1 width=3,853) (actual time=50.222..540,915.237 rows=351,697 loops=1)

  • Join Filter: (srv.product = p.product_id)
  • Rows Removed by Join Filter: 10550883
4. 167,726.328 537,779.671 ↓ 351,697.0 351,697 1

Nested Loop Left Join (cost=1,001.70..208,498.39 rows=1 width=3,731) (actual time=50.197..537,779.671 rows=351,697 loops=1)

  • Join Filter: ((s.subject)::text = (p.subject)::text)
  • Rows Removed by Join Filter: 1711357606
5. 735.547 234,649.998 ↓ 351,697.0 351,697 1

Nested Loop Left Join (cost=1,001.70..208,266.89 rows=1 width=3,731) (actual time=50.133..234,649.998 rows=351,697 loops=1)

  • Join Filter: (rt.resource_type_id = r.resource_type_id)
  • Rows Removed by Join Filter: 1406788
6. 846.282 233,562.754 ↓ 351,697.0 351,697 1

Nested Loop Left Join (cost=1,001.70..208,265.80 rows=1 width=3,699) (actual time=50.100..233,562.754 rows=351,697 loops=1)

  • Join Filter: (r.product_id = p.product_id)
7. 567.800 232,716.472 ↓ 351,697.0 351,697 1

Nested Loop Left Join (cost=1,001.70..208,245.00 rows=1 width=3,562) (actual time=50.092..232,716.472 rows=351,697 loops=1)

  • Join Filter: (purchase_items.license = l.license_id)
8. 74,364.733 231,445.278 ↓ 351,697.0 351,697 1

Nested Loop (cost=1,001.28..208,070.57 rows=1 width=3,510) (actual time=50.052..231,445.278 rows=351,697 loops=1)

  • Join Filter: (p.publisher = pub.publisher_id)
  • Rows Removed by Join Filter: 1006806378
9. 912.815 5,061.793 ↓ 355,184.0 355,184 1

Nested Loop Left Join (cost=1,001.28..207,454.74 rows=1 width=2,761) (actual time=49.847..5,061.793 rows=355,184 loops=1)

  • Filter: ((eb.publication_date IS NULL) OR (eb.publication_date <= now()))
  • Rows Removed by Filter: 509
10. 0.000 1,303.434 ↓ 177,846.5 355,693 1

Gather (cost=1,000.85..207,450.57 rows=2 width=1,336) (actual time=49.802..1,303.434 rows=355,693 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
11. 2,763.648 6,091.428 ↓ 118,564.0 118,564 3

Nested Loop (cost=0.85..206,450.37 rows=1 width=1,336) (actual time=43.943..6,091.428 rows=118,564 loops=3)

12. 2,556.716 3,327.767 ↓ 93,872.5 187,745 3

Nested Loop (cost=0.43..206,448.37 rows=2 width=253) (actual time=43.522..3,327.767 rows=187,745 loops=3)

13. 771.045 771.045 ↓ 34,157.5 375,733 3

Parallel Seq Scan on licenses l (cost=0.00..206,355.36 rows=11 width=53) (actual time=0.238..771.045 rows=375,733 loops=3)

  • Filter: ((NOT deleted) AND (array_length(schools, 1) IS NULL) AND (array_length(school_groups, 1) IS NULL))
  • Rows Removed by Filter: 928495
14. 0.006 0.006 ↓ 0.0 0 1,127,199

Index Scan using product_prices_license_idx on product_prices pp (cost=0.43..8.45 rows=1 width=200) (actual time=0.006..0.006 rows=0 loops=1,127,199)

  • Index Cond: (license = l.license_id)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
15. 0.013 0.013 ↑ 1.0 1 563,235

Index Scan using products_product_id_idx on products p (cost=0.42..1.00 rows=1 width=1,083) (actual time=0.013..0.013 rows=1 loops=563,235)

  • Index Cond: (product_id = pp.product)
  • Filter: (active AND approved AND (NOT discontinued) AND (regions && '{ALL,ZA}'::character varying[]) AND (NOT (study_year && '{TE}'::character varying[])) AND (markets && '{2}'::integer[]))
  • Rows Removed by Filter: 0
16. 2,845.544 2,845.544 ↑ 1.0 1 355,693

Index Scan using idx_ebooks_product_id on ebooks eb (cost=0.42..2.07 rows=1 width=1,425) (actual time=0.008..0.008 rows=1 loops=355,693)

  • Index Cond: (product_id = p.product_id)
17. 152,018.752 152,018.752 ↑ 5.2 2,836 355,184

Seq Scan on publishers pub (cost=0.00..430.01 rows=14,866 width=749) (actual time=0.001..0.428 rows=2,836 loops=355,184)

  • Filter: (NOT private)
  • Rows Removed by Filter: 294
18. 703.394 703.394 ↓ 0.0 0 351,697

Index Scan using purchase_items_bought_for_idx on purchase_items (cost=0.42..174.24 rows=15 width=52) (actual time=0.002..0.002 rows=0 loops=351,697)

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

SubPlan (forIndex Scan)

20. 0.000 0.000 ↓ 0.0 0

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

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

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

  • Index Cond: (purchase_items.purchase = purchase_id)
  • Filter: ((status = 4) OR (status = 6) OR (status = 8))
22. 0.000 0.000 ↓ 0.0 0 351,697

Seq Scan on resources r (cost=0.00..14.80 rows=480 width=141) (actual time=0.000..0.000 rows=0 loops=351,697)

23. 351.697 351.697 ↑ 1.0 4 351,697

Seq Scan on resource_types rt (cost=0.00..1.04 rows=4 width=36) (actual time=0.000..0.001 rows=4 loops=351,697)

24. 135,403.345 135,403.345 ↑ 1.0 4,867 351,697

Seq Scan on subjects s (cost=0.00..170.67 rows=4,867 width=39) (actual time=0.001..0.385 rows=4,867 loops=351,697)

25. 1,055.091 1,055.091 ↑ 1.0 30 351,697

Seq Scan on services srv (cost=0.00..1.30 rows=30 width=36) (actual time=0.001..0.003 rows=30 loops=351,697)

Planning time : 111.242 ms
Execution time : 541,965.353 ms