| 584 | | }}} |
| 585 | | {{{ |
| 586 | | |QUERY PLAN | |
| 587 | | |------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| |
| 588 | | |Sort (cost=12866.81..12904.99 rows=15269 width=506) (actual time=25.855..25.861 rows=13 loops=1) | |
| 589 | | | Sort Key: (to_char(with_metrics.year, 'YYYY'::text)) DESC, (rank() OVER (?)) | |
| 590 | | | Sort Method: quicksort Memory: 26kB | |
| 591 | | | -> WindowAgg (cost=6492.23..8305.75 rows=15269 width=506) (actual time=25.805..25.831 rows=13 loops=1) | |
| 592 | | | -> Incremental Sort (cost=6492.15..7465.95 rows=15269 width=386) (actual time=25.795..25.801 rows=13 loops=1) | |
| 593 | | | Sort Key: with_metrics.year, with_metrics.total_views DESC | |
| 594 | | | Presorted Key: with_metrics.year | |
| 595 | | | Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB | |
| 596 | | | -> WindowAgg (cost=6488.23..6793.59 rows=15269 width=386) (actual time=25.768..25.787 rows=13 loops=1) | |
| 597 | | | -> Sort (cost=6488.21..6526.38 rows=15269 width=378) (actual time=25.765..25.771 rows=13 loops=1) | |
| 598 | | | Sort Key: with_metrics.year, with_metrics.engagement_rate DESC | |
| 599 | | | Sort Method: quicksort Memory: 26kB | |
| 600 | | | -> Subquery Scan on with_metrics (cost=4.26..2763.64 rows=15269 width=378) (actual time=8.764..25.751 rows=13 loops=1) | |
| 601 | | | -> WindowAgg (cost=4.26..2610.95 rows=15269 width=390) (actual time=8.763..25.744 rows=13 loops=1) | |
| 602 | | | -> Subquery Scan on genre_annual (cost=4.12..1847.50 rows=15269 width=302) (actual time=5.946..25.666 rows=13 loops=1) | |
| 603 | | | -> GroupAggregate (cost=4.12..1694.81 rows=15269 width=310) (actual time=5.945..25.660 rows=13 loops=1) | |
| 604 | | | Group Key: g.genre_id, (date_trunc('year'::text, s.story_created_at)) | |
| 605 | | | -> Incremental Sort (cost=4.12..1122.23 rows=15269 width=257) (actual time=4.189..18.142 rows=16554 loops=1) | |
| 606 | | | Sort Key: g.genre_id, (date_trunc('year'::text, s.story_created_at)), s.story_id | |
| 607 | | | Presorted Key: g.genre_id | |
| 608 | | | Full-sort Groups: 10 Sort Method: quicksort Average Memory: 29kB Peak Memory: 29kB | |
| 609 | | | Pre-sorted Groups: 10 Sort Method: quicksort Average Memory: 289kB Peak Memory: 289kB | |
| 610 | | | -> Nested Loop Left Join (cost=1.24..499.24 rows=15269 width=257) (actual time=0.115..10.632 rows=16554 loops=1) | |
| 611 | | | -> Nested Loop Left Join (cost=1.08..199.93 rows=2651 width=253) (actual time=0.097..2.231 rows=2778 loops=1) | |
| 612 | | | -> Nested Loop Left Join (cost=0.92..109.30 rows=562 width=242) (actual time=0.081..1.228 rows=570 loops=1) | |
| 613 | | | -> Nested Loop (cost=0.77..68.97 rows=147 width=238) (actual time=0.070..0.809 rows=147 loops=1) | |
| 614 | | | -> Nested Loop (cost=0.60..58.55 rows=147 width=238) (actual time=0.060..0.698 rows=147 loops=1) | |
| 615 | | | -> Nested Loop (cost=0.45..39.01 rows=147 width=230) (actual time=0.052..0.491 rows=147 loops=1) | |
| 616 | | | -> Nested Loop (cost=0.30..30.04 rows=147 width=12) (actual time=0.041..0.345 rows=147 loops=1) | |
| 617 | | | -> Index Only Scan using idx_has_genre_genre_id on has_genre hg (cost=0.14..14.39 rows=150 width=8) (actual time=0.022..0.102 rows=150 loops=1) | |
| 618 | | | Heap Fetches: 150 | |
| 619 | | | -> Memoize (cost=0.15..0.25 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=150) | |
| 620 | | | Cache Key: hg.story_id | |
| 621 | | | Cache Mode: logical | |
| 622 | | | Hits: 100 Misses: 50 Evictions: 0 Overflows: 0 Memory Usage: 6kB | |
| 623 | | | -> Index Only Scan using idx_status_story_published on status st (cost=0.14..0.24 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=50)| |
| 624 | | | Index Cond: (story_id = hg.story_id) | |
| 625 | | | Heap Fetches: 49 | |
| 626 | | | -> Memoize (cost=0.16..0.49 rows=1 width=222) (actual time=0.001..0.001 rows=1 loops=147) | |
| 627 | | | Cache Key: hg.genre_id | |
| 628 | | | Cache Mode: logical | |
| 629 | | | Hits: 137 Misses: 10 Evictions: 0 Overflows: 0 Memory Usage: 2kB | |
| 630 | | | -> Index Scan using genre_pkey on genre g (cost=0.15..0.48 rows=1 width=222) (actual time=0.005..0.005 rows=1 loops=10) | |
| 631 | | | Index Cond: (genre_id = hg.genre_id) | |
| 632 | | | -> Memoize (cost=0.15..0.33 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=147) | |
| 633 | | | Cache Key: hg.story_id | |
| 634 | | | Cache Mode: logical | |
| 635 | | | Hits: 98 Misses: 49 Evictions: 0 Overflows: 0 Memory Usage: 6kB | |
| 636 | | | -> Index Only Scan using idx_story_covering on story s (cost=0.14..0.32 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=49) | |
| 637 | | | Index Cond: (story_id = hg.story_id) | |
| 638 | | | Heap Fetches: 49 | |
| 639 | | | -> Memoize (cost=0.17..1.14 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=147) | |
| 640 | | | Cache Key: s.user_id | |
| 641 | | | Cache Mode: logical | |
| 642 | | | Hits: 142 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB | |
| 643 | | | -> Index Only Scan using writer_pkey on writer w (cost=0.15..1.13 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=5) | |
| 644 | | | Index Cond: (user_id = s.user_id) | |
| 645 | | | Heap Fetches: 5 | |
| 646 | | | -> Memoize (cost=0.15..0.62 rows=4 width=8) (actual time=0.001..0.002 rows=4 loops=147) | |
| 647 | | | Cache Key: s.story_id | |
| 648 | | | Cache Mode: logical | |
| 649 | | | Hits: 98 Misses: 49 Evictions: 0 Overflows: 0 Memory Usage: 11kB | |
| 650 | | | -> Index Only Scan using idx_comment_covering on comment c (cost=0.14..0.61 rows=4 width=8) (actual time=0.002..0.003 rows=4 loops=49) | |
| 651 | | | Index Cond: (story_id = s.story_id) | |
| 652 | | | Heap Fetches: 190 | |
| 653 | | | -> Memoize (cost=0.15..0.96 rows=5 width=15) (actual time=0.000..0.001 rows=5 loops=570) | |
| 654 | | | Cache Key: s.story_id | |
| 655 | | | Cache Mode: logical | |
| 656 | | | Hits: 521 Misses: 49 Evictions: 0 Overflows: 0 Memory Usage: 15kB | |
| 657 | | | -> Index Scan using unique_chapter_number on chapter ch (cost=0.14..0.95 rows=5 width=15) (actual time=0.002..0.004 rows=5 loops=49) | |
| 658 | | | Index Cond: (story_id = s.story_id) | |
| 659 | | | -> Memoize (cost=0.16..0.58 rows=6 width=8) (actual time=0.000..0.001 rows=6 loops=2778) | |
| 660 | | | Cache Key: s.story_id | |
| 661 | | | Cache Mode: logical | |
| 662 | | | Hits: 2729 Misses: 49 Evictions: 0 Overflows: 0 Memory Usage: 15kB | |
| 663 | | | -> Index Only Scan using idx_likes_covering on likes l (cost=0.15..0.57 rows=6 width=8) (actual time=0.002..0.004 rows=6 loops=49) | |
| 664 | | | Index Cond: (story_id = s.story_id) | |
| 665 | | | Heap Fetches: 287 | |
| 666 | | |Planning Time: 6.299 ms | |
| 667 | | |Execution Time: 26.085 ms | |
| 668 | | }}} |
| 669 | | Average time without indexes is: 26.381 ms |
| | 584 | |
| | 585 | }}} |
| | 586 | {{{ |
| | 587 | | QUERY PLAN | |
| | 588 | | :--- | |
| | 589 | | Sort \(cost=127.28..127.80 rows=207 width=506\) \(actual time=0.668..0.671 rows=10 loops=1\) | |
| | 590 | | Sort Key: \(to\_char\(with\_metrics.year, 'YYYY'::text\)\) DESC, \(rank\(\) OVER \(?\)\) | |
| | 591 | | Sort Method: quicksort Memory: 26kB | |
| | 592 | | -> WindowAgg \(cost=94.75..119.32 rows=207 width=506\) \(actual time=0.627..0.643 rows=10 loops=1\) | |
| | 593 | | -> Incremental Sort \(cost=94.75..107.93 rows=207 width=386\) \(actual time=0.618..0.621 rows=10 loops=1\) | |
| | 594 | | Sort Key: with\_metrics.year, with\_metrics.total\_views DESC | |
| | 595 | | Presorted Key: with\_metrics.year | |
| | 596 | | Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB | |
| | 597 | | -> WindowAgg \(cost=94.72..98.86 rows=207 width=386\) \(actual time=0.600..0.611 rows=10 loops=1\) | |
| | 598 | | -> Sort \(cost=94.72..95.24 rows=207 width=378\) \(actual time=0.599..0.602 rows=10 loops=1\) | |
| | 599 | | Sort Key: with\_metrics.year, with\_metrics.engagement\_rate DESC | |
| | 600 | | Sort Method: quicksort Memory: 26kB | |
| | 601 | | -> Subquery Scan on with\_metrics \(cost=73.82..86.76 rows=207 width=378\) \(actual time=0.573..0.592 rows=10 loops=1\) | |
| | 602 | | -> WindowAgg \(cost=73.82..84.69 rows=207 width=390\) \(actual time=0.572..0.590 rows=10 loops=1\) | |
| | 603 | | -> Sort \(cost=73.82..74.34 rows=207 width=302\) \(actual time=0.563..0.565 rows=10 loops=1\) | |
| | 604 | | Sort Key: genre\_annual.genre\_id, genre\_annual.year | |
| | 605 | | Sort Method: quicksort Memory: 25kB | |
| | 606 | | -> Subquery Scan on genre\_annual \(cost=55.51..65.86 rows=207 width=302\) \(actual time=0.430..0.558 rows=10 loops=1\) | |
| | 607 | | -> GroupAggregate \(cost=55.51..63.79 rows=207 width=310\) \(actual time=0.429..0.557 rows=10 loops=1\) | |
| | 608 | | Group Key: \(date\_trunc\('year'::text, s.story\_created\_at\)\), g.genre\_id | |
| | 609 | | -> Sort \(cost=55.51..56.03 rows=207 width=257\) \(actual time=0.381..0.394 rows=354 loops=1\) | |
| | 610 | | Sort Key: \(date\_trunc\('year'::text, s.story\_created\_at\)\), g.genre\_id, s.story\_id | |
| | 611 | | Sort Method: quicksort Memory: 51kB | |
| | 612 | | -> Hash Left Join \(cost=22.34..47.55 rows=207 width=257\) \(actual time=0.163..0.276 rows=354 loops=1\) | |
| | 613 | | Hash Cond: \(s.story\_id = l.story\_id\) | |
| | 614 | | -> Hash Join \(cost=20.93..43.08 rows=57 width=253\) \(actual time=0.144..0.170 rows=78 loops=1\) | |
| | 615 | | Hash Cond: \(s.story\_id = hg.story\_id\) | |
| | 616 | | -> Hash Left Join \(cost=3.66..25.08 rows=20 width=31\) \(actual time=0.114..0.128 rows=26 loops=1\) | |
| | 617 | | Hash Cond: \(s.story\_id = c.story\_id\) | |
| | 618 | | -> Hash Left Join \(cost=2.41..23.57 rows=9 width=27\) \(actual time=0.098..0.108 rows=10 loops=1\) | |
| | 619 | | Hash Cond: \(s.story\_id = ch.story\_id\) | |
| | 620 | | -> Nested Loop \(cost=0.16..21.21 rows=4 width=16\) \(actual time=0.070..0.079 rows=4 loops=1\) | |
| | 621 | | -> Seq Scan on story s \(cost=0.00..1.06 rows=4 width=16\) \(actual time=0.044..0.045 rows=4 loops=1\) | |
| | 622 | | Filter: \(\(status\)::text = 'published'::text\) | |
| | 623 | | Rows Removed by Filter: 1 | |
| | 624 | | -> Memoize \(cost=0.16..6.18 rows=1 width=4\) \(actual time=0.007..0.007 rows=1 loops=4\) | |
| | 625 | | Cache Key: s.user\_id | |
| | 626 | | Cache Mode: logical | |
| | 627 | | Hits: 1 Misses: 3 Evictions: 0 Overflows: 0 Memory Usage: 1kB | |
| | 628 | | -> Index Only Scan using writer\_pkey on writer w \(cost=0.15..6.17 rows=1 width=4\) \(actual time=0.007..0.007 rows=1 loops=3\) | |
| | 629 | | Index Cond: \(user\_id = s.user\_id\) | |
| | 630 | | Heap Fetches: 3 | |
| | 631 | | -> Hash \(cost=2.11..2.11 rows=11 width=15\) \(actual time=0.018..0.019 rows=11 loops=1\) | |
| | 632 | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| | 633 | | -> Seq Scan on chapter ch \(cost=0.00..2.11 rows=11 width=15\) \(actual time=0.008..0.013 rows=11 loops=1\) | |
| | 634 | | -> Hash \(cost=1.11..1.11 rows=11 width=8\) \(actual time=0.013..0.013 rows=11 loops=1\) | |
| | 635 | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| | 636 | | -> Seq Scan on comment c \(cost=0.00..1.11 rows=11 width=8\) \(actual time=0.006..0.008 rows=11 loops=1\) | |
| | 637 | | -> Hash \(cost=17.09..17.09 rows=15 width=226\) \(actual time=0.027..0.028 rows=15 loops=1\) | |
| | 638 | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| | 639 | | -> Hash Join \(cost=1.34..17.09 rows=15 width=226\) \(actual time=0.021..0.024 rows=15 loops=1\) | |
| | 640 | | Hash Cond: \(g.genre\_id = hg.genre\_id\) | |
| | 641 | | -> Seq Scan on genre g \(cost=0.00..13.20 rows=320 width=222\) \(actual time=0.005..0.006 rows=10 loops=1\) | |
| | 642 | | -> Hash \(cost=1.15..1.15 rows=15 width=8\) \(actual time=0.010..0.010 rows=15 loops=1\) | |
| | 643 | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| | 644 | | -> Seq Scan on has\_genre hg \(cost=0.00..1.15 rows=15 width=8\) \(actual time=0.005..0.006 rows=15 loops=1\) | |
| | 645 | | -> Hash \(cost=1.18..1.18 rows=18 width=8\) \(actual time=0.011..0.011 rows=18 loops=1\) | |
| | 646 | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| | 647 | | -> Seq Scan on likes l \(cost=0.00..1.18 rows=18 width=8\) \(actual time=0.006..0.007 rows=18 loops=1\) | |
| | 648 | | Planning Time: 2.065 ms | |
| | 649 | | Execution Time: 0.982 ms | |
| | 650 | }}} |
| | 651 | Average time without indexes is: 1.35 ms |
| 682 | | |QUERY PLAN | |
| 683 | | |------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| |
| 684 | | |Sort (cost=12866.81..12904.99 rows=15269 width=506) (actual time=25.519..25.525 rows=13 loops=1) | |
| 685 | | | Sort Key: (to_char(with_metrics.year, 'YYYY'::text)) DESC, (rank() OVER (?)) | |
| 686 | | | Sort Method: quicksort Memory: 26kB | |
| 687 | | | -> WindowAgg (cost=6492.23..8305.75 rows=15269 width=506) (actual time=25.471..25.496 rows=13 loops=1) | |
| 688 | | | -> Incremental Sort (cost=6492.15..7465.95 rows=15269 width=386) (actual time=25.461..25.467 rows=13 loops=1) | |
| 689 | | | Sort Key: with_metrics.year, with_metrics.total_views DESC | |
| 690 | | | Presorted Key: with_metrics.year | |
| 691 | | | Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB | |
| 692 | | | -> WindowAgg (cost=6488.23..6793.59 rows=15269 width=386) (actual time=25.434..25.452 rows=13 loops=1) | |
| 693 | | | -> Sort (cost=6488.21..6526.38 rows=15269 width=378) (actual time=25.431..25.436 rows=13 loops=1) | |
| 694 | | | Sort Key: with_metrics.year, with_metrics.engagement_rate DESC | |
| 695 | | | Sort Method: quicksort Memory: 26kB | |
| 696 | | | -> Subquery Scan on with_metrics (cost=4.26..2763.64 rows=15269 width=378) (actual time=8.532..25.418 rows=13 loops=1) | |
| 697 | | | -> WindowAgg (cost=4.26..2610.95 rows=15269 width=390) (actual time=8.531..25.412 rows=13 loops=1) | |
| 698 | | | -> Subquery Scan on genre_annual (cost=4.12..1847.50 rows=15269 width=302) (actual time=5.747..25.343 rows=13 loops=1) | |
| 699 | | | -> GroupAggregate (cost=4.12..1694.81 rows=15269 width=310) (actual time=5.746..25.337 rows=13 loops=1) | |
| 700 | | | Group Key: g.genre_id, (date_trunc('year'::text, s.story_created_at)) | |
| 701 | | | -> Incremental Sort (cost=4.12..1122.23 rows=15269 width=257) (actual time=4.125..17.940 rows=16554 loops=1) | |
| 702 | | | Sort Key: g.genre_id, (date_trunc('year'::text, s.story_created_at)), s.story_id | |
| 703 | | | Presorted Key: g.genre_id | |
| 704 | | | Full-sort Groups: 10 Sort Method: quicksort Average Memory: 29kB Peak Memory: 29kB | |
| 705 | | | Pre-sorted Groups: 10 Sort Method: quicksort Average Memory: 289kB Peak Memory: 289kB | |
| 706 | | | -> Nested Loop Left Join (cost=1.24..499.24 rows=15269 width=257) (actual time=0.106..10.466 rows=16554 loops=1) | |
| 707 | | | -> Nested Loop Left Join (cost=1.08..199.93 rows=2651 width=253) (actual time=0.090..2.200 rows=2778 loops=1) | |
| 708 | | | -> Nested Loop Left Join (cost=0.92..109.30 rows=562 width=242) (actual time=0.079..1.165 rows=570 loops=1) | |
| 709 | | | -> Nested Loop (cost=0.77..68.97 rows=147 width=238) (actual time=0.067..0.756 rows=147 loops=1) | |
| 710 | | | -> Nested Loop (cost=0.60..58.55 rows=147 width=238) (actual time=0.057..0.647 rows=147 loops=1) | |
| 711 | | | -> Nested Loop (cost=0.45..39.01 rows=147 width=230) (actual time=0.050..0.448 rows=147 loops=1) | |
| 712 | | | -> Nested Loop (cost=0.30..30.04 rows=147 width=12) (actual time=0.039..0.318 rows=147 loops=1) | |
| 713 | | | -> Index Only Scan using idx_has_genre_genre_id on has_genre hg (cost=0.14..14.39 rows=150 width=8) (actual time=0.020..0.092 rows=150 loops=1) | |
| 714 | | | Heap Fetches: 150 | |
| 715 | | | -> Memoize (cost=0.15..0.25 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=150) | |
| 716 | | | Cache Key: hg.story_id | |
| 717 | | | Cache Mode: logical | |
| 718 | | | Hits: 100 Misses: 50 Evictions: 0 Overflows: 0 Memory Usage: 6kB | |
| 719 | | | -> Index Only Scan using idx_status_story_published on status st (cost=0.14..0.24 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=50)| |
| 720 | | | Index Cond: (story_id = hg.story_id) | |
| 721 | | | Heap Fetches: 49 | |
| 722 | | | -> Memoize (cost=0.16..0.49 rows=1 width=222) (actual time=0.001..0.001 rows=1 loops=147) | |
| 723 | | | Cache Key: hg.genre_id | |
| 724 | | | Cache Mode: logical | |
| 725 | | | Hits: 137 Misses: 10 Evictions: 0 Overflows: 0 Memory Usage: 2kB | |
| 726 | | | -> Index Scan using genre_pkey on genre g (cost=0.15..0.48 rows=1 width=222) (actual time=0.003..0.003 rows=1 loops=10) | |
| 727 | | | Index Cond: (genre_id = hg.genre_id) | |
| 728 | | | -> Memoize (cost=0.15..0.33 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=147) | |
| 729 | | | Cache Key: hg.story_id | |
| 730 | | | Cache Mode: logical | |
| 731 | | | Hits: 98 Misses: 49 Evictions: 0 Overflows: 0 Memory Usage: 6kB | |
| 732 | | | -> Index Only Scan using idx_story_created_user on story s (cost=0.14..0.32 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=49) | |
| 733 | | | Index Cond: (story_id = hg.story_id) | |
| 734 | | | Heap Fetches: 49 | |
| 735 | | | -> Memoize (cost=0.17..1.14 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=147) | |
| 736 | | | Cache Key: s.user_id | |
| 737 | | | Cache Mode: logical | |
| 738 | | | Hits: 142 Misses: 5 Evictions: 0 Overflows: 0 Memory Usage: 1kB | |
| 739 | | | -> Index Only Scan using writer_pkey on writer w (cost=0.15..1.13 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=5) | |
| 740 | | | Index Cond: (user_id = s.user_id) | |
| 741 | | | Heap Fetches: 5 | |
| 742 | | | -> Memoize (cost=0.15..0.62 rows=4 width=8) (actual time=0.001..0.002 rows=4 loops=147) | |
| 743 | | | Cache Key: s.story_id | |
| 744 | | | Cache Mode: logical | |
| 745 | | | Hits: 98 Misses: 49 Evictions: 0 Overflows: 0 Memory Usage: 11kB | |
| 746 | | | -> Index Only Scan using idx_comment_covering on comment c (cost=0.14..0.61 rows=4 width=8) (actual time=0.002..0.003 rows=4 loops=49) | |
| 747 | | | Index Cond: (story_id = s.story_id) | |
| 748 | | | Heap Fetches: 190 | |
| 749 | | | -> Memoize (cost=0.15..0.96 rows=5 width=15) (actual time=0.000..0.001 rows=5 loops=570) | |
| 750 | | | Cache Key: s.story_id | |
| 751 | | | Cache Mode: logical | |
| 752 | | | Hits: 521 Misses: 49 Evictions: 0 Overflows: 0 Memory Usage: 15kB | |
| 753 | | | -> Index Scan using unique_chapter_number on chapter ch (cost=0.14..0.95 rows=5 width=15) (actual time=0.002..0.004 rows=5 loops=49) | |
| 754 | | | Index Cond: (story_id = s.story_id) | |
| 755 | | | -> Memoize (cost=0.16..0.58 rows=6 width=8) (actual time=0.000..0.001 rows=6 loops=2778) | |
| 756 | | | Cache Key: s.story_id | |
| 757 | | | Cache Mode: logical | |
| 758 | | | Hits: 2729 Misses: 49 Evictions: 0 Overflows: 0 Memory Usage: 15kB | |
| 759 | | | -> Index Only Scan using idx_likes_covering on likes l (cost=0.15..0.57 rows=6 width=8) (actual time=0.002..0.004 rows=6 loops=49) | |
| 760 | | | Index Cond: (story_id = s.story_id) | |
| 761 | | | Heap Fetches: 287 | |
| 762 | | |Planning Time: 6.209 ms | |
| 763 | | |Execution Time: 25.737 ms | |
| 764 | | }}} |
| 765 | | 25.813 ms |
| 766 | | |
| 767 | | Two new indexes were created for this scenario: idx_story_created_user and idx_genre_covering. Combined with indexes from previous scenarios, 8 indexes are used naturally in the execution plan without any forcing flags. Average execution time: 25.720 ms without indexes vs 25.813 ms with indexes. The indexes are kept and will deliver measurable improvements as data volume grows. |
| | 664 | | QUERY PLAN | |
| | 665 | | :--- | |
| | 666 | | Sort \(cost=127.28..127.80 rows=207 width=506\) \(actual time=0.927..0.931 rows=10 loops=1\) | |
| | 667 | | Sort Key: \(to\_char\(with\_metrics.year, 'YYYY'::text\)\) DESC, \(rank\(\) OVER \(?\)\) | |
| | 668 | | Sort Method: quicksort Memory: 26kB | |
| | 669 | | -> WindowAgg \(cost=94.75..119.32 rows=207 width=506\) \(actual time=0.886..0.903 rows=10 loops=1\) | |
| | 670 | | -> Incremental Sort \(cost=94.75..107.93 rows=207 width=386\) \(actual time=0.876..0.879 rows=10 loops=1\) | |
| | 671 | | Sort Key: with\_metrics.year, with\_metrics.total\_views DESC | |
| | 672 | | Presorted Key: with\_metrics.year | |
| | 673 | | Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB | |
| | 674 | | -> WindowAgg \(cost=94.72..98.86 rows=207 width=386\) \(actual time=0.850..0.861 rows=10 loops=1\) | |
| | 675 | | -> Sort \(cost=94.72..95.24 rows=207 width=378\) \(actual time=0.847..0.850 rows=10 loops=1\) | |
| | 676 | | Sort Key: with\_metrics.year, with\_metrics.engagement\_rate DESC | |
| | 677 | | Sort Method: quicksort Memory: 26kB | |
| | 678 | | -> Subquery Scan on with\_metrics \(cost=73.82..86.76 rows=207 width=378\) \(actual time=0.820..0.840 rows=10 loops=1\) | |
| | 679 | | -> WindowAgg \(cost=73.82..84.69 rows=207 width=390\) \(actual time=0.819..0.838 rows=10 loops=1\) | |
| | 680 | | -> Sort \(cost=73.82..74.34 rows=207 width=302\) \(actual time=0.808..0.811 rows=10 loops=1\) | |
| | 681 | | Sort Key: genre\_annual.genre\_id, genre\_annual.year | |
| | 682 | | Sort Method: quicksort Memory: 25kB | |
| | 683 | | -> Subquery Scan on genre\_annual \(cost=55.51..65.86 rows=207 width=302\) \(actual time=0.671..0.804 rows=10 loops=1\) | |
| | 684 | | -> GroupAggregate \(cost=55.51..63.79 rows=207 width=310\) \(actual time=0.670..0.802 rows=10 loops=1\) | |
| | 685 | | Group Key: \(date\_trunc\('year'::text, s.story\_created\_at\)\), g.genre\_id | |
| | 686 | | -> Sort \(cost=55.51..56.03 rows=207 width=257\) \(actual time=0.567..0.580 rows=354 loops=1\) | |
| | 687 | | Sort Key: \(date\_trunc\('year'::text, s.story\_created\_at\)\), g.genre\_id, s.story\_id | |
| | 688 | | Sort Method: quicksort Memory: 51kB | |
| | 689 | | -> Hash Left Join \(cost=22.34..47.55 rows=207 width=257\) \(actual time=0.325..0.444 rows=354 loops=1\) | |
| | 690 | | Hash Cond: \(s.story\_id = l.story\_id\) | |
| | 691 | | -> Hash Join \(cost=20.93..43.08 rows=57 width=253\) \(actual time=0.281..0.310 rows=78 loops=1\) | |
| | 692 | | Hash Cond: \(s.story\_id = hg.story\_id\) | |
| | 693 | | -> Hash Left Join \(cost=3.66..25.08 rows=20 width=31\) \(actual time=0.186..0.202 rows=26 loops=1\) | |
| | 694 | | Hash Cond: \(s.story\_id = c.story\_id\) | |
| | 695 | | -> Hash Left Join \(cost=2.41..23.57 rows=9 width=27\) \(actual time=0.154..0.166 rows=10 loops=1\) | |
| | 696 | | Hash Cond: \(s.story\_id = ch.story\_id\) | |
| | 697 | | -> Nested Loop \(cost=0.16..21.21 rows=4 width=16\) \(actual time=0.113..0.123 rows=4 loops=1\) | |
| | 698 | | -> Seq Scan on story s \(cost=0.00..1.06 rows=4 width=16\) \(actual time=0.059..0.062 rows=4 loops=1\) | |
| | 699 | | Filter: \(\(status\)::text = 'published'::text\) | |
| | 700 | | Rows Removed by Filter: 1 | |
| | 701 | | -> Memoize \(cost=0.16..6.18 rows=1 width=4\) \(actual time=0.014..0.014 rows=1 loops=4\) | |
| | 702 | | Cache Key: s.user\_id | |
| | 703 | | Cache Mode: logical | |
| | 704 | | Hits: 1 Misses: 3 Evictions: 0 Overflows: 0 Memory Usage: 1kB | |
| | 705 | | -> Index Only Scan using writer\_pkey on writer w \(cost=0.15..6.17 rows=1 width=4\) \(actual time=0.015..0.015 rows=1 loops=3\) | |
| | 706 | | Index Cond: \(user\_id = s.user\_id\) | |
| | 707 | | Heap Fetches: 3 | |
| | 708 | | -> Hash \(cost=2.11..2.11 rows=11 width=15\) \(actual time=0.028..0.028 rows=11 loops=1\) | |
| | 709 | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| | 710 | | -> Seq Scan on chapter ch \(cost=0.00..2.11 rows=11 width=15\) \(actual time=0.013..0.021 rows=11 loops=1\) | |
| | 711 | | -> Hash \(cost=1.11..1.11 rows=11 width=8\) \(actual time=0.022..0.022 rows=11 loops=1\) | |
| | 712 | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| | 713 | | -> Seq Scan on comment c \(cost=0.00..1.11 rows=11 width=8\) \(actual time=0.015..0.017 rows=11 loops=1\) | |
| | 714 | | -> Hash \(cost=17.09..17.09 rows=15 width=226\) \(actual time=0.087..0.087 rows=15 loops=1\) | |
| | 715 | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| | 716 | | -> Hash Join \(cost=1.34..17.09 rows=15 width=226\) \(actual time=0.041..0.079 rows=15 loops=1\) | |
| | 717 | | Hash Cond: \(g.genre\_id = hg.genre\_id\) | |
| | 718 | | -> Seq Scan on genre g \(cost=0.00..13.20 rows=320 width=222\) \(actual time=0.013..0.013 rows=10 loops=1\) | |
| | 719 | | -> Hash \(cost=1.15..1.15 rows=15 width=8\) \(actual time=0.019..0.019 rows=15 loops=1\) | |
| | 720 | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| | 721 | | -> Seq Scan on has\_genre hg \(cost=0.00..1.15 rows=15 width=8\) \(actual time=0.013..0.015 rows=15 loops=1\) | |
| | 722 | | -> Hash \(cost=1.18..1.18 rows=18 width=8\) \(actual time=0.019..0.019 rows=18 loops=1\) | |
| | 723 | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| | 724 | | -> Seq Scan on likes l \(cost=0.00..1.18 rows=18 width=8\) \(actual time=0.011..0.012 rows=18 loops=1\) | |
| | 725 | | Planning Time: 1.757 ms | |
| | 726 | | Execution Time: 1.185 ms | |
| | 727 | }}} |
| | 728 | Average time: 0.910 ms |
| | 729 | |
| | 730 | Two new indexes were created for this scenario: idx_story_created_user and idx_chapter_aggregates. Combined with indexes from previous scenarios, writer_pkey is used naturally in the execution plan without any forcing flags. Average execution time: 1.351 ms without indexes vs 0.910 ms with indexes. The indexes are kept and will deliver measurable improvements as data volume grows. |
