explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 195.273 128,413.116 ↑ 2.7 373,853 1

───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────Unique (cost=1,127,262.70..1,134,892.52 rows=1,017,310 width=36) (actual time=128,055.694..128,413.116 rows=373,853 loops=1)

2. 486.375 128,217.843 ↑ 2.7 377,956 1

Sort (cost=1,127,262.70..1,129,805.97 rows=1,017,310 width=36) (actual time=128,055.691..128,217.843 rows=377,956 loops=1)

  • Sort Key: staff.id, staff.first_name
  • Sort Method: external merge Disk: 10008kB
3. 58.098 127,731.468 ↑ 2.7 377,956 1

Append (cost=891,178.12..970,117.84 rows=1,017,310 width=36) (actual time=74,684.785..127,731.468 rows=377,956 loops=1)

4. 507.377 75,739.087 ↑ 2.7 366,824 1

Unique (cost=891,178.12..901,912.90 rows=1,000,000 width=17) (actual time=74,684.784..75,739.087 rows=366,824 loops=1)

5. 2,445.138 75,231.710 ↓ 1.0 1,490,989 1

Sort (cost=891,178.12..894,756.38 rows=1,431,305 width=17) (actual time=74,684.782..75,231.710 rows=1,490,989 loops=1)

  • Sort Key: staff.id, staff.first_name
  • Sort Method: external merge Disk: 39456kB
6. 773.423 72,786.572 ↓ 1.0 1,490,989 1

Gather (cost=62,161.81..686,126.05 rows=1,431,305 width=17) (actual time=71,554.574..72,786.572 rows=1,490,989 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 1,545.604 72,013.149 ↑ 1.2 496,996 3 / 3

Parallel Hash Join (cost=61,161.81..541,995.55 rows=596,377 width=17) (actual time=71,129.967..72,013.149 rows=496,996 loops=3)

  • Hash Cond: (invoices.employee_id = staff.id)
8. 2,762.558 69,868.395 ↑ 1.2 496,996 3 / 3

Parallel Hash Join (cost=37,980.81..510,147.05 rows=596,377 width=4) (actual time=68,221.680..69,868.395 rows=496,996 loops=3)

  • Hash Cond: (invoice_items.product_code = products.code)
9. 24,658.386 66,746.864 ↑ 1.2 945,257 3 / 3

Parallel Hash Join (cost=19,950.00..474,780.89 rows=1,133,842 width=19) (actual time=54,471.196..66,746.864 rows=945,257 loops=3)

  • Hash Cond: (invoice_items.invoice_number = invoices.number)
10. 41,203.510 41,203.510 ↑ 1.2 9,165,172 3 / 3

Parallel Seq Scan on invoice_items (cost=0.00..289,695.65 rows=11,456,465 width=19) (actual time=10.929..41,203.510 rows=9,165,172 loops=3)

11. 884.968 884.968 ↑ 1.2 171,733 3 / 3

Parallel Hash (cost=16,566.66..16,566.66 rows=206,187 width=8) (actual time=884.968..884.968 rows=171,733 loops=3)

  • Buckets: 131072 Batches: 8 Memory Usage: 3616kB
  • -> Parallel Index Scan using invoices_date_idx on invoices (cost=0.44..16566.66 rows=206187 width=8) (actual time=156.145..634.308 rows=171733 lo…ops=3)
  • Index Cond: (date > (now() - '4 mons'::interval))
12. 147.801 358.973 ↑ 1.3 175,214 3 / 3

Parallel Hash (cost=14,220.33..14,220.33 rows=219,158 width=15) (actual time=358.973..358.973 rows=175,214 loops=3)

  • Buckets: 131072 Batches: 16 Memory Usage: 2624kB
13. 211.172 211.172 ↑ 1.3 175,214 3 / 3

Parallel Seq Scan on products (cost=0.00..14,220.33 rows=219,158 width=15) (actual time=0.031..211.172 rows=175,214 loops=3)

  • Filter: (price > 500)
  • Rows Removed by Filter: 158119
14. 400.002 599.150 ↑ 1.3 333,333 3 / 3

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

  • Buckets: 65536 Batches: 16 Memory Usage: 3968kB
15. 199.148 199.148 ↑ 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.038..199.148 rows=333,333 loops=3)

16. 534.099 51,934.283 ↑ 1.6 11,132 1

HashAggregate (cost=52,772.19..52,945.29 rows=17,310 width=17) (actual time=51,928.441..51,934.283 rows=11,132 loops=1)

  • Group Key: staff_1.id, staff_1.first_name
17. 0.000 51,400.184 ↓ 21.5 372,603 1

Gather (cost=32,513.68..52,685.64 rows=17,310 width=17) (actual time=48,704.456..51,400.184 rows=372,603 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
18. 196.183 51,505.198 ↓ 17.2 124,201 3 / 3

Parallel Hash Join (cost=31,513.68..49,954.64 rows=7,212 width=17) (actual time=48,619.082..51,505.198 rows=124,201 loops=3)

  • Hash Cond: (staff_1.store = stores.id)
19. 2,701.583 2,701.583 ↑ 1.3 166,639 3 / 3

Parallel Seq Scan on staff staff_1 (cost=0.00..17,614.00 rows=209,215 width=21) (actual time=10.685..2,701.583 rows=166,639 loops=3)

  • Filter: ((salary >= 3000) AND (salary <= 5000))
  • Rows Removed by Filter: 166694
20. 1,258.693 48,607.432 ↓ 12.3 49,829 3 / 3

Parallel Hash (cost=31,463.02..31,463.02 rows=4,053 width=8) (actual time=48,607.432..48,607.432 rows=49,829 loops=3)

  • Buckets: 262144 (originally 8192) Batches: 1 (originally 1) Memory Usage: 9920kB
21. 462.147 47,348.739 ↓ 12.3 49,829 3 / 3

Nested Loop (cost=267.86..31,463.02 rows=4,053 width=8) (actual time=55.191..47,348.739 rows=49,829 loops=3)

22. 127.176 40,958.042 ↓ 12.4 79,047 3 / 3

Nested Loop (cost=267.44..20,355.40 rows=6,394 width=23) (actual time=41.596..40,958.042 rows=79,047 loops=3)

23. 8.693 176.704 ↓ 12.6 1,498 3 / 3

Hash Join (cost=266.88..4,152.50 rows=119 width=4) (actual time=11.016..176.704 rows=1,498 loops=3)

  • Hash Cond: ((stores.city = sp_cities.name) AND (sp_provinces.id = sp_cities.province))
24. 63.534 157.418 ↑ 4.1 1,498 3 / 3

Hash Join (cost=2.98..3,825.49 rows=6,192 width=21) (actual time=0.358..157.418 rows=1,498 loops=3)

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

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

26. 0.008 0.192 ↑ 3.0 1 3 / 3

Hash (cost=2.94..2.94 rows=3 width=12) (actual time=0.191..0.192 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.049 0.184 ↑ 3.0 1 3 / 3

Hash Join (cost=1.25..2.94 rows=3 width=12) (actual time=0.170..0.184 rows=1 loops=3)

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

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

29. 0.015 0.075 ↑ 1.0 1 3 / 3

Hash (cost=1.24..1.24 rows=1 width=4) (actual time=0.075..0.075 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.060 0.060 ↑ 1.0 1 3 / 3

Seq Scan on sp_autonomous_communities (cost=0.00..1.24 rows=1 width=4) (actual time=0.057..0.060 rows=1 loops=3)

  • Filter: (name = 'Comunidad de Madrid'::text)
  • Rows Removed by Filter: 18
31. 6.249 10.593 ↑ 1.0 8,116 3 / 3

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

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

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

33. 40,654.162 40,654.162 ↑ 1.4 53 4,493 / 3

Index Scan using shops_stock_pk on stock_stores (cost=0.56..135.44 rows=72 width=19) (actual time=2.761..27.145 rows=53 loops=4,493)

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

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