explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kIwD : Cal2 q10 analyze

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 203.781 149,075.122 ↑ 2.7 373,853 1

───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────Unique (cost=1,174,632.49..1,182,143.71 rows=1,001,496 width=36) (actual time=148,709.850..149,075.122 rows=373,853 loops=1)

2. 398.496 148,871.341 ↑ 2.6 377,956 1

Sort (cost=1,174,632.49..1,177,136.23 rows=1,001,496 width=36) (actual time=148,709.847..148,871.341 rows=377,956 loops=1)

  • Sort Key: staff.id, staff.first_name
  • Sort Method: external merge Disk: 10008kB
3. 58.642 148,472.845 ↑ 2.6 377,956 1

Append (cost=967,982.98..1,020,039.76 rows=1,001,496 width=36) (actual time=94,280.531..148,472.845 rows=377,956 loops=1)

4. 501.844 95,330.995 ↑ 2.7 366,824 1

Unique (cost=967,982.98..978,797.00 rows=1,000,000 width=17) (actual time=94,280.529..95,330.995 rows=366,824 loops=1)

5. 2,732.588 94,829.151 ↓ 1.0 1,490,989 1

Sort (cost=967,982.98..971,587.65 rows=1,441,870 width=17) (actual time=94,280.527..94,829.151 rows=1,490,989 loops=1)

  • Sort Key: staff.id, staff.first_name
  • Sort Method: external merge Disk: 39456kB
6. 786.881 92,096.563 ↓ 1.0 1,490,989 1

Gather (cost=136,053.96..761,340.21 rows=1,441,870 width=17) (actual time=90,874.503..92,096.563 rows=1,490,989 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 1,534.772 91,309.682 ↑ 1.2 496,996 3 / 3

Parallel Hash Join (cost=135,053.96..616,153.21 rows=600,779 width=17) (actual time=90,455.000..91,309.682 rows=496,996 loops=3)

  • Hash Cond: (invoices.employee_id = staff.id)
8. 3,976.522 88,770.495 ↑ 1.2 496,996 3 / 3

Parallel Hash Join (cost=111,872.96..584,259.15 rows=600,779 width=4) (actual time=86,639.793..88,770.495 rows=496,996 loops=3)

  • Hash Cond: (invoice_items.product_code = products.code)
9. 24,841.707 84,251.867 ↑ 1.2 945,257 3 / 3

Parallel Hash Join (cost=93,865.24..548,705.41 rows=1,148,957 width=19) (actual time=71,903.468..84,251.867 rows=945,257 loops=3)

  • Hash Cond: (invoice_items.invoice_number = invoices.number)
10. 47,165.013 47,165.013 ↑ 1.3 9,165,172 3 / 3

Parallel Seq Scan on invoice_items (cost=0.00..289,695.87 rows=11,456,487 width=19) (actual time=52.507..47,165.013 rows=9,165,172 loops=3)

11. 145.405 12,245.147 ↑ 1.2 171,733 3 / 3

Parallel Hash (cost=90,443.55..90,443.55 rows=208,535 width=8) (actual time=12,245.147..12,245.147 rows=171,733 loops=3)

  • Buckets: 131072 Batches: 8 Memory Usage: 3616kB
12. 12,099.742 12,099.742 ↑ 1.2 171,733 3 / 3

Parallel Seq Scan on invoices (cost=0.00..90,443.55 rows=208,535 width=8) (actual time=11,622.258..12,099.742 rows=171,733 loops=3)

  • Filter: (date > (now() - '4 mons'::interval))
  • Rows Removed by Filter: 1494934
13. 145.157 542.106 ↑ 1.2 175,214 3 / 3

Parallel Hash (cost=14,220.33..14,220.33 rows=217,871 width=15) (actual time=542.106..542.106 rows=175,214 loops=3)

  • Buckets: 131072 Batches: 16 Memory Usage: 2624kB
14. 396.949 396.949 ↑ 1.2 175,214 3 / 3

Parallel Seq Scan on products (cost=0.00..14,220.33 rows=217,871 width=15) (actual time=0.115..396.949 rows=175,214 loops=3)

  • Filter: (price > 500)
  • Rows Removed by Filter: 158119
15. 617.065 1,004.415 ↑ 1.3 333,333 3 / 3

Parallel Hash (cost=15,530.67..15,530.67 rows=416,667 width=17) (actual time=1,004.414..1,004.415 rows=333,333 loops=3)

  • Buckets: 65536 Batches: 16 Memory Usage: 4000kB
16. 387.350 387.350 ↑ 1.3 333,333 3 / 3

Parallel Seq Scan on staff (cost=0.00..15,530.67 rows=416,667 width=17) (actual time=0.095..387.350 rows=333,333 loops=3)

17. 119.247 53,083.208 ↓ 7.4 11,132 1

Unique (cost=26,209.10..26,220.32 rows=1,496 width=17) (actual time=52,854.628..53,083.208 rows=11,132 loops=1)

18. 634.089 52,963.961 ↓ 249.1 372,603 1

Sort (cost=26,209.10..26,212.84 rows=1,496 width=17) (actual time=52,854.624..52,963.961 rows=372,603 loops=1)

  • Sort Key: staff_1.id, staff_1.first_name
  • Sort Method: external merge Disk: 9872kB
19. 24.587 52,329.872 ↓ 249.1 372,603 1

Gather (cost=7,585.35..26,130.21 rows=1,496 width=17) (actual time=49,429.334..52,329.872 rows=372,603 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
20. 208.027 52,305.285 ↓ 199.4 124,201 3 / 3

Parallel Hash Join (cost=6,585.35..24,980.61 rows=623 width=17) (actual time=49,299.873..52,305.285 rows=124,201 loops=3)

  • Hash Cond: (staff_1.store = stores.id)
21. 2,807.865 2,807.865 ↑ 1.2 166,639 3 / 3

Parallel Seq Scan on staff staff_1 (cost=0.00..17,614.00 rows=207,364 width=21) (actual time=9.362..2,807.865 rows=166,639 loops=3)

  • Filter: ((salary >= 3000) AND (salary <= 5000))
  • Rows Removed by Filter: 166694
22. 1,672.009 49,289.393 ↓ 141.6 49,829 3 / 3

Parallel Hash (cost=6,580.95..6,580.95 rows=352 width=8) (actual time=49,289.393..49,289.393 rows=49,829 loops=3)

  • Buckets: 262144 (originally 1024) Batches: 1 (originally 1) Memory Usage: 9976kB
23. 442.385 47,617.384 ↓ 141.6 49,829 3 / 3

Nested Loop (cost=287.23..6,580.95 rows=352 width=8) (actual time=82.796..47,617.384 rows=49,829 loops=3)

24. 129.355 41,088.354 ↓ 141.4 79,047 3 / 3

Nested Loop (cost=286.81..5,609.37 rows=559 width=23) (actual time=67.825..41,088.354 rows=79,047 loops=3)

25. 9.045 217.973 ↓ 136.2 1,498 3 / 3

Hash Join (cost=286.24..4,069.16 rows=11 width=4) (actual time=55.677..217.973 rows=1,498 loops=3)

  • Hash Cond: ((stores.city = sp_cities.name) AND (sp_provinces.id = sp_cities.province))
26. 64.629 170.614 ↓ 2.7 1,498 3 / 3

Hash Join (cost=22.34..3,799.61 rows=554 width=21) (actual time=17.184..170.614 rows=1,498 loops=3)

  • Hash Cond: (stores.province = sp_provinces.name)
27. 102.212 102.212 ↑ 1.8 66,667 3 / 3

Parallel Seq Scan on stores (cost=0.00..3,313.47 rows=117,647 width=25) (actual time=13.116..102.212 rows=66,667 loops=3)

28. 0.022 3.773 ↑ 1.0 1 3 / 3

Hash (cost=22.33..22.33 rows=1 width=12) (actual time=3.772..3.773 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.073 3.751 ↑ 1.0 1 3 / 3

Hash Join (cost=20.68..22.33 rows=1 width=12) (actual time=3.726..3.751 rows=1 loops=3)

  • Hash Cond: (sp_provinces.community = sp_autonomous_communities.id)
30. 0.112 0.112 ↑ 1.0 52 3 / 3

Seq Scan on sp_provinces (cost=0.00..1.52 rows=52 width=16) (actual time=0.095..0.112 rows=52 loops=3)

31. 3.566 3.566 ↑ 4.0 1 3 / 3

Hash (cost=20.62..20.62 rows=4 width=4) (actual time=3.566..3.566 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • -> Seq Scan on sp_autonomous_communities (cost=0.00..20.62 rows=4 width=4) (actual time=3.529..3.535 rows=1 loops=3…)
  • Filter: (name = 'Comunidad de Madrid'::text)
  • Rows Removed by Filter: 18
32. 9.961 38.314 ↑ 1.0 8,116 3 / 3

Hash (cost=142.16..142.16 rows=8,116 width=17) (actual time=38.314..38.314 rows=8,116 loops=3)

  • Buckets: 8192 Batches: 1 Memory Usage: 469kB
33. 28.353 28.353 ↑ 1.0 8,116 3 / 3

Seq Scan on sp_cities (cost=0.00..142.16 rows=8,116 width=17) (actual time=9.901..28.353 rows=8,116 loops=3)

34. 40,741.026 40,741.026 ↑ 1.4 53 4,493 / 3

Index Scan using shops_stock_pk on stock_stores (cost=0.56..139.29 rows=73 width=19) (actual time=2.871..27.203 rows=53 loops=4,493)

  • Index Cond: (store = stores.id)
  • Filter: (quantity > 100)
  • Rows Removed by Filter: 48
35. 6,086.645 6,086.645 ↑ 1.0 1 237,142 / 3

Index Scan using products_pk on products products_1 (cost=0.42..1.74 rows=1 width=15) (actual time=0.077..0.077 rows=1 loops=237,142)

  • Index Cond: (code = stock_stores.product)
  • Filter: (price > 400)
  • Rows Removed by Filter: 0
Planning time : 9.992 ms
Execution time : 149,331.550 ms