explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 245.137 149,616.428 ↑ 2.7 373,853 1

Unique (cost=1,174,077.85..1,181,589.07 rows=1,001,496 width=36) (actual time=149,167.535..149,616.428 rows=373,853 loops=1)

2. 475.432 149,371.291 ↑ 2.6 377,956 1

Sort (cost=1,174,077.85..1,176,581.59 rows=1,001,496 width=36) (actual time=149,167.531..149,371.291 rows=377,956 loops=1)

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

Append (cost=967,441.54..1,019,485.12 rows=1,001,496 width=36) (actual time=94,998.608..148,895.859 rows=377,956 loops=1)

4. 532.298 96,128.422 ↑ 2.7 366,824 1

Unique (cost=967,441.54..978,240.55 rows=1,000,000 width=17) (actual time=94,998.586..96,128.422 rows=366,824 loops=1)

5. 2,711.226 95,596.124 ↓ 1.0 1,490,989 1

Sort (cost=967,441.54..971,041.21 rows=1,439,868 width=17) (actual time=94,998.583..95,596.124 rows=1,490,989 loops=1)

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

Gather (cost=136,049.34..761,102.01 rows=1,439,868 width=17) (actual time=91,606.559..92,884.898 rows=1,490,989 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 1,569.190 92,506.400 ↑ 1.2 496,996 3 / 3

Parallel Hash Join (cost=135,049.34..616,115.21 rows=599,945 width=17) (actual time=91,751.161..92,506.400 rows=496,996 loops=3)

  • Hash Cond: (invoices.employee_id = staff.id)
8. 3,145.699 90,196.007 ↑ 1.2 496,996 3 / 3

Parallel Hash Join (cost=111,868.34..584,229.34 rows=599,945 width=4) (actual time=88,123.730..90,196.007 rows=496,996 loops=3)

  • Hash Cond: (invoice_items.product_code = products.code)
9. 26,831.950 86,632.502 ↑ 1.2 945,257 3 / 3

Parallel Hash Join (cost=93,860.61..548,699.78 rows=1,147,361 width=19) (actual time=73,101.655..86,632.502 rows=945,257 loops=3)

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

11. 136.663 13,558.523 ↑ 1.2 171,733 3 / 3

Parallel Hash (cost=90,443.55..90,443.55 rows=208,245 width=8) (actual time=13,558.522..13,558.523 rows=171,733 loops=3)

  • Buckets: 131072 Batches: 8 Memory Usage: 3584kB
12. 13,421.860 13,421.860 ↑ 1.2 171,733 3 / 3

Parallel Seq Scan on invoices (cost=0.00..90,443.55 rows=208,245 width=8) (actual time=13,001.923..13,421.860 rows=171,733 loops=3)

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

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

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

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

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

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

17. 144.599 52,704.312 ↓ 7.4 11,132 1

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

18. 686.920 52,559.713 ↓ 249.1 372,603 1

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

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

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

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

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

  • Hash Cond: (staff_1.store = stores.id)
21. 2,915.194 2,915.194 ↑ 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=9.138..2,915.194 rows=166,639 loops=3)

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

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

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

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

24. 127.328 40,152.474 ↓ 141.4 79,047 3 / 3

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

25. 8.901 208.180 ↓ 136.2 1,498 3 / 3

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

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

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

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

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

28. 0.018 3.033 ↑ 1.0 1 3 / 3

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

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

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

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

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

31. 2.800 2.800 ↑ 4.0 1 3 / 3

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

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

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

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

34. 39,816.966 39,816.966 ↑ 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.782..26.586 rows=53 loops=4,493)

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

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