explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Aufe : Optimization for: Cal2 q10 analyze; plan #kIwD

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 190.942 150,057.057 ↑ 2.7 373,853 1

───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────Unique (cost=1,174,170.00..1,181,681.22 rows=1,001,496 width=36) (actual time=149,712.951..150,057.057 rows=373,853 loops=1)

2. 378.866 149,866.115 ↑ 2.6 377,956 1

Sort (cost=1,174,170.00..1,176,673.74 rows=1,001,496 width=36) (actual time=149,712.948..149,866.115 rows=377,956 loops=1)

  • Sort Key: staff.id, staff.first_name
  • Sort Method: external merge Disk: 10008kB
3. 57.140 149,487.249 ↑ 2.6 377,956 1

Append (cost=967,531.25..1,019,577.27 rows=1,001,496 width=36) (actual time=96,229.438..149,487.249 rows=377,956 loops=1)

4. 488.724 97,250.739 ↑ 2.7 366,824 1

Unique (cost=967,531.25..978,332.70 rows=1,000,000 width=17) (actual time=96,229.437..97,250.739 rows=366,824 loops=1)

5. 2,541.344 96,762.015 ↓ 1.0 1,490,989 1

Sort (cost=967,531.25..971,131.74 rows=1,440,193 width=17) (actual time=96,229.435..96,762.015 rows=1,490,989 loops=1)

  • Sort Key: staff.id, staff.first_name
  • Sort Method: external merge Disk: 39456kB
6. 606.760 94,220.671 ↓ 1.0 1,490,989 1

Gather (cost=136,049.93..761,142.13 rows=1,440,193 width=17) (actual time=93,101.914..94,220.671 rows=1,490,989 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 1,357.232 93,613.911 ↑ 1.2 496,996 3 / 3

Parallel Hash Join (cost=135,049.93..616,122.83 rows=600,080 width=17) (actual time=92,823.890..93,613.911 rows=496,996 loops=3)

  • Hash Cond: (invoices.employee_id = staff.id)
8. 4,082.351 91,555.525 ↑ 1.2 496,996 3 / 3

Parallel Hash Join (cost=111,868.92..584,234.61 rows=600,080 width=4) (actual time=89,523.055..91,555.525 rows=496,996 loops=3)

  • Hash Cond: (invoice_items.product_code = products.code)
9. 25,837.281 86,942.086 ↑ 1.2 945,257 3 / 3

Parallel Hash Join (cost=93,861.20..548,700.37 rows=1,147,621 width=19) (actual time=73,112.985..86,942.086 rows=945,257 loops=3)

  • Hash Cond: (invoice_items.invoice_number = invoices.number)
10. 47,985.595 47,985.595 ↑ 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=48.685..47,985.595 rows=9,165,172 loops=3)

11. 148.194 13,119.210 ↑ 1.2 171,733 3 / 3

Parallel Hash (cost=90,443.55..90,443.55 rows=208,292 width=8) (actual time=13,119.209..13,119.210 rows=171,733 loops=3)

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

Parallel Seq Scan on invoices (cost=0.00..90,443.55 rows=208,292 width=8) (actual time=12,510.670..12,971.016 rows=171,733 loops=3)

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

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

  • Buckets: 131072 Batches: 16 Memory Usage: 2624kB
14. 351.953 351.953 ↑ 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.089..351.953 rows=175,214 loops=3)

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

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

  • Buckets: 65536 Batches: 16 Memory Usage: 4000kB
16. 182.289 182.289 ↑ 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.042..182.289 rows=333,333 loops=3)

17. 105.000 52,179.370 ↓ 7.4 11,132 1

Unique (cost=26,210.91..26,222.13 rows=1,496 width=17) (actual time=51,979.672..52,179.370 rows=11,132 loops=1)

18. 589.086 52,074.370 ↓ 249.1 372,603 1

Sort (cost=26,210.91..26,214.65 rows=1,496 width=17) (actual time=51,979.669..52,074.370 rows=372,603 loops=1)

  • Sort Key: staff_1.id, staff_1.first_name
  • Sort Method: external merge Disk: 9872kB
19. 33.847 51,485.284 ↓ 249.1 372,603 1

Gather (cost=7,585.35..26,132.02 rows=1,496 width=17) (actual time=48,608.556..51,485.284 rows=372,603 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
20. 200.238 51,451.437 ↓ 199.4 124,201 3 / 3

Parallel Hash Join (cost=6,585.35..24,982.42 rows=623 width=17) (actual time=48,466.050..51,451.437 rows=124,201 loops=3)

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

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

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

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

  • Buckets: 262144 (originally 1024) Batches: 1 (originally 1) Memory Usage: 9976kB
23. 447.917 46,797.767 ↓ 141.6 49,829 3 / 3

Nested Loop (cost=287.23..6,580.95 rows=352 width=8) (actual time=82.733..46,797.767 rows=49,829 loops=3)

24. 126.183 40,500.347 ↓ 141.4 79,047 3 / 3

Nested Loop (cost=286.81..5,609.37 rows=559 width=23) (actual time=65.942..40,500.347 rows=79,047 loops=3)

25. 9.147 206.744 ↓ 136.2 1,498 3 / 3

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

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

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

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

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

28. 0.015 2.419 ↑ 1.0 1 3 / 3

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

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

Hash Join (cost=20.68..22.33 rows=1 width=12) (actual time=2.386..2.404 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.094..0.112 rows=52 loops=3)

31. 2.180 2.180 ↑ 4.0 1 3 / 3

Hash (cost=20.62..20.62 rows=4 width=4) (actual time=2.179..2.180 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=2.147..2.154 rows=1 loops=3…)
  • Filter: (name = 'Comunidad de Madrid'::text)
  • Rows Removed by Filter: 18
32. 8.855 34.462 ↑ 1.0 8,116 3 / 3

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

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

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

34. 40,167.420 40,167.420 ↑ 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.690..26.820 rows=53 loops=4,493)

  • Index Cond: (store = stores.id)
  • Filter: (quantity > 100)
  • Rows Removed by Filter: 48
35. 5,849.503 5,849.503 ↑ 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.074..0.074 rows=1 loops=237,142)

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