explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2Uhl : purchases after order by index

Settings
# exclusive inclusive rows x rows loops node
1. 10.480 2,902.883 ↑ 1.0 30 1

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

2. 285.430 2,892.403 ↑ 3,036.7 30 1

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

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

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

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

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

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

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

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

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

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

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

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

  • Workers Planned: 2
  • Workers Launched: 2
9. 336.685 2,393.946 ↓ 1.1 51,730 3

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

10. 253.312 1,436.505 ↓ 1.1 51,730 3

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

11. 871.689 871.689 ↓ 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.431..290.563 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. 450.410 450.410 ↑ 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.012..450.410 rows=270,498 loops=1)

15. 0.002 0.005 ↓ 0.0 0 1

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

16. 0.000 0.003 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.28..91.78 rows=210 width=146) (actual time=0.003..0.003 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.005 0.022 ↓ 0.0 0 1

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

  • Sort Key: purchase_items.product
  • Sort Method: quicksort Memory: 25kB
20. 0.017 0.017 ↓ 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.017..0.017 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.225 2.624 ↑ 1.0 4,867 1

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

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

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

26. 0.008 0.022 ↑ 1.0 33 1

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

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

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