| | 404 | |
| | 405 | == Scenario 2 - Annual genre popularity and engagement trend |
| | 406 | |
| | 407 | == Without index analysis |
| | 408 | {{{ |
| | 409 | |QUERY PLAN | |
| | 410 | |---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| |
| | 411 | |Sort (cost=13106.00..13134.14 rows=11256 width=506) (actual time=26.052..26.060 rows=10 loops=1) | |
| | 412 | | Sort Key: (to_char(with_metrics.year, 'YYYY'::text)) DESC, (rank() OVER (?)) | |
| | 413 | | Sort Method: quicksort Memory: 26kB | |
| | 414 | | -> WindowAgg (cost=8455.78..9769.06 rows=11256 width=506) (actual time=26.011..26.036 rows=10 loops=1) | |
| | 415 | | -> Incremental Sort (cost=8455.70..9149.98 rows=11256 width=386) (actual time=26.000..26.008 rows=10 loops=1) | |
| | 416 | | Sort Key: with_metrics.year, with_metrics.total_views DESC | |
| | 417 | | Presorted Key: with_metrics.year | |
| | 418 | | Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB | |
| | 419 | | -> WindowAgg (cost=8452.94..8678.04 rows=11256 width=386) (actual time=25.968..25.985 rows=10 loops=1) | |
| | 420 | | -> Sort (cost=8452.92..8481.06 rows=11256 width=378) (actual time=25.964..25.972 rows=10 loops=1) | |
| | 421 | | Sort Key: with_metrics.year, with_metrics.engagement_rate DESC | |
| | 422 | | Sort Method: quicksort Memory: 26kB | |
| | 423 | | -> Subquery Scan on with_metrics (cost=3888.85..5731.98 rows=11256 width=378) (actual time=25.567..25.960 rows=10 loops=1) | |
| | 424 | | -> WindowAgg (cost=3888.85..5619.42 rows=11256 width=390) (actual time=25.565..25.956 rows=10 loops=1) | |
| | 425 | | -> Subquery Scan on genre_annual (cost=3888.72..5056.62 rows=11256 width=302) (actual time=25.405..25.906 rows=10 loops=1) | |
| | 426 | | -> GroupAggregate (cost=3888.72..4944.06 rows=11256 width=310) (actual time=25.404..25.903 rows=10 loops=1) | |
| | 427 | | Group Key: g.genre_id, (date_trunc('year'::text, s.story_created_at)) | |
| | 428 | | -> Incremental Sort (cost=3888.72..4521.96 rows=11256 width=256) (actual time=25.290..25.595 rows=354 loops=1) | |
| | 429 | | Sort Key: g.genre_id, (date_trunc('year'::text, s.story_created_at)), s.story_id | |
| | 430 | | Presorted Key: g.genre_id | |
| | 431 | | Full-sort Groups: 6 Sort Method: quicksort Average Memory: 29kB Peak Memory: 29kB | |
| | 432 | | Pre-sorted Groups: 2 Sort Method: quicksort Average Memory: 29kB Peak Memory: 29kB | |
| | 433 | | -> Merge Join (cost=3887.20..4085.78 rows=11256 width=256) (actual time=25.170..25.379 rows=354 loops=1) | |
| | 434 | | Merge Cond: (hg.genre_id = g.genre_id) | |
| | 435 | | -> Sort (cost=3860.69..3888.83 rows=11256 width=38) (actual time=25.136..25.164 rows=354 loops=1) | |
| | 436 | | Sort Key: hg.genre_id | |
| | 437 | | Sort Method: quicksort Memory: 47kB | |
| | 438 | | -> Hash Right Join (cost=680.54..3103.25 rows=11256 width=38) (actual time=6.911..25.050 rows=354 loops=1) | |
| | 439 | | Hash Cond: (c.story_id = s.story_id) | |
| | 440 | | -> Seq Scan on comment c (cost=0.00..1935.11 rows=100011 width=8) (actual time=0.023..7.354 rows=100011 loops=1) | |
| | 441 | | -> Hash (cost=666.46..666.46 rows=1126 width=34) (actual time=6.878..6.883 rows=132 loops=1) | |
| | 442 | | Buckets: 2048 Batches: 1 Memory Usage: 26kB | |
| | 443 | | -> Hash Left Join (cost=623.05..666.46 rows=1126 width=34) (actual time=6.759..6.851 rows=132 loops=1) | |
| | 444 | | Hash Cond: (s.story_id = l.story_id) | |
| | 445 | | -> Nested Loop (cost=621.62..655.16 rows=1126 width=30) (actual time=6.734..6.800 rows=30 loops=1) | |
| | 446 | | -> Hash Right Join (cost=621.46..626.13 rows=1126 width=30) (actual time=6.705..6.750 rows=30 loops=1) | |
| | 447 | | Hash Cond: (ch.story_id = s.story_id) | |
| | 448 | | -> Seq Scan on chapter ch (cost=0.00..4.12 rows=112 width=14) (actual time=0.012..0.025 rows=112 loops=1) | |
| | 449 | | -> Hash (cost=607.38..607.38 rows=1126 width=20) (actual time=6.686..6.688 rows=12 loops=1) | |
| | 450 | | Buckets: 2048 Batches: 1 Memory Usage: 17kB | |
| | 451 | | -> Hash Join (cost=555.05..607.38 rows=1126 width=20) (actual time=6.675..6.684 rows=12 loops=1) | |
| | 452 | | Hash Cond: (hg.story_id = s.story_id) | |
| | 453 | | -> Seq Scan on has_genre hg (cost=0.00..32.60 rows=2260 width=8) (actual time=0.015..0.016 rows=15 loops=1) | |
| | 454 | | -> Hash (cost=492.75..492.75 rows=4984 width=20) (actual time=6.643..6.645 rows=4984 loops=1) | |
| | 455 | | Buckets: 8192 Batches: 1 Memory Usage: 318kB | |
| | 456 | | -> Hash Join (cost=242.43..492.75 rows=4984 width=20) (actual time=2.142..5.633 rows=4984 loops=1) | |
| | 457 | | Hash Cond: (s.story_id = st.story_id) | |
| | 458 | | -> Seq Scan on story s (cost=0.00..224.05 rows=10005 width=16) (actual time=0.008..0.854 rows=10005 loops=1) | |
| | 459 | | -> Hash (cost=180.12..180.12 rows=4984 width=4) (actual time=2.115..2.116 rows=4984 loops=1) | |
| | 460 | | Buckets: 8192 Batches: 1 Memory Usage: 240kB | |
| | 461 | | -> Seq Scan on status st (cost=0.00..180.12 rows=4984 width=4) (actual time=0.011..1.402 rows=4984 loops=1)| |
| | 462 | | Filter: ((status)::text = 'published'::text) | |
| | 463 | | Rows Removed by Filter: 5026 | |
| | 464 | | -> Memoize (cost=0.17..0.19 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=30) | |
| | 465 | | Cache Key: s.user_id | |
| | 466 | | Cache Mode: logical | |
| | 467 | | Hits: 27 Misses: 3 Evictions: 0 Overflows: 0 Memory Usage: 1kB | |
| | 468 | | -> Index Only Scan using writer_pkey on writer w (cost=0.15..0.18 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=3) | |
| | 469 | | Index Cond: (user_id = s.user_id) | |
| | 470 | | Heap Fetches: 3 | |
| | 471 | | -> Hash (cost=1.19..1.19 rows=19 width=8) (actual time=0.017..0.017 rows=19 loops=1) | |
| | 472 | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| | 473 | | -> Seq Scan on likes l (cost=0.00..1.19 rows=19 width=8) (actual time=0.009..0.012 rows=19 loops=1) | |
| | 474 | | -> Sort (cost=26.52..27.32 rows=320 width=222) (actual time=0.023..0.024 rows=9 loops=1) | |
| | 475 | | Sort Key: g.genre_id | |
| | 476 | | Sort Method: quicksort Memory: 25kB | |
| | 477 | | -> Seq Scan on genre g (cost=0.00..13.20 rows=320 width=222) (actual time=0.009..0.010 rows=10 loops=1) | |
| | 478 | |Planning Time: 6.014 ms | |
| | 479 | |Execution Time: 26.272 ms | |
| | 480 | }}} |
| | 481 | |
