| | 577 | |
| | 578 | == Scenario 3 — Quarterly writer performance report |
| | 579 | {{{ |
| | 580 | EXPLAIN ANALYZE |
| | 581 | WITH quarterly_stats AS ( |
| | 582 | SELECT |
| | 583 | DATE_TRUNC('quarter', s.story_created_at) AS quarter, |
| | 584 | u.user_id, u.username, u.user_name, u.surname, |
| | 585 | COUNT(DISTINCT s.story_id) AS stories_published, |
| | 586 | COUNT(DISTINCT ch.chapter_id) AS chapters_written, |
| | 587 | COALESCE(SUM(ch.view_count), 0) AS total_views, |
| | 588 | COALESCE(SUM(ch.word_count), 0) AS total_words, |
| | 589 | COUNT(DISTINCT l.user_id) AS total_likes, |
| | 590 | COUNT(DISTINCT c.comment_id) AS total_comments, |
| | 591 | ROUND(AVG(ch.rating), 2) AS avg_rating |
| | 592 | FROM story s |
| | 593 | JOIN writer w ON s.user_id = w.user_id |
| | 594 | JOIN users u ON w.user_id = u.user_id |
| | 595 | JOIN status st ON s.story_id = st.story_id AND st.status = 'published' |
| | 596 | LEFT JOIN chapter ch ON s.story_id = ch.story_id |
| | 597 | LEFT JOIN likes l ON s.story_id = l.story_id |
| | 598 | LEFT JOIN comment c ON s.story_id = c.story_id |
| | 599 | GROUP BY |
| | 600 | DATE_TRUNC('quarter', s.story_created_at), |
| | 601 | u.user_id, u.username, u.user_name, u.surname |
| | 602 | ), |
| | 603 | with_growth AS ( |
| | 604 | SELECT *, |
| | 605 | LAG(total_views) OVER (PARTITION BY user_id ORDER BY quarter) AS prev_views, |
| | 606 | LAG(total_likes) OVER (PARTITION BY user_id ORDER BY quarter) AS prev_likes, |
| | 607 | LAG(total_comments) OVER (PARTITION BY user_id ORDER BY quarter) AS prev_comments, |
| | 608 | ROUND((total_views - LAG(total_views) OVER (PARTITION BY user_id ORDER BY quarter))::DECIMAL |
| | 609 | / NULLIF(LAG(total_views) OVER (PARTITION BY user_id ORDER BY quarter), 0) * 100, 2) AS views_growth_pct, |
| | 610 | ROUND((total_likes - LAG(total_likes) OVER (PARTITION BY user_id ORDER BY quarter))::DECIMAL |
| | 611 | / NULLIF(LAG(total_likes) OVER (PARTITION BY user_id ORDER BY quarter), 0) * 100, 2) AS likes_growth_pct |
| | 612 | FROM quarterly_stats |
| | 613 | ) |
| | 614 | SELECT |
| | 615 | TO_CHAR(quarter, 'YYYY "Q"Q') AS period, |
| | 616 | username, user_name, surname, |
| | 617 | stories_published, chapters_written, total_words, |
| | 618 | total_views, |
| | 619 | COALESCE(views_growth_pct, 0) AS views_growth_pct, |
| | 620 | total_likes, |
| | 621 | COALESCE(likes_growth_pct, 0) AS likes_growth_pct, |
| | 622 | total_comments, |
| | 623 | COALESCE(avg_rating, 0) AS avg_rating, |
| | 624 | RANK() OVER (PARTITION BY quarter ORDER BY total_views DESC) AS rank_by_views |
| | 625 | FROM with_growth |
| | 626 | ORDER BY quarter DESC, rank_by_views; |
| | 627 | }}} |
| | 628 | === Analysis without indexes: |
| | 629 | {{{ |
| | 630 | |QUERY PLAN | |
| | 631 | |----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| |
| | 632 | |Sort (cost=295447.54..295697.57 rows=100011 width=1144) (actual time=380.965..380.977 rows=14 loops=1) | |
| | 633 | | Sort Key: with_growth.quarter DESC, (rank() OVER (?)) | |
| | 634 | | Sort Method: quicksort Memory: 26kB | |
| | 635 | | -> WindowAgg (cost=185071.50..187321.72 rows=100011 width=1144) (actual time=380.907..380.958 rows=14 loops=1) | |
| | 636 | | -> Sort (cost=185071.48..185321.50 rows=100011 width=1104) (actual time=380.856..380.868 rows=14 loops=1) | |
| | 637 | | Sort Key: with_growth.quarter, with_growth.total_views DESC | |
| | 638 | | Sort Method: quicksort Memory: 26kB | |
| | 639 | | -> Subquery Scan on with_growth (cost=28996.63..80361.66 rows=100011 width=1104) (actual time=160.911..380.850 rows=14 loops=1) | |
| | 640 | | -> WindowAgg (cost=28996.63..79361.55 rows=100011 width=1132) (actual time=160.905..380.839 rows=14 loops=1) | |
| | 641 | | -> GroupAggregate (cost=28995.22..73860.95 rows=100011 width=1044) (actual time=160.788..380.735 rows=14 loops=1) | |
| | 642 | | Group Key: u.user_id, (date_trunc('quarter'::text, s.story_created_at)) | |
| | 643 | | -> Incremental Sort (cost=28995.22..69860.51 rows=100011 width=994) (actual time=160.712..342.505 rows=110218 loops=1) | |
| | 644 | | Sort Key: u.user_id, (date_trunc('quarter'::text, s.story_created_at)), s.story_id | |
| | 645 | | Presorted Key: u.user_id | |
| | 646 | | Full-sort Groups: 3 Sort Method: quicksort Average Memory: 30kB Peak Memory: 30kB | |
| | 647 | | Pre-sorted Groups: 2 Sort Methods: quicksort, external merge Average Memory: 13kB Peak Memory: 26kB Average Disk: 3760kB Peak Disk: 7520kB | |
| | 648 | | -> Merge Join (cost=15790.20..17540.59 rows=100011 width=994) (actual time=160.635..229.966 rows=110218 loops=1) | |
| | 649 | | Merge Cond: (s.user_id = u.user_id) | |
| | 650 | | -> Sort (cost=15778.73..16028.76 rows=100011 width=46) (actual time=160.547..175.009 rows=110218 loops=1) | |
| | 651 | | Sort Key: s.user_id | |
| | 652 | | Sort Method: external merge Disk: 4504kB | |
| | 653 | | -> Hash Right Join (cost=1082.66..4392.92 rows=100011 width=46) (actual time=74.630..114.895 rows=110218 loops=1) | |
| | 654 | | Hash Cond: (c.story_id = s.story_id) | |
| | 655 | | -> Seq Scan on comment c (cost=0.00..1935.11 rows=100011 width=8) (actual time=0.011..8.173 rows=100011 loops=1) | |
| | 656 | | -> Hash (cost=957.59..957.59 rows=10005 width=42) (actual time=74.569..74.577 rows=10145 loops=1) | |
| | 657 | | Buckets: 16384 Batches: 1 Memory Usage: 726kB | |
| | 658 | | -> Hash Left Join (cost=812.87..957.59 rows=10005 width=42) (actual time=64.682..71.998 rows=10145 loops=1) | |
| | 659 | | Hash Cond: (s.story_id = l.story_id) | |
| | 660 | | -> Hash Join (cost=811.44..868.43 rows=10005 width=38) (actual time=64.638..70.028 rows=10111 loops=1) | |
| | 661 | | Hash Cond: (s.story_id = st.story_id) | |
| | 662 | | -> Hash Join (cost=416.49..447.21 rows=10005 width=38) (actual time=3.787..7.183 rows=10111 loops=1) | |
| | 663 | | Hash Cond: (s.user_id = w.user_id) | |
| | 664 | | -> Hash Right Join (cost=349.11..353.53 rows=10005 width=34) (actual time=3.736..5.286 rows=10111 loops=1) | |
| | 665 | | Hash Cond: (ch.story_id = s.story_id) | |
| | 666 | | -> Seq Scan on chapter ch (cost=0.00..4.12 rows=112 width=22) (actual time=0.012..0.030 rows=112 loops=1) | |
| | 667 | | -> Hash (cost=224.05..224.05 rows=10005 width=16) (actual time=3.677..3.678 rows=10005 loops=1) | |
| | 668 | | Buckets: 16384 Batches: 1 Memory Usage: 597kB | |
| | 669 | | -> Seq Scan on story s (cost=0.00..224.05 rows=10005 width=16) (actual time=0.017..1.823 rows=10005 loops=1)| |
| | 670 | | -> Hash (cost=35.50..35.50 rows=2550 width=4) (actual time=0.025..0.026 rows=5 loops=1) | |
| | 671 | | Buckets: 4096 Batches: 1 Memory Usage: 33kB | |
| | 672 | | -> Seq Scan on writer w (cost=0.00..35.50 rows=2550 width=4) (actual time=0.010..0.012 rows=5 loops=1) | |
| | 673 | | -> Hash (cost=269.89..269.89 rows=10005 width=4) (actual time=60.813..60.813 rows=10005 loops=1) | |
| | 674 | | Buckets: 16384 Batches: 1 Memory Usage: 480kB | |
| | 675 | | -> Seq Scan on status st (cost=0.00..269.89 rows=10005 width=4) (actual time=57.194..59.348 rows=10005 loops=1) | |
| | 676 | | Filter: ((status)::text = 'published'::text) | |
| | 677 | | Rows Removed by Filter: 5026 | |
| | 678 | | -> Hash (cost=1.19..1.19 rows=19 width=8) (actual time=0.024..0.025 rows=19 loops=1) | |
| | 679 | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| | 680 | | -> Seq Scan on likes l (cost=0.00..1.19 rows=19 width=8) (actual time=0.012..0.015 rows=19 loops=1) | |
| | 681 | | -> Sort (cost=11.46..11.56 rows=40 width=956) (actual time=0.043..0.044 rows=9 loops=1) | |
| | 682 | | Sort Key: u.user_id | |
| | 683 | | Sort Method: quicksort Memory: 25kB | |
| | 684 | | -> Seq Scan on users u (cost=0.00..10.40 rows=40 width=956) (actual time=0.022..0.025 rows=10 loops=1) | |
| | 685 | |Planning Time: 4.139 ms | |
| | 686 | |JIT: | |
| | 687 | | Functions: 75 | |
| | 688 | | Options: Inlining false, Optimization false, Expressions true, Deforming true | |
| | 689 | | Timing: Generation 5.977 ms (Deform 3.329 ms), Inlining 0.000 ms, Optimization 2.386 ms, Emission 55.005 ms, Total 63.368 ms | |
| | 690 | |Execution Time: 390.828 ms | |
| | 691 | }}} |
| | 692 | === We create indexes |
| | 693 | {{{ |
| | 694 | CREATE INDEX idx_story_quarter |
| | 695 | ON story(story_created_at, user_id, story_id); |
| | 696 | |
| | 697 | ANALYZE story; |
| | 698 | ANALYZE status; |
| | 699 | ANALYZE chapter; |
| | 700 | ANALYZE likes; |
| | 701 | ANALYZE comment; |
| | 702 | }}} |
| | 703 | === After indexes we get: |
| | 704 | {{{ |
| | 705 | |QUERY PLAN | |
| | 706 | |----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| |
| | 707 | |Sort (cost=295447.54..295697.57 rows=100011 width=1144) (actual time=385.005..385.018 rows=14 loops=1) | |
| | 708 | | Sort Key: with_growth.quarter DESC, (rank() OVER (?)) | |
| | 709 | | Sort Method: quicksort Memory: 26kB | |
| | 710 | | -> WindowAgg (cost=185071.50..187321.72 rows=100011 width=1144) (actual time=384.949..384.998 rows=14 loops=1) | |
| | 711 | | -> Sort (cost=185071.48..185321.50 rows=100011 width=1104) (actual time=384.909..384.921 rows=14 loops=1) | |
| | 712 | | Sort Key: with_growth.quarter, with_growth.total_views DESC | |
| | 713 | | Sort Method: quicksort Memory: 26kB | |
| | 714 | | -> Subquery Scan on with_growth (cost=28996.63..80361.66 rows=100011 width=1104) (actual time=164.271..384.904 rows=14 loops=1) | |
| | 715 | | -> WindowAgg (cost=28996.63..79361.55 rows=100011 width=1132) (actual time=164.265..384.892 rows=14 loops=1) | |
| | 716 | | -> GroupAggregate (cost=28995.22..73860.95 rows=100011 width=1044) (actual time=164.169..384.779 rows=14 loops=1) | |
| | 717 | | Group Key: u.user_id, (date_trunc('quarter'::text, s.story_created_at)) | |
| | 718 | | -> Incremental Sort (cost=28995.22..69860.51 rows=100011 width=994) (actual time=164.095..346.459 rows=110218 loops=1) | |
| | 719 | | Sort Key: u.user_id, (date_trunc('quarter'::text, s.story_created_at)), s.story_id | |
| | 720 | | Presorted Key: u.user_id | |
| | 721 | | Full-sort Groups: 3 Sort Method: quicksort Average Memory: 30kB Peak Memory: 30kB | |
| | 722 | | Pre-sorted Groups: 2 Sort Methods: quicksort, external merge Average Memory: 13kB Peak Memory: 26kB Average Disk: 3760kB Peak Disk: 7520kB | |
| | 723 | | -> Merge Join (cost=15790.20..17540.59 rows=100011 width=994) (actual time=164.019..233.324 rows=110218 loops=1) | |
| | 724 | | Merge Cond: (s.user_id = u.user_id) | |
| | 725 | | -> Sort (cost=15778.73..16028.76 rows=100011 width=46) (actual time=163.944..178.489 rows=110218 loops=1) | |
| | 726 | | Sort Key: s.user_id | |
| | 727 | | Sort Method: external merge Disk: 4504kB | |
| | 728 | | -> Hash Right Join (cost=1082.66..4392.92 rows=100011 width=46) (actual time=76.636..117.141 rows=110218 loops=1) | |
| | 729 | | Hash Cond: (c.story_id = s.story_id) | |
| | 730 | | -> Seq Scan on comment c (cost=0.00..1935.11 rows=100011 width=8) (actual time=0.010..8.251 rows=100011 loops=1) | |
| | 731 | | -> Hash (cost=957.59..957.59 rows=10005 width=42) (actual time=76.575..76.583 rows=10145 loops=1) | |
| | 732 | | Buckets: 16384 Batches: 1 Memory Usage: 726kB | |
| | 733 | | -> Hash Left Join (cost=812.87..957.59 rows=10005 width=42) (actual time=66.657..73.978 rows=10145 loops=1) | |
| | 734 | | Hash Cond: (s.story_id = l.story_id) | |
| | 735 | | -> Hash Join (cost=811.44..868.43 rows=10005 width=38) (actual time=66.611..71.975 rows=10111 loops=1) | |
| | 736 | | Hash Cond: (s.story_id = st.story_id) | |
| | 737 | | -> Hash Join (cost=416.49..447.21 rows=10005 width=38) (actual time=3.840..7.192 rows=10111 loops=1) | |
| | 738 | | Hash Cond: (s.user_id = w.user_id) | |
| | 739 | | -> Hash Right Join (cost=349.11..353.53 rows=10005 width=34) (actual time=3.785..5.305 rows=10111 loops=1) | |
| | 740 | | Hash Cond: (ch.story_id = s.story_id) | |
| | 741 | | -> Seq Scan on chapter ch (cost=0.00..4.12 rows=112 width=22) (actual time=0.011..0.028 rows=112 loops=1) | |
| | 742 | | -> Hash (cost=224.05..224.05 rows=10005 width=16) (actual time=3.725..3.726 rows=10005 loops=1) | |
| | 743 | | Buckets: 16384 Batches: 1 Memory Usage: 597kB | |
| | 744 | | -> Seq Scan on story s (cost=0.00..224.05 rows=10005 width=16) (actual time=0.018..1.860 rows=10005 loops=1)| |
| | 745 | | -> Hash (cost=35.50..35.50 rows=2550 width=4) (actual time=0.029..0.030 rows=5 loops=1) | |
| | 746 | | Buckets: 4096 Batches: 1 Memory Usage: 33kB | |
| | 747 | | -> Seq Scan on writer w (cost=0.00..35.50 rows=2550 width=4) (actual time=0.012..0.013 rows=5 loops=1) | |
| | 748 | | -> Hash (cost=269.89..269.89 rows=10005 width=4) (actual time=62.733..62.734 rows=10005 loops=1) | |
| | 749 | | Buckets: 16384 Batches: 1 Memory Usage: 480kB | |
| | 750 | | -> Seq Scan on status st (cost=0.00..269.89 rows=10005 width=4) (actual time=59.051..61.260 rows=10005 loops=1) | |
| | 751 | | Filter: ((status)::text = 'published'::text) | |
| | 752 | | Rows Removed by Filter: 5026 | |
| | 753 | | -> Hash (cost=1.19..1.19 rows=19 width=8) (actual time=0.025..0.026 rows=19 loops=1) | |
| | 754 | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| | 755 | | -> Seq Scan on likes l (cost=0.00..1.19 rows=19 width=8) (actual time=0.012..0.016 rows=19 loops=1) | |
| | 756 | | -> Sort (cost=11.46..11.56 rows=40 width=956) (actual time=0.032..0.033 rows=9 loops=1) | |
| | 757 | | Sort Key: u.user_id | |
| | 758 | | Sort Method: quicksort Memory: 25kB | |
| | 759 | | -> Seq Scan on users u (cost=0.00..10.40 rows=40 width=956) (actual time=0.021..0.023 rows=10 loops=1) | |
| | 760 | |Planning Time: 4.181 ms | |
| | 761 | |JIT: | |
| | 762 | | Functions: 75 | |
| | 763 | | Options: Inlining false, Optimization false, Expressions true, Deforming true | |
| | 764 | | Timing: Generation 5.996 ms (Deform 3.374 ms), Inlining 0.000 ms, Optimization 2.381 ms, Emission 56.875 ms, Total 65.252 ms | |
| | 765 | |Execution Time: 394.812 ms | |
| | 766 | }}} |
| | 767 | Average time: 396.791 ms |
| | 768 | We conclude that the indexes added for this query provided no meaningful improvement, with execution time remaining virtually unchanged from 393 ms to 397 ms (~+1%), which is within normal measurement variance. The query plan reveals two core issues: sort operations spilling to disk (up to 7.5 MB), and several tables (chapter, likes, writer) being too small for index scans to be beneficial. The indexes are therefore not kept, as they introduce write overhead without any measurable read benefit. |
| | 769 | }}} |
| | 770 | }}} |
