Changes between Version 5 and Version 6 of Indexes


Ignore:
Timestamp:
02/06/26 16:28:31 (2 days ago)
Author:
233194
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • Indexes

    v5 v6  
    434434Without indexes: \\
    435435{{{
    436 "Limit  (cost=13848.06..13848.08 rows=10 width=135) (actual time=338.505..338.510 rows=10.00 loops=1)"
     436"Limit  (cost=13848.06..13848.08 rows=10 width=135) (actual time=314.741..314.745 rows=10.00 loops=1)"
    437437"  Output: u.username, u.email, (count(b.id)), (COALESCE(sum((count(DISTINCT fb.user_id))), '0'::numeric)), (round(COALESCE((avg((avg(rb.value)))), '0'::numeric), 2)), ((((((count(b.id)) * 10))::numeric + ((COALESCE(sum((count(DISTINCT fb.user_id))), '0'::numeric)) * '5'::numeric)) + (COALESCE((avg((avg(rb.value)))), '0'::numeric) * '20'::numeric)))"
    438438"  Buffers: shared hit=142841, temp read=231 written=584"
    439 "  ->  Sort  (cost=13848.06..13848.56 rows=200 width=135) (actual time=338.504..338.508 rows=10.00 loops=1)"
     439"  ->  Sort  (cost=13848.06..13848.56 rows=200 width=135) (actual time=314.740..314.743 rows=10.00 loops=1)"
    440440"        Output: u.username, u.email, (count(b.id)), (COALESCE(sum((count(DISTINCT fb.user_id))), '0'::numeric)), (round(COALESCE((avg((avg(rb.value)))), '0'::numeric), 2)), ((((((count(b.id)) * 10))::numeric + ((COALESCE(sum((count(DISTINCT fb.user_id))), '0'::numeric)) * '5'::numeric)) + (COALESCE((avg((avg(rb.value)))), '0'::numeric) * '20'::numeric)))"
    441441"        Sort Key: ((((((count(b.id)) * 10))::numeric + ((COALESCE(sum((count(DISTINCT fb.user_id))), '0'::numeric)) * '5'::numeric)) + (COALESCE((avg((avg(rb.value)))), '0'::numeric) * '20'::numeric))) DESC"
    442442"        Sort Method: top-N heapsort  Memory: 26kB"
    443443"        Buffers: shared hit=142841, temp read=231 written=584"
    444 "        ->  Nested Loop  (cost=12461.53..13843.74 rows=200 width=135) (actual time=142.982..324.335 rows=47142.00 loops=1)"
     444"        ->  Nested Loop  (cost=12461.53..13843.74 rows=200 width=135) (actual time=133.050..301.004 rows=47142.00 loops=1)"
    445445"              Output: u.username, u.email, (count(b.id)), (COALESCE(sum((count(DISTINCT fb.user_id))), '0'::numeric)), round(COALESCE((avg((avg(rb.value)))), '0'::numeric), 2), (((((count(b.id)) * 10))::numeric + ((COALESCE(sum((count(DISTINCT fb.user_id))), '0'::numeric)) * '5'::numeric)) + (COALESCE((avg((avg(rb.value)))), '0'::numeric) * '20'::numeric))"
    446446"              Inner Unique: true"
    447447"              Buffers: shared hit=142841, temp read=231 written=584"
    448 "              ->  HashAggregate  (cost=12461.24..12464.24 rows=200 width=76) (actual time=142.958..207.997 rows=47142.00 loops=1)"
     448"              ->  HashAggregate  (cost=12461.24..12464.24 rows=200 width=76) (actual time=133.026..193.252 rows=47142.00 loops=1)"
    449449"                    Output: b.user_id, count(b.id), COALESCE(sum((count(DISTINCT fb.user_id))), '0'::numeric), avg((avg(rb.value)))"
    450450"                    Group Key: b.user_id"
    451451"                    Batches: 5  Memory Usage: 8257kB  Disk Usage: 3344kB"
    452452"                    Buffers: shared hit=1415, temp read=231 written=584"
    453 "                    ->  GroupAggregate  (cost=0.84..11187.64 rows=63680 width=48) (actual time=0.061..113.081 rows=63679.00 loops=1)"
     453"                    ->  GroupAggregate  (cost=0.84..11187.64 rows=63680 width=48) (actual time=0.068..104.662 rows=63679.00 loops=1)"
    454454"                          Output: b.id, b.user_id, count(DISTINCT fb.user_id), avg(rb.value)"
    455455"                          Group Key: b.id"
    456456"                          Buffers: shared hit=1415"
    457 "                          ->  Incremental Sort  (cost=0.84..9824.23 rows=75655 width=17) (actual time=0.055..65.316 rows=94843.00 loops=1)"
     457"                          ->  Incremental Sort  (cost=0.84..9824.23 rows=75655 width=17) (actual time=0.060..59.549 rows=94843.00 loops=1)"
    458458"                                Output: b.id, b.user_id, fb.user_id, rb.value"
    459459"                                Sort Key: b.id, fb.user_id"
    … …  
    461461"                                Full-sort Groups: 2934  Sort Method: quicksort  Average Memory: 25kB  Peak Memory: 25kB"
    462462"                                Buffers: shared hit=1415"
    463 "                                ->  Merge Left Join  (cost=0.72..6838.88 rows=75655 width=17) (actual time=0.036..48.651 rows=94843.00 loops=1)"
     463"                                ->  Merge Left Join  (cost=0.72..6838.88 rows=75655 width=17) (actual time=0.038..44.233 rows=94843.00 loops=1)"
    464464"                                      Output: b.id, b.user_id, fb.user_id, rb.value"
    465465"                                      Merge Cond: (b.id = rb.build_id)"
    466466"                                      Buffers: shared hit=1415"
    467 "                                      ->  Merge Left Join  (cost=0.30..2979.52 rows=63680 width=12) (actual time=0.023..21.860 rows=63679.00 loops=1)"
     467"                                      ->  Merge Left Join  (cost=0.30..2979.52 rows=63680 width=12) (actual time=0.026..20.287 rows=63679.00 loops=1)"
    468468"                                            Output: b.id, b.user_id, fb.user_id"
    469469"                                            Merge Cond: (b.id = fb.build_id)"
    470470"                                            Buffers: shared hit=1070"
    471 "                                            ->  Index Scan using build_pkey on public.build b  (cost=0.29..2820.29 rows=63680 width=8) (actual time=0.009..16.139 rows=63679.00 loops=1)"
     471"                                            ->  Index Scan using build_pkey on public.build b  (cost=0.29..2820.29 rows=63680 width=8) (actual time=0.010..15.036 rows=63679.00 loops=1)"
    472472"                                                  Output: b.id, b.user_id, b.name, b.created_at, b.description, b.total_price, b.is_approved"
    473473"                                                  Filter: b.is_approved"
    … …  
    475475"                                                  Index Searches: 1"
    476476"                                                  Buffers: shared hit=1070"
    477 "                                            ->  Sort  (cost=0.01..0.02 rows=1 width=8) (actual time=0.012..0.013 rows=0.00 loops=1)"
     477"                                            ->  Sort  (cost=0.01..0.02 rows=1 width=8) (actual time=0.015..0.015 rows=0.00 loops=1)"
    478478"                                                  Output: fb.user_id, fb.build_id"
    479479"                                                  Sort Key: fb.build_id"
    480480"                                                  Sort Method: quicksort  Memory: 25kB"
    481 "                                                  ->  Seq Scan on public.favorite_build fb  (cost=0.00..0.00 rows=1 width=8) (actual time=0.008..0.009 rows=0.00 loops=1)"
     481"                                                  ->  Seq Scan on public.favorite_build fb  (cost=0.00..0.00 rows=1 width=8) (actual time=0.009..0.010 rows=0.00 loops=1)"
    482482"                                                        Output: fb.user_id, fb.build_id"
    483 "                                      ->  Index Only Scan using idx_rating_build_build_value on public.rating_build rb  (cost=0.42..2720.98 rows=89104 width=9) (actual time=0.012..8.719 rows=89104.00 loops=1)"
     483"                                      ->  Index Only Scan using idx_rating_build_build_value on public.rating_build rb  (cost=0.42..2720.98 rows=89104 width=9) (actual time=0.010..8.113 rows=89104.00 loops=1)"
    484484"                                            Output: rb.build_id, rb.value"
    485485"                                            Heap Fetches: 0"
    … …  
    492492"                    Buffers: shared hit=141426"
    493493"Planning:"
    494 "  Buffers: shared hit=53 read=3 dirtied=3"
    495 "Planning Time: 1.559 ms"
    496 "Execution Time: 350.055 ms"
     494"  Buffers: shared hit=37 dirtied=1"
     495"Planning Time: 1.422 ms"
     496"Execution Time: 325.769 ms"
    497497}}}
    498498
    499499With indexes: \\
    500500{{{
    501 "Limit  (cost=13848.06..13848.08 rows=10 width=135) (actual time=310.840..310.844 rows=10.00 loops=1)"
     501"Limit  (cost=13848.06..13848.08 rows=10 width=135) (actual time=293.344..293.349 rows=10.00 loops=1)"
    502502"  Output: u.username, u.email, (count(b.id)), (COALESCE(sum((count(DISTINCT fb.user_id))), '0'::numeric)), (round(COALESCE((avg((avg(rb.value)))), '0'::numeric), 2)), ((((((count(b.id)) * 10))::numeric + ((COALESCE(sum((count(DISTINCT fb.user_id))), '0'::numeric)) * '5'::numeric)) + (COALESCE((avg((avg(rb.value)))), '0'::numeric) * '20'::numeric)))"
    503503"  Buffers: shared hit=142841, temp read=231 written=584"
    504 "  ->  Sort  (cost=13848.06..13848.56 rows=200 width=135) (actual time=310.839..310.842 rows=10.00 loops=1)"
     504"  ->  Sort  (cost=13848.06..13848.56 rows=200 width=135) (actual time=293.343..293.347 rows=10.00 loops=1)"
    505505"        Output: u.username, u.email, (count(b.id)), (COALESCE(sum((count(DISTINCT fb.user_id))), '0'::numeric)), (round(COALESCE((avg((avg(rb.value)))), '0'::numeric), 2)), ((((((count(b.id)) * 10))::numeric + ((COALESCE(sum((count(DISTINCT fb.user_id))), '0'::numeric)) * '5'::numeric)) + (COALESCE((avg((avg(rb.value)))), '0'::numeric) * '20'::numeric)))"
    506506"        Sort Key: ((((((count(b.id)) * 10))::numeric + ((COALESCE(sum((count(DISTINCT fb.user_id))), '0'::numeric)) * '5'::numeric)) + (COALESCE((avg((avg(rb.value)))), '0'::numeric) * '20'::numeric))) DESC"
    507507"        Sort Method: top-N heapsort  Memory: 26kB"
    508508"        Buffers: shared hit=142841, temp read=231 written=584"
    509 "        ->  Nested Loop  (cost=12461.53..13843.74 rows=200 width=135) (actual time=127.073..297.426 rows=47142.00 loops=1)"
     509"        ->  Nested Loop  (cost=12461.53..13843.74 rows=200 width=135) (actual time=125.883..280.717 rows=47142.00 loops=1)"
    510510"              Output: u.username, u.email, (count(b.id)), (COALESCE(sum((count(DISTINCT fb.user_id))), '0'::numeric)), round(COALESCE((avg((avg(rb.value)))), '0'::numeric), 2), (((((count(b.id)) * 10))::numeric + ((COALESCE(sum((count(DISTINCT fb.user_id))), '0'::numeric)) * '5'::numeric)) + (COALESCE((avg((avg(rb.value)))), '0'::numeric) * '20'::numeric))"
    511511"              Inner Unique: true"
    512512"              Buffers: shared hit=142841, temp read=231 written=584"
    513 "              ->  HashAggregate  (cost=12461.24..12464.24 rows=200 width=76) (actual time=127.053..185.307 rows=47142.00 loops=1)"
     513"              ->  HashAggregate  (cost=12461.24..12464.24 rows=200 width=76) (actual time=125.860..181.260 rows=47142.00 loops=1)"
    514514"                    Output: b.user_id, count(b.id), COALESCE(sum((count(DISTINCT fb.user_id))), '0'::numeric), avg((avg(rb.value)))"
    515515"                    Group Key: b.user_id"
    516516"                    Batches: 5  Memory Usage: 8257kB  Disk Usage: 3344kB"
    517517"                    Buffers: shared hit=1415, temp read=231 written=584"
    518 "                    ->  GroupAggregate  (cost=0.84..11187.64 rows=63680 width=48) (actual time=0.083..99.800 rows=63679.00 loops=1)"
     518"                    ->  GroupAggregate  (cost=0.84..11187.64 rows=63680 width=48) (actual time=0.060..98.939 rows=63679.00 loops=1)"
    519519"                          Output: b.id, b.user_id, count(DISTINCT fb.user_id), avg(rb.value)"
    520520"                          Group Key: b.id"
    521521"                          Buffers: shared hit=1415"
    522 "                          ->  Incremental Sort  (cost=0.84..9824.23 rows=75655 width=17) (actual time=0.078..55.977 rows=94843.00 loops=1)"
     522"                          ->  Incremental Sort  (cost=0.84..9824.23 rows=75655 width=17) (actual time=0.055..56.113 rows=94843.00 loops=1)"
    523523"                                Output: b.id, b.user_id, fb.user_id, rb.value"
    524524"                                Sort Key: b.id, fb.user_id"
    … …  
    526526"                                Full-sort Groups: 2934  Sort Method: quicksort  Average Memory: 25kB  Peak Memory: 25kB"
    527527"                                Buffers: shared hit=1415"
    528 "                                ->  Merge Left Join  (cost=0.72..6838.88 rows=75655 width=17) (actual time=0.035..41.138 rows=94843.00 loops=1)"
     528"                                ->  Merge Left Join  (cost=0.72..6838.88 rows=75655 width=17) (actual time=0.032..41.184 rows=94843.00 loops=1)"
    529529"                                      Output: b.id, b.user_id, fb.user_id, rb.value"
    530530"                                      Merge Cond: (b.id = rb.build_id)"
    531531"                                      Buffers: shared hit=1415"
    532 "                                      ->  Merge Left Join  (cost=0.30..2979.52 rows=63680 width=12) (actual time=0.023..18.353 rows=63679.00 loops=1)"
     532"                                      ->  Merge Left Join  (cost=0.30..2979.52 rows=63680 width=12) (actual time=0.022..18.581 rows=63679.00 loops=1)"
    533533"                                            Output: b.id, b.user_id, fb.user_id"
    534534"                                            Merge Cond: (b.id = fb.build_id)"
    535535"                                            Buffers: shared hit=1070"
    536 "                                            ->  Index Scan using build_pkey on public.build b  (cost=0.29..2820.29 rows=63680 width=8) (actual time=0.010..13.634 rows=63679.00 loops=1)"
     536"                                            ->  Index Scan using build_pkey on public.build b  (cost=0.29..2820.29 rows=63680 width=8) (actual time=0.009..13.763 rows=63679.00 loops=1)"
    537537"                                                  Output: b.id, b.user_id, b.name, b.created_at, b.description, b.total_price, b.is_approved"
    538538"                                                  Filter: b.is_approved"
    … …  
    540540"                                                  Index Searches: 1"
    541541"                                                  Buffers: shared hit=1070"
    542 "                                            ->  Sort  (cost=0.01..0.02 rows=1 width=8) (actual time=0.012..0.012 rows=0.00 loops=1)"
     542"                                            ->  Sort  (cost=0.01..0.02 rows=1 width=8) (actual time=0.011..0.011 rows=0.00 loops=1)"
    543543"                                                  Output: fb.user_id, fb.build_id"
    544544"                                                  Sort Key: fb.build_id"
    545545"                                                  Sort Method: quicksort  Memory: 25kB"
    546 "                                                  ->  Seq Scan on public.favorite_build fb  (cost=0.00..0.00 rows=1 width=8) (actual time=0.008..0.008 rows=0.00 loops=1)"
     546"                                                  ->  Seq Scan on public.favorite_build fb  (cost=0.00..0.00 rows=1 width=8) (actual time=0.007..0.007 rows=0.00 loops=1)"
    547547"                                                        Output: fb.user_id, fb.build_id"
    548 "                                      ->  Index Only Scan using idx_rating_build_build_value on public.rating_build rb  (cost=0.42..2720.98 rows=89104 width=9) (actual time=0.010..7.703 rows=89104.00 loops=1)"
     548"                                      ->  Index Only Scan using idx_rating_build_build_value on public.rating_build rb  (cost=0.42..2720.98 rows=89104 width=9) (actual time=0.009..7.694 rows=89104.00 loops=1)"
    549549"                                            Output: rb.build_id, rb.value"
    550550"                                            Heap Fetches: 0"
    … …  
    557557"                    Buffers: shared hit=141426"
    558558"Planning:"
    559 "  Buffers: shared hit=20"
    560 "Planning Time: 0.552 ms"
    561 "Execution Time: 321.427 ms"
    562 }}}
    563 
    564 Result: Here the improvement is modest but still, the time is reduced from 350.055ms to 321.427ms. \\
     559"  Buffers: shared hit=60 read=2"
     560"Planning Time: 2.444 ms"
     561"Execution Time: 304.156 ms"
     562}}}
     563
     564Result: Here the improvement is modest but still, the time is reduced from 325.769ms to 304.156ms. The difference isn't that big because both queries use the same access path. \\
    565565
    566566=== Scenario 9: Budget Tier Popularity Report ===
    … …  
    568568Without indexes: \\
    569569{{{
    570 "Limit  (cost=10028.88..10028.91 rows=15 width=144) (actual time=129.793..129.800 rows=3.00 loops=1)"
     570"Limit  (cost=10349.54..10349.58 rows=15 width=144) (actual time=129.156..129.161 rows=3.00 loops=1)"
    571571"  Output: engagement_stats.price_tier, engagement_stats.builds_count, (round(engagement_stats.avg_favorites, 1)), (round(COALESCE(engagement_stats.avg_rating, '0'::numeric), 2)), engagement_stats.unique_builders, (round((((((engagement_stats.builds_count * 2))::numeric + (engagement_stats.avg_favorites * '3'::numeric)) + (COALESCE(engagement_stats.avg_rating, '0'::numeric) * '10'::numeric)) + ((engagement_stats.unique_builders)::numeric * 1.5)), 2))"
    572 "  Buffers: shared hit=1346 read=28"
    573 "  ->  Sort  (cost=10028.88..10105.53 rows=30660 width=144) (actual time=129.790..129.795 rows=3.00 loops=1)"
     572"  Buffers: shared hit=1346"
     573"  ->  Sort  (cost=10349.54..10426.19 rows=30660 width=144) (actual time=129.155..129.159 rows=3.00 loops=1)"
    574574"        Output: engagement_stats.price_tier, engagement_stats.builds_count, (round(engagement_stats.avg_favorites, 1)), (round(COALESCE(engagement_stats.avg_rating, '0'::numeric), 2)), engagement_stats.unique_builders, (round((((((engagement_stats.builds_count * 2))::numeric + (engagement_stats.avg_favorites * '3'::numeric)) + (COALESCE(engagement_stats.avg_rating, '0'::numeric) * '10'::numeric)) + ((engagement_stats.unique_builders)::numeric * 1.5)), 2))"
    575575"        Sort Key: (round((((((engagement_stats.builds_count * 2))::numeric + (engagement_stats.avg_favorites * '3'::numeric)) + (COALESCE(engagement_stats.avg_rating, '0'::numeric) * '10'::numeric)) + ((engagement_stats.unique_builders)::numeric * 1.5)), 2)) DESC"
    576576"        Sort Method: quicksort  Memory: 25kB"
    577 "        Buffers: shared hit=1346 read=28"
    578 "        ->  Subquery Scan on engagement_stats  (cost=7102.18..9276.65 rows=30660 width=144) (actual time=122.973..129.780 rows=3.00 loops=1)"
     577"        Buffers: shared hit=1346"
     578"        ->  Subquery Scan on engagement_stats  (cost=7422.84..9597.32 rows=30660 width=144) (actual time=120.760..129.147 rows=3.00 loops=1)"
    579579"              Output: engagement_stats.price_tier, engagement_stats.builds_count, round(engagement_stats.avg_favorites, 1), round(COALESCE(engagement_stats.avg_rating, '0'::numeric), 2), engagement_stats.unique_builders, round((((((engagement_stats.builds_count * 2))::numeric + (engagement_stats.avg_favorites * '3'::numeric)) + (COALESCE(engagement_stats.avg_rating, '0'::numeric) * '10'::numeric)) + ((engagement_stats.unique_builders)::numeric * 1.5)), 2)"
    580 "              Buffers: shared hit=1346 read=28"
    581 "              ->  GroupAggregate  (cost=7102.18..8356.85 rows=30660 width=112) (actual time=122.969..129.762 rows=3.00 loops=1)"
     580"              Buffers: shared hit=1346"
     581"              ->  GroupAggregate  (cost=7422.84..8677.52 rows=30660 width=112) (actual time=120.745..129.125 rows=3.00 loops=1)"
    582582"                    Output: (CASE WHEN (b.total_price < '500'::numeric) THEN 'Budget'::text WHEN (b.total_price < '1000'::numeric) THEN 'Mid-Range'::text WHEN (b.total_price < '2000'::numeric) THEN 'High-End'::text ELSE 'Enthusiast'::text END), count(DISTINCT b.id), avg(COALESCE(f.favorites_count, '0'::bigint)), avg(rb.value), count(DISTINCT b.user_id)"
    583583"                    Group Key: (CASE WHEN (b.total_price < '500'::numeric) THEN 'Budget'::text WHEN (b.total_price < '1000'::numeric) THEN 'Mid-Range'::text WHEN (b.total_price < '2000'::numeric) THEN 'High-End'::text ELSE 'Enthusiast'::text END)"
    584 "                    Buffers: shared hit=1346 read=28"
    585 "                    ->  Sort  (cost=7102.18..7196.31 rows=37655 width=53) (actual time=108.154..112.232 rows=47343.00 loops=1)"
     584"                    Buffers: shared hit=1346"
     585"                    ->  Sort  (cost=7422.84..7516.98 rows=37655 width=53) (actual time=107.389..110.953 rows=47343.00 loops=1)"
    586586"                          Output: (CASE WHEN (b.total_price < '500'::numeric) THEN 'Budget'::text WHEN (b.total_price < '1000'::numeric) THEN 'Mid-Range'::text WHEN (b.total_price < '2000'::numeric) THEN 'High-End'::text ELSE 'Enthusiast'::text END), b.id, f.favorites_count, rb.value, b.user_id"
    587587"                          Sort Key: (CASE WHEN (b.total_price < '500'::numeric) THEN 'Budget'::text WHEN (b.total_price < '1000'::numeric) THEN 'Mid-Range'::text WHEN (b.total_price < '2000'::numeric) THEN 'High-End'::text ELSE 'Enthusiast'::text END), b.id"
    588588"                          Sort Method: quicksort  Memory: 3562kB"
    589 "                          Buffers: shared hit=1346 read=28"
    590 "                          ->  Hash Left Join  (cost=2252.07..4240.29 rows=37655 width=53) (actual time=9.040..47.523 rows=47343.00 loops=1)"
     589"                          Buffers: shared hit=1346"
     590"                          ->  Hash Left Join  (cost=2572.74..4560.96 rows=37655 width=53) (actual time=16.527..54.066 rows=47343.00 loops=1)"
    591591"                                Output: CASE WHEN (b.total_price < '500'::numeric) THEN 'Budget'::text WHEN (b.total_price < '1000'::numeric) THEN 'Mid-Range'::text WHEN (b.total_price < '2000'::numeric) THEN 'High-End'::text ELSE 'Enthusiast'::text END, b.id, f.favorites_count, rb.value, b.user_id"
    592592"                                Inner Unique: true"
    593593"                                Hash Cond: (b.id = f.build_id)"
    594 "                                Buffers: shared hit=1346 read=28"
    595 "                                ->  Hash Right Join  (cost=2252.02..3858.97 rows=37655 width=19) (actual time=9.018..36.039 rows=47343.00 loops=1)"
     594"                                Buffers: shared hit=1346"
     595"                                ->  Hash Right Join  (cost=2572.69..4179.64 rows=37655 width=19) (actual time=16.499..43.795 rows=47343.00 loops=1)"
    596596"                                      Output: b.total_price, b.id, b.user_id, rb.value"
    597597"                                      Inner Unique: true"
    598598"                                      Hash Cond: (rb.build_id = b.id)"
    599 "                                      Buffers: shared hit=1346 read=28"
    600 "                                      ->  Seq Scan on public.rating_build rb  (cost=0.00..1373.04 rows=89104 width=9) (actual time=0.018..4.017 rows=89104.00 loops=1)"
     599"                                      Buffers: shared hit=1346"
     600"                                      ->  Seq Scan on public.rating_build rb  (cost=0.00..1373.04 rows=89104 width=9) (actual time=0.019..3.738 rows=89104.00 loops=1)"
    601601"                                            Output: rb.build_id, rb.user_id, rb.value"
    602602"                                            Buffers: shared hit=482"
    603 "                                      ->  Hash  (cost=1855.83..1855.83 rows=31695 width=14) (actual time=8.927..8.928 rows=31774.00 loops=1)"
     603"                                      ->  Hash  (cost=2176.50..2176.50 rows=31695 width=14) (actual time=16.413..16.414 rows=31774.00 loops=1)"
    604604"                                            Output: b.total_price, b.id, b.user_id"
    605605"                                            Buckets: 32768  Batches: 1  Memory Usage: 1746kB"
    606 "                                            Buffers: shared hit=864 read=28"
    607 "                                            ->  Bitmap Heap Scan on public.build b  (cost=437.17..1855.83 rows=31695 width=14) (actual time=1.272..5.897 rows=31774.00 loops=1)"
     606"                                            Buffers: shared hit=864"
     607"                                            ->  Seq Scan on public.build b  (cost=0.00..2176.50 rows=31695 width=14) (actual time=0.012..12.589 rows=31774.00 loops=1)"
    608608"                                                  Output: b.total_price, b.id, b.user_id"
    609 "                                                  Recheck Cond: (b.is_approved AND (b.created_at >= (CURRENT_DATE - '6 mons'::interval)))"
    610 "                                                  Heap Blocks: exact=864"
    611 "                                                  Buffers: shared hit=864 read=28"
    612 "                                                  ->  Bitmap Index Scan on idx_build_approved_date  (cost=0.00..429.25 rows=31695 width=0) (actual time=1.198..1.198 rows=31774.00 loops=1)"
    613 "                                                        Index Cond: ((b.is_approved = true) AND (b.created_at >= (CURRENT_DATE - '6 mons'::interval)))"
    614 "                                                        Index Searches: 1"
    615 "                                                        Buffers: shared read=28"
    616 "                                ->  Hash  (cost=0.04..0.04 rows=1 width=12) (actual time=0.015..0.017 rows=0.00 loops=1)"
     609"                                                  Filter: (b.is_approved AND (b.created_at >= (CURRENT_DATE - '6 mons'::interval)))"
     610"                                                  Rows Removed by Filter: 43226"
     611"                                                  Buffers: shared hit=864"
     612"                                ->  Hash  (cost=0.04..0.04 rows=1 width=12) (actual time=0.017..0.019 rows=0.00 loops=1)"
    617613"                                      Output: f.favorites_count, f.build_id"
    618614"                                      Buckets: 1024  Batches: 1  Memory Usage: 8kB"
    619 "                                      ->  Subquery Scan on f  (cost=0.01..0.04 rows=1 width=12) (actual time=0.015..0.015 rows=0.00 loops=1)"
     615"                                      ->  Subquery Scan on f  (cost=0.01..0.04 rows=1 width=12) (actual time=0.017..0.018 rows=0.00 loops=1)"
    620616"                                            Output: f.favorites_count, f.build_id"
    621 "                                            ->  GroupAggregate  (cost=0.01..0.03 rows=1 width=12) (actual time=0.014..0.015 rows=0.00 loops=1)"
     617"                                            ->  GroupAggregate  (cost=0.01..0.03 rows=1 width=12) (actual time=0.016..0.017 rows=0.00 loops=1)"
    622618"                                                  Output: favorite_build.build_id, count(DISTINCT favorite_build.user_id)"
    623619"                                                  Group Key: favorite_build.build_id"
    624 "                                                  ->  Sort  (cost=0.01..0.02 rows=1 width=8) (actual time=0.014..0.014 rows=0.00 loops=1)"
     620"                                                  ->  Sort  (cost=0.01..0.02 rows=1 width=8) (actual time=0.015..0.016 rows=0.00 loops=1)"
    625621"                                                        Output: favorite_build.build_id, favorite_build.user_id"
    626622"                                                        Sort Key: favorite_build.build_id, favorite_build.user_id"
    627623"                                                        Sort Method: quicksort  Memory: 25kB"
    628 "                                                        ->  Seq Scan on public.favorite_build  (cost=0.00..0.00 rows=1 width=8) (actual time=0.008..0.008 rows=0.00 loops=1)"
     624"                                                        ->  Seq Scan on public.favorite_build  (cost=0.00..0.00 rows=1 width=8) (actual time=0.008..0.009 rows=0.00 loops=1)"
    629625"                                                              Output: favorite_build.build_id, favorite_build.user_id"
    630626"Planning:"
    631 "  Buffers: shared hit=29 dirtied=1"
    632 "Planning Time: 1.567 ms"
    633 "Execution Time: 130.489 ms"
     627"  Buffers: shared hit=26 dirtied=1"
     628"Planning Time: 1.348 ms"
     629"Execution Time: 129.409 ms"
    634630}}}
    635631
    636632With indexes: \\
    637633{{{
    638 "Limit  (cost=10028.88..10028.91 rows=15 width=144) (actual time=117.098..117.102 rows=3.00 loops=1)"
     634"Limit  (cost=10242.59..10242.63 rows=15 width=144) (actual time=125.400..125.404 rows=3.00 loops=1)"
    639635"  Output: engagement_stats.price_tier, engagement_stats.builds_count, (round(engagement_stats.avg_favorites, 1)), (round(COALESCE(engagement_stats.avg_rating, '0'::numeric), 2)), engagement_stats.unique_builders, (round((((((engagement_stats.builds_count * 2))::numeric + (engagement_stats.avg_favorites * '3'::numeric)) + (COALESCE(engagement_stats.avg_rating, '0'::numeric) * '10'::numeric)) + ((engagement_stats.unique_builders)::numeric * 1.5)), 2))"
    640 "  Buffers: shared hit=1374"
    641 "  ->  Sort  (cost=10028.88..10105.53 rows=30660 width=144) (actual time=117.097..117.101 rows=3.00 loops=1)"
     636"  Buffers: shared hit=1348 read=125"
     637"  ->  Sort  (cost=10242.59..10319.24 rows=30660 width=144) (actual time=125.399..125.403 rows=3.00 loops=1)"
    642638"        Output: engagement_stats.price_tier, engagement_stats.builds_count, (round(engagement_stats.avg_favorites, 1)), (round(COALESCE(engagement_stats.avg_rating, '0'::numeric), 2)), engagement_stats.unique_builders, (round((((((engagement_stats.builds_count * 2))::numeric + (engagement_stats.avg_favorites * '3'::numeric)) + (COALESCE(engagement_stats.avg_rating, '0'::numeric) * '10'::numeric)) + ((engagement_stats.unique_builders)::numeric * 1.5)), 2))"
    643639"        Sort Key: (round((((((engagement_stats.builds_count * 2))::numeric + (engagement_stats.avg_favorites * '3'::numeric)) + (COALESCE(engagement_stats.avg_rating, '0'::numeric) * '10'::numeric)) + ((engagement_stats.unique_builders)::numeric * 1.5)), 2)) DESC"
    644640"        Sort Method: quicksort  Memory: 25kB"
    645 "        Buffers: shared hit=1374"
    646 "        ->  Subquery Scan on engagement_stats  (cost=7102.18..9276.65 rows=30660 width=144) (actual time=110.076..117.088 rows=3.00 loops=1)"
     641"        Buffers: shared hit=1348 read=125"
     642"        ->  Subquery Scan on engagement_stats  (cost=7315.89..9490.37 rows=30660 width=144) (actual time=118.820..125.394 rows=3.00 loops=1)"
    647643"              Output: engagement_stats.price_tier, engagement_stats.builds_count, round(engagement_stats.avg_favorites, 1), round(COALESCE(engagement_stats.avg_rating, '0'::numeric), 2), engagement_stats.unique_builders, round((((((engagement_stats.builds_count * 2))::numeric + (engagement_stats.avg_favorites * '3'::numeric)) + (COALESCE(engagement_stats.avg_rating, '0'::numeric) * '10'::numeric)) + ((engagement_stats.unique_builders)::numeric * 1.5)), 2)"
    648 "              Buffers: shared hit=1374"
    649 "              ->  GroupAggregate  (cost=7102.18..8356.85 rows=30660 width=112) (actual time=110.071..117.076 rows=3.00 loops=1)"
     644"              Buffers: shared hit=1348 read=125"
     645"              ->  GroupAggregate  (cost=7315.89..8570.57 rows=30660 width=112) (actual time=118.816..125.384 rows=3.00 loops=1)"
    650646"                    Output: (CASE WHEN (b.total_price < '500'::numeric) THEN 'Budget'::text WHEN (b.total_price < '1000'::numeric) THEN 'Mid-Range'::text WHEN (b.total_price < '2000'::numeric) THEN 'High-End'::text ELSE 'Enthusiast'::text END), count(DISTINCT b.id), avg(COALESCE(f.favorites_count, '0'::bigint)), avg(rb.value), count(DISTINCT b.user_id)"
    651647"                    Group Key: (CASE WHEN (b.total_price < '500'::numeric) THEN 'Budget'::text WHEN (b.total_price < '1000'::numeric) THEN 'Mid-Range'::text WHEN (b.total_price < '2000'::numeric) THEN 'High-End'::text ELSE 'Enthusiast'::text END)"
    652 "                    Buffers: shared hit=1374"
    653 "                    ->  Sort  (cost=7102.18..7196.31 rows=37655 width=53) (actual time=95.917..99.817 rows=47343.00 loops=1)"
     648"                    Buffers: shared hit=1348 read=125"
     649"                    ->  Sort  (cost=7315.89..7410.03 rows=37655 width=53) (actual time=106.073..109.284 rows=47343.00 loops=1)"
    654650"                          Output: (CASE WHEN (b.total_price < '500'::numeric) THEN 'Budget'::text WHEN (b.total_price < '1000'::numeric) THEN 'Mid-Range'::text WHEN (b.total_price < '2000'::numeric) THEN 'High-End'::text ELSE 'Enthusiast'::text END), b.id, f.favorites_count, rb.value, b.user_id"
    655651"                          Sort Key: (CASE WHEN (b.total_price < '500'::numeric) THEN 'Budget'::text WHEN (b.total_price < '1000'::numeric) THEN 'Mid-Range'::text WHEN (b.total_price < '2000'::numeric) THEN 'High-End'::text ELSE 'Enthusiast'::text END), b.id"
    656652"                          Sort Method: quicksort  Memory: 3562kB"
    657 "                          Buffers: shared hit=1374"
    658 "                          ->  Hash Left Join  (cost=2252.07..4240.29 rows=37655 width=53) (actual time=9.370..39.857 rows=47343.00 loops=1)"
     653"                          Buffers: shared hit=1348 read=125"
     654"                          ->  Hash Left Join  (cost=2465.79..4454.01 rows=37655 width=53) (actual time=9.387..44.093 rows=47343.00 loops=1)"
    659655"                                Output: CASE WHEN (b.total_price < '500'::numeric) THEN 'Budget'::text WHEN (b.total_price < '1000'::numeric) THEN 'Mid-Range'::text WHEN (b.total_price < '2000'::numeric) THEN 'High-End'::text ELSE 'Enthusiast'::text END, b.id, f.favorites_count, rb.value, b.user_id"
    660656"                                Inner Unique: true"
    661657"                                Hash Cond: (b.id = f.build_id)"
    662 "                                Buffers: shared hit=1374"
    663 "                                ->  Hash Right Join  (cost=2252.02..3858.97 rows=37655 width=19) (actual time=9.347..30.148 rows=47343.00 loops=1)"
     658"                                Buffers: shared hit=1348 read=125"
     659"                                ->  Hash Right Join  (cost=2465.74..4072.69 rows=37655 width=19) (actual time=9.367..33.926 rows=47343.00 loops=1)"
    664660"                                      Output: b.total_price, b.id, b.user_id, rb.value"
    665661"                                      Inner Unique: true"
    666662"                                      Hash Cond: (rb.build_id = b.id)"
    667 "                                      Buffers: shared hit=1374"
    668 "                                      ->  Seq Scan on public.rating_build rb  (cost=0.00..1373.04 rows=89104 width=9) (actual time=0.019..3.375 rows=89104.00 loops=1)"
     663"                                      Buffers: shared hit=1348 read=125"
     664"                                      ->  Seq Scan on public.rating_build rb  (cost=0.00..1373.04 rows=89104 width=9) (actual time=0.025..3.442 rows=89104.00 loops=1)"
    669665"                                            Output: rb.build_id, rb.user_id, rb.value"
    670666"                                            Buffers: shared hit=482"
    671 "                                      ->  Hash  (cost=1855.83..1855.83 rows=31695 width=14) (actual time=9.250..9.250 rows=31774.00 loops=1)"
     667"                                      ->  Hash  (cost=2069.55..2069.55 rows=31695 width=14) (actual time=9.328..9.328 rows=31774.00 loops=1)"
    672668"                                            Output: b.total_price, b.id, b.user_id"
    673669"                                            Buckets: 32768  Batches: 1  Memory Usage: 1746kB"
    674 "                                            Buffers: shared hit=892"
    675 "                                            ->  Bitmap Heap Scan on public.build b  (cost=437.17..1855.83 rows=31695 width=14) (actual time=1.482..6.062 rows=31774.00 loops=1)"
     670"                                            Buffers: shared hit=866 read=125"
     671"                                            ->  Bitmap Heap Scan on public.build b  (cost=650.89..2069.55 rows=31695 width=14) (actual time=2.199..6.497 rows=31774.00 loops=1)"
    676672"                                                  Output: b.total_price, b.id, b.user_id"
    677 "                                                  Recheck Cond: (b.is_approved AND (b.created_at >= (CURRENT_DATE - '6 mons'::interval)))"
     673"                                                  Recheck Cond: ((b.created_at >= (CURRENT_DATE - '6 mons'::interval)) AND b.is_approved)"
    678674"                                                  Heap Blocks: exact=864"
    679 "                                                  Buffers: shared hit=892"
    680 "                                                  ->  Bitmap Index Scan on idx_build_approved_date  (cost=0.00..429.25 rows=31695 width=0) (actual time=1.387..1.388 rows=31774.00 loops=1)"
    681 "                                                        Index Cond: ((b.is_approved = true) AND (b.created_at >= (CURRENT_DATE - '6 mons'::interval)))"
    682 "                                                        Index Searches: 1"
    683 "                                                        Buffers: shared hit=28"
    684 "                                ->  Hash  (cost=0.04..0.04 rows=1 width=12) (actual time=0.015..0.017 rows=0.00 loops=1)"
     675"                                                  Buffers: shared hit=866 read=125"
     676"                                                  ->  Bitmap Index Scan on idx_build_approved_created_price  (cost=0.00..642.96 rows=31695 width=0) (actual time=2.129..2.129 rows=31774.00 loops=1)"
     677"                                                        Index Cond: (b.created_at >= (CURRENT_DATE - '6 mons'::interval))"
     678"                                                        Index Searches: 2"
     679"                                                        Buffers: shared hit=2 read=125"
     680"                                ->  Hash  (cost=0.04..0.04 rows=1 width=12) (actual time=0.015..0.016 rows=0.00 loops=1)"
    685681"                                      Output: f.favorites_count, f.build_id"
    686682"                                      Buckets: 1024  Batches: 1  Memory Usage: 8kB"
    … …  
    690686"                                                  Output: favorite_build.build_id, count(DISTINCT favorite_build.user_id)"
    691687"                                                  Group Key: favorite_build.build_id"
    692 "                                                  ->  Sort  (cost=0.01..0.02 rows=1 width=8) (actual time=0.014..0.014 rows=0.00 loops=1)"
     688"                                                  ->  Sort  (cost=0.01..0.02 rows=1 width=8) (actual time=0.013..0.014 rows=0.00 loops=1)"
    693689"                                                        Output: favorite_build.build_id, favorite_build.user_id"
    694690"                                                        Sort Key: favorite_build.build_id, favorite_build.user_id"
    695691"                                                        Sort Method: quicksort  Memory: 25kB"
    696 "                                                        ->  Seq Scan on public.favorite_build  (cost=0.00..0.00 rows=1 width=8) (actual time=0.007..0.008 rows=0.00 loops=1)"
     692"                                                        ->  Seq Scan on public.favorite_build  (cost=0.00..0.00 rows=1 width=8) (actual time=0.008..0.008 rows=0.00 loops=1)"
    697693"                                                              Output: favorite_build.build_id, favorite_build.user_id"
    698694"Planning:"
    699 "  Buffers: shared hit=17"
    700 "Planning Time: 0.718 ms"
    701 "Execution Time: 117.824 ms"
    702 }}}
    703 
    704 Result: Modest improvement, but the index still helped out with unnecessary reads, reducing time from 130.489ms to 117.824ms. \\
    705 
    706 
    707 
    708 
     695"  Buffers: shared hit=42 read=1"
     696"Planning Time: 1.620 ms"
     697"Execution Time: 125.603 ms"
     698}}}
     699
     700Result: Modest improvement, but the index still helped out with unnecessary reads, reducing time from 129.409ms to 125.603ms. \\
     701
     702
     703
     704