explain-analyze.sql

irb(main):032:0> result = ActiveRecord::Base.connection.execute(sql)
irb(main):033:0> (0...result.ntuples).each { |i| puts result.getvalue(i, 0) }
Sort  (cost=534.90..535.03 rows=270 width=144) (actual time=1.648..1.669 rows=443 loops=1)
  Sort Key: universes.page_type, ((universes.id)::bigint)
  Sort Method: quicksort  Memory: 87kB
  ->  Append  (cost=0.08..532.72 rows=270 width=144) (actual time=0.028..1.260 rows=443 loops=1)
        ->  Result  (cost=0.08..219.59 rows=120 width=144) (actual time=0.028..0.612 rows=247 loops=1)
              ->  Append  (cost=0.08..219.11 rows=120 width=136) (actual time=0.027..0.580 rows=247 loops=1)
                    ->  Index Scan using index_universes_on_deleted_at_and_user_id on universes  (cost=0.08..82.13 rows=46 width=70) (actual time=0.027..0.139 rows=38 loops=1)
                          Index Cond: ((deleted_at IS NULL) AND (user_id = 5))
                          Filter: (archived_at IS NULL)
                    ->  Index Scan using index_characters_on_user_id_and_universe_id_and_deleted_at on characters  (cost=0.08..29.04 rows=15 width=71) (actual time=0.017..0.273 rows=122 loops=1)
                          Index Cond: ((user_id = 5) AND (deleted_at IS NULL))
                          Filter: (archived_at IS NULL)
                          Rows Removed by Filter: 3
                    ->  Index Scan using index_locations_on_deleted_at_and_user_id on locations  (cost=0.08..100.01 rows=56 width=69) (actual time=0.025..0.095 rows=67 loops=1)
                          Index Cond: ((deleted_at IS NULL) AND (user_id = 5))
                          Filter: (archived_at IS NULL)
                    ->  Index Scan using index_items_on_deleted_at_and_user_id on items  (cost=0.08..7.57 rows=3 width=68) (actual time=0.018..0.054 rows=20 loops=1)
                          Index Cond: ((deleted_at IS NULL) AND (user_id = 5))
                          Filter: (archived_at IS NULL)
        ->  Bitmap Heap Scan on buildings  (cost=2.06..5.68 rows=2 width=80) (actual time=0.009..0.009 rows=1 loops=1)
              Recheck Cond: (user_id = 5)
              Filter: ((deleted_at IS NULL) AND (archived_at IS NULL))
              Heap Blocks: exact=1
              ->  Bitmap Index Scan on index_buildings_on_user_id  (cost=0.00..2.06 rows=2 width=0) (actual time=0.006..0.006 rows=1 loops=1)
                    Index Cond: (user_id = 5)
        ->  Index Scan using index_conditions_on_user_id on conditions  (cost=0.06..4.06 rows=1 width=78) (actual time=0.010..0.010 rows=0 loops=1)
              Index Cond: (user_id = 5)
              Filter: ((deleted_at IS NULL) AND (archived_at IS NULL))
        ->  Subquery Scan on "*SELECT* 7"  (cost=0.06..7.77 rows=3 width=73) (actual time=0.017..0.018 rows=1 loops=1)
              ->  Index Scan using index_countries_on_deleted_at_and_user_id on countries  (cost=0.06..7.76 rows=3 width=65) (actual time=0.017..0.017 rows=1 loops=1)
                    Index Cond: ((deleted_at IS NULL) AND (user_id = 5))
                    Filter: (archived_at IS NULL)
        ->  Subquery Scan on "*SELECT* 8"  (cost=0.06..7.93 rows=3 width=72) (actual time=0.012..0.062 rows=22 loops=1)
              ->  Index Scan using index_creatures_on_deleted_at_and_user_id on creatures  (cost=0.06..7.92 rows=3 width=64) (actual time=0.012..0.059 rows=22 loops=1)
                    Index Cond: ((deleted_at IS NULL) AND (user_id = 5))
                    Filter: (archived_at IS NULL)
                    Rows Removed by Filter: 4
        ->  Subquery Scan on "*SELECT* 9"  (cost=0.06..9.31 rows=4 width=70) (actual time=0.012..0.012 rows=1 loops=1)
              ->  Index Scan using index_deities_on_deleted_at_and_user_id on deities  (cost=0.06..9.30 rows=4 width=62) (actual time=0.011..0.012 rows=1 loops=1)
                    Index Cond: ((deleted_at IS NULL) AND (user_id = 5))
                    Filter: (archived_at IS NULL)
        ->  Subquery Scan on "*SELECT* 10"  (cost=2.06..5.91 rows=2 width=69) (actual time=0.016..0.020 rows=3 loops=1)
              ->  Bitmap Heap Scan on floras  (cost=2.06..5.90 rows=2 width=61) (actual time=0.016..0.019 rows=3 loops=1)
                    Recheck Cond: (user_id = 5)
                    Filter: ((deleted_at IS NULL) AND (archived_at IS NULL))
                    Heap Blocks: exact=3
                    ->  Bitmap Index Scan on index_floras_on_user_id  (cost=0.00..2.06 rows=2 width=0) (actual time=0.013..0.013 rows=3 loops=1)
                          Index Cond: (user_id = 5)
        ->  Bitmap Heap Scan on foods  (cost=2.06..5.58 rows=2 width=94) (actual time=0.006..0.006 rows=0 loops=1)
              Recheck Cond: (user_id = 5)
              Filter: ((deleted_at IS NULL) AND (archived_at IS NULL))
              ->  Bitmap Index Scan on index_foods_on_user_id  (cost=0.00..2.06 rows=2 width=0) (actual time=0.006..0.006 rows=0 loops=1)
                    Index Cond: (user_id = 5)
        ->  Subquery Scan on "*SELECT* 12"  (cost=0.06..5.79 rows=2 width=81) (actual time=0.011..0.012 rows=1 loops=1)
              ->  Index Scan using index_governments_on_deleted_at_and_user_id on governments  (cost=0.06..5.78 rows=2 width=73) (actual time=0.011..0.011 rows=1 loops=1)
                    Index Cond: ((deleted_at IS NULL) AND (user_id = 5))
                    Filter: (archived_at IS NULL)
        ->  Subquery Scan on "*SELECT* 13"  (cost=0.06..9.75 rows=4 width=73) (actual time=0.012..0.034 rows=13 loops=1)
              ->  Index Scan using index_groups_on_deleted_at_and_user_id on groups  (cost=0.06..9.73 rows=4 width=65) (actual time=0.012..0.032 rows=13 loops=1)
                    Index Cond: ((deleted_at IS NULL) AND (user_id = 5))
                    Filter: (archived_at IS NULL)
        ->  Bitmap Heap Scan on jobs  (cost=2.06..5.57 rows=2 width=95) (actual time=0.016..0.017 rows=2 loops=1)
              Recheck Cond: (user_id = 5)
              Filter: ((deleted_at IS NULL) AND (archived_at IS NULL))
              Heap Blocks: exact=2
              ->  Bitmap Index Scan on index_jobs_on_user_id  (cost=0.00..2.06 rows=2 width=0) (actual time=0.011..0.011 rows=2 loops=1)
                    Index Cond: (user_id = 5)
        ->  Subquery Scan on "*SELECT* 15"  (cost=2.06..5.86 rows=2 width=76) (actual time=0.014..0.021 rows=5 loops=1)
              ->  Bitmap Heap Scan on landmarks  (cost=2.06..5.85 rows=2 width=68) (actual time=0.014..0.020 rows=5 loops=1)
                    Recheck Cond: (user_id = 5)
                    Filter: ((deleted_at IS NULL) AND (archived_at IS NULL))
                    Heap Blocks: exact=5
                    ->  Bitmap Index Scan on index_landmarks_on_user_id  (cost=0.00..2.06 rows=2 width=0) (actual time=0.011..0.011 rows=5 loops=1)
                          Index Cond: (user_id = 5)
        ->  Subquery Scan on "*SELECT* 16"  (cost=0.06..4.07 rows=1 width=72) (actual time=0.013..0.014 rows=2 loops=1)
              ->  Index Scan using index_languages_on_deleted_at_and_user_id on languages  (cost=0.06..4.06 rows=1 width=64) (actual time=0.012..0.014 rows=2 loops=1)
                    Index Cond: ((deleted_at IS NULL) AND (user_id = 5))
                    Filter: (archived_at IS NULL)
        ->  Subquery Scan on "*SELECT* 17"  (cost=2.19..111.14 rows=63 width=72) (actual time=0.028..0.143 rows=74 loops=1)
              ->  Bitmap Heap Scan on magics  (cost=2.19..110.89 rows=63 width=64) (actual time=0.028..0.134 rows=74 loops=1)
                    Recheck Cond: ((deleted_at IS NULL) AND (user_id = 5))
                    Filter: (archived_at IS NULL)
                    Heap Blocks: exact=38
                    ->  Bitmap Index Scan on index_magics_on_deleted_at_and_user_id  (cost=0.00..2.18 rows=63 width=0) (actual time=0.022..0.023 rows=74 loops=1)
                          Index Cond: ((deleted_at IS NULL) AND (user_id = 5))
        ->  Subquery Scan on "*SELECT* 18"  (cost=0.06..5.87 rows=2 width=71) (actual time=0.010..0.011 rows=1 loops=1)
              ->  Index Scan using index_planets_on_deleted_at_and_user_id on planets  (cost=0.06..5.87 rows=2 width=63) (actual time=0.010..0.010 rows=1 loops=1)
                    Index Cond: ((deleted_at IS NULL) AND (user_id = 5))
                    Filter: (archived_at IS NULL)
        ->  Subquery Scan on "*SELECT* 19"  (cost=0.08..7.63 rows=3 width=68) (actual time=0.012..0.030 rows=12 loops=1)
              ->  Index Scan using index_races_on_deleted_at_and_user_id on races  (cost=0.08..7.62 rows=3 width=60) (actual time=0.012..0.029 rows=12 loops=1)
                    Index Cond: ((deleted_at IS NULL) AND (user_id = 5))
                    Filter: (archived_at IS NULL)
        ->  Subquery Scan on "*SELECT* 20"  (cost=2.06..5.94 rows=2 width=74) (actual time=0.019..0.027 rows=7 loops=1)
              ->  Bitmap Heap Scan on religions  (cost=2.06..5.93 rows=2 width=66) (actual time=0.018..0.026 rows=7 loops=1)
                    Recheck Cond: (user_id = 5)
                    Filter: ((deleted_at IS NULL) AND (archived_at IS NULL))
                    Heap Blocks: exact=5
                    ->  Bitmap Index Scan on index_religions_on_user_id  (cost=0.00..2.06 rows=2 width=0) (actual time=0.013..0.013 rows=7 loops=1)
                          Index Cond: (user_id = 5)
        ->  Subquery Scan on "*SELECT* 21"  (cost=2.14..73.98 rows=42 width=74) (actual time=0.023..0.079 rows=43 loops=1)
              ->  Bitmap Heap Scan on scenes  (cost=2.14..73.81 rows=42 width=66) (actual time=0.023..0.074 rows=43 loops=1)
                    Recheck Cond: ((deleted_at IS NULL) AND (user_id = 5))
                    Filter: (archived_at IS NULL)
                    Heap Blocks: exact=20
                    ->  Bitmap Index Scan on index_scenes_on_deleted_at_and_user_id  (cost=0.00..2.14 rows=42 width=0) (actual time=0.015..0.015 rows=43 loops=1)
                          Index Cond: ((deleted_at IS NULL) AND (user_id = 5))
        ->  Index Scan using index_schools_on_user_id_and_universe_id_and_deleted_at on schools  (cost=0.06..4.06 rows=1 width=103) (actual time=0.011..0.011 rows=1 loops=1)
              Index Cond: ((user_id = 5) AND (deleted_at IS NULL))
              Filter: (archived_at IS NULL)
        ->  Index Scan using index_sports_on_user_id on sports  (cost=0.03..4.03 rows=1 width=96) (actual time=0.007..0.007 rows=0 loops=1)
              Index Cond: (user_id = 5)
              Filter: ((deleted_at IS NULL) AND (archived_at IS NULL))
        ->  Subquery Scan on "*SELECT* 24"  (cost=0.06..5.75 rows=2 width=77) (actual time=0.013..0.022 rows=3 loops=1)
              ->  Index Scan using index_technologies_on_deleted_at_and_user_id on technologies  (cost=0.06..5.74 rows=2 width=69) (actual time=0.012..0.021 rows=3 loops=1)
                    Index Cond: ((deleted_at IS NULL) AND (user_id = 5))
                    Filter: (archived_at IS NULL)
        ->  Subquery Scan on "*SELECT* 25"  (cost=2.06..7.83 rows=3 width=69) (actual time=0.026..0.034 rows=4 loops=1)
              ->  Bitmap Heap Scan on towns  (cost=2.06..7.81 rows=3 width=61) (actual time=0.026..0.033 rows=4 loops=1)
                    Recheck Cond: ((deleted_at IS NULL) AND (user_id = 5))
                    Filter: (archived_at IS NULL)
                    Heap Blocks: exact=3
                    ->  Bitmap Index Scan on index_towns_on_deleted_at_and_user_id  (cost=0.00..2.06 rows=3 width=0) (actual time=0.013..0.013 rows=4 loops=1)
                          Index Cond: ((deleted_at IS NULL) AND (user_id = 5))
        ->  Bitmap Heap Scan on traditions  (cost=2.06..5.52 rows=2 width=80) (actual time=0.004..0.005 rows=0 loops=1)
              Recheck Cond: (user_id = 5)
              Filter: ((deleted_at IS NULL) AND (archived_at IS NULL))
              ->  Bitmap Index Scan on index_traditions_on_user_id  (cost=0.00..2.06 rows=2 width=0) (actual time=0.004..0.004 rows=0 loops=1)
                    Index Cond: (user_id = 5)
        ->  Index Scan using index_vehicles_on_user_id_and_universe_id_and_deleted_at on vehicles  (cost=0.06..4.06 rows=1 width=76) (actual time=0.014..0.014 rows=0 loops=1)
              Index Cond: ((user_id = 5) AND (deleted_at IS NULL))
              Filter: (archived_at IS NULL)
Planning time: 2.222 ms
Execution time: 2.222 ms




Times for the first time I ran this (in case the above sped up from caching):
Planning time: 10.141 ms
Execution time: 13.549 ms