explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZmYH

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 87,928.112 ↑ 1.4 72 1

Limit (cost=24,179.59..24,179.84 rows=100 width=24) (actual time=87,928.093..87,928.112 rows=72 loops=1)

2. 0.221 87,928.100 ↑ 29.2 72 1

Sort (cost=24,179.59..24,184.85 rows=2,102 width=24) (actual time=87,928.091..87,928.100 rows=72 loops=1)

  • Sort Key: puppetclasses.name
  • Sort Method: quicksort  Memory: 30kB
3. 32,954.221 87,927.879 ↑ 29.2 72 1

HashAggregate (cost=24,078.23..24,099.25 rows=2,102 width=24) (actual time=87,927.859..87,927.879 rows=72 loops=1)

  • Group Key: puppetclasses.name, puppetclasses.id
4. 29,387.904 54,973.658 ↓ 452.7 100,758,576 1

Hash Left Join (cost=15,784.74..22,965.37 rows=222,572 width=24) (actual time=19,452.537..54,973.658 rows=100,758,576 loops=1)

  • Hash Cond: (environment_classes_puppetclasses_join_2.environment_id = environments_puppetclasses_join_2.id)
  • Join Filter: (environments.id = ANY ('{7,8,4,1}'::integer[]))
  • Rows Removed by Join Filter: 13044303
5. 6,129.558 25,585.685 ↓ 135.5 30,169,430 1

Hash Right Join (cost=15,762.08..18,769.49 rows=222,572 width=32) (actual time=19,452.440..25,585.685 rows=30,169,430 loops=1)

  • Hash Cond: (environment_classes_puppetclasses_join_2.puppetclass_id = puppetclasses.id)
6. 3.999 3.999 ↓ 1.0 28,434 1

Seq Scan on environment_classes environment_classes_puppetclasses_join_2 (cost=0.00..433.53 rows=27,853 width=8) (actual time=0.014..3.999 rows=28,434 loops=1)

7. 87.733 19,452.128 ↓ 16.2 272,262 1

Hash (cost=15,552.12..15,552.12 rows=16,797 width=28) (actual time=19,452.128..19,452.128 rows=272,262 loops=1)

  • Buckets: 524288 (originally 32768)  Batches: 1 (originally 1)  Memory Usage: 20867kB
8. 15,979.828 19,364.395 ↓ 16.2 272,262 1

Hash Left Join (cost=1,256.86..15,552.12 rows=16,797 width=28) (actual time=45.350..19,364.395 rows=272,262 loops=1)

  • Hash Cond: (taxable_taxonomies.taxonomy_id = taxonomies.id)
  • Filter: (((puppetclasses.name)::text ~~* '%postgres%'::text) OR ((environments.name)::text ~~* '%postgres%'::text) OR ((taxonomies.name)::text ~~* '%postgres%'::text) OR ((taxonomies.name)::text ~~*
  • Rows Removed by Filter: 7415017
9. 2,741.091 3,384.551 ↓ 20.8 7,687,279 1

Hash Left Join (cost=1,255.76..13,112.50 rows=369,072 width=548) (actual time=45.299..3,384.551 rows=7,687,279 loops=1)

  • Hash Cond: (environment_classes_puppetclasses_join.environment_id = environments_puppetclasses_join.id)
  • Join Filter: (environments.id = ANY ('{7,8,4,1}'::integer[]))
  • Rows Removed by Join Filter: 3337068
10. 582.526 643.330 ↓ 7.4 2,748,400 1

Hash Right Join (cost=1,233.11..6,169.74 rows=369,072 width=548) (actual time=45.130..643.330 rows=2,748,400 loops=1)

  • Hash Cond: (environment_classes.puppetclass_id = puppetclasses.id)
11. 10.265 16.054 ↓ 1.0 28,434 1

Hash Left Join (cost=1.14..724.81 rows=27,853 width=524) (actual time=0.070..16.054 rows=28,434 loops=1)

  • Hash Cond: (environment_classes.environment_id = environments.id)
12. 5.770 5.770 ↓ 1.0 28,434 1

Seq Scan on environment_classes (cost=0.00..433.53 rows=27,853 width=8) (actual time=0.007..5.770 rows=28,434 loops=1)

13. 0.008 0.019 ↑ 1.0 4 1

Hash (cost=1.09..1.09 rows=4 width=520) (actual time=0.019..0.019 rows=4 loops=1)

  • Buckets: 1024  Batches: 1  Memory Usage: 9kB
14. 0.011 0.011 ↑ 1.0 4 1

Seq Scan on environments (cost=0.00..1.09 rows=4 width=520) (actual time=0.006..0.011 rows=4 loops=1)

  • Filter: (id = ANY ('{7,8,4,1}'::integer[]))
  • Rows Removed by Filter: 2
15. 14.761 44.750 ↓ 1.0 28,434 1

Hash (cost=883.80..883.80 rows=27,853 width=28) (actual time=44.750..44.750 rows=28,434 loops=1)

  • Buckets: 32768  Batches: 1  Memory Usage: 1874kB
16. 20.981 29.989 ↓ 1.0 28,434 1

Hash Right Join (cost=67.30..883.80 rows=27,853 width=28) (actual time=2.014..29.989 rows=28,434 loops=1)

  • Hash Cond: (environment_classes_puppetclasses_join.puppetclass_id = puppetclasses.id)
17. 7.053 7.053 ↓ 1.0 28,434 1

Seq Scan on environment_classes environment_classes_puppetclasses_join (cost=0.00..433.53 rows=27,853 width=8) (actual time=0.010..7.053 rows=28,434 loops=1)

18. 1.162 1.955 ↓ 1.0 2,182 1

Hash (cost=41.02..41.02 rows=2,102 width=24) (actual time=1.955..1.955 rows=2,182 loops=1)

  • Buckets: 4096  Batches: 1  Memory Usage: 158kB
19. 0.793 0.793 ↓ 1.0 2,182 1

Seq Scan on puppetclasses (cost=0.00..41.02 rows=2,102 width=24) (actual time=0.009..0.793 rows=2,182 loops=1)

20. 0.024 0.130 ↓ 3.2 19 1

Hash (cost=22.58..22.58 rows=6 width=8) (actual time=0.130..0.130 rows=19 loops=1)

  • Buckets: 1024  Batches: 1  Memory Usage: 9kB
21. 0.046 0.106 ↓ 3.2 19 1

Hash Right Join (cost=1.55..22.58 rows=6 width=8) (actual time=0.077..0.106 rows=19 loops=1)

  • Hash Cond: (taxable_taxonomies.taxable_id = environments_puppetclasses_join.id)
22. 0.043 0.043 ↑ 1.0 18 1

Index Only Scan using taxable_index on taxable_taxonomies (cost=0.41..21.37 rows=18 width=8) (actual time=0.032..0.043 rows=18 loops=1)

  • Index Cond: (taxable_type = 'Environment'::text)
  • Heap Fetches: 5
23. 0.013 0.017 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=4) (actual time=0.017..0.017 rows=6 loops=1)

  • Buckets: 1024  Batches: 1  Memory Usage: 9kB
24. 0.004 0.004 ↑ 1.0 6 1

Seq Scan on environments environments_puppetclasses_join (cost=0.00..1.06 rows=6 width=4) (actual time=0.002..0.004 rows=6 loops=1)

25. 0.006 0.016 ↑ 1.0 1 1

Hash (cost=1.09..1.09 rows=1 width=520) (actual time=0.016..0.016 rows=1 loops=1)

  • Buckets: 1024  Batches: 1  Memory Usage: 9kB
26. 0.010 0.010 ↑ 1.0 1 1

Seq Scan on taxonomies (cost=0.00..1.09 rows=1 width=520) (actual time=0.007..0.010 rows=1 loops=1)

  • Filter: ((type)::text = 'Organization'::text)
  • Rows Removed by Filter: 6
27. 0.003 0.069 ↓ 3.2 19 1

Hash (cost=22.58..22.58 rows=6 width=4) (actual time=0.069..0.069 rows=19 loops=1)

  • Buckets: 1024  Batches: 1  Memory Usage: 9kB
28. 0.019 0.066 ↓ 3.2 19 1

Hash Right Join (cost=1.55..22.58 rows=6 width=4) (actual time=0.050..0.066 rows=19 loops=1)

  • Hash Cond: (taxable_taxonomies_puppetclasses_join.taxable_id = environments_puppetclasses_join_2.id)
29. 0.038 0.038 ↑ 1.0 18 1

Index Only Scan using taxable_index on taxable_taxonomies taxable_taxonomies_puppetclasses_join (cost=0.41..21.37 rows=18 width=8) (actual time=0.029..0.038 rows=18 loops=1)

  • Index Cond: (taxable_type = 'Environment'::text)
  • Heap Fetches: 5
30. 0.005 0.009 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=4) (actual time=0.009..0.009 rows=6 loops=1)

  • Buckets: 1024  Batches: 1  Memory Usage: 9kB
31. 0.004 0.004 ↑ 1.0 6 1

Seq Scan on environments environments_puppetclasses_join_2 (cost=0.00..1.06 rows=6 width=4) (actual time=0.002..0.004 rows=6 loops=1)

Planning time : 3.856 ms
Execution time : 87,930.021 ms