| | 493 | === After analisys we get: |
| | 494 | {{{ |
| | 495 | ANALYZE genre; |
| | 496 | ANALYZE has_genre; |
| | 497 | ANALYZE status; |
| | 498 | ANALYZE story; |
| | 499 | ANALYZE chapter; |
| | 500 | ANALYZE likes; |
| | 501 | ANALYZE comment; |
| | 502 | }}} |
| | 503 | {{{ |
| | 504 | |QUERY PLAN | |
| | 505 | |----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| |
| | 506 | |Sort (cost=33.89..34.06 rows=70 width=298) (actual time=1.079..1.086 rows=10 loops=1) | |
| | 507 | | Sort Key: (to_char(with_metrics.year, 'YYYY'::text)) DESC, (rank() OVER (?)) | |
| | 508 | | Sort Method: quicksort Memory: 26kB | |
| | 509 | | -> WindowAgg (cost=23.37..31.74 rows=70 width=298) (actual time=1.021..1.045 rows=10 loops=1) | |
| | 510 | | -> Incremental Sort (cost=23.37..27.89 rows=70 width=178) (actual time=1.001..1.008 rows=10 loops=1) | |
| | 511 | | Sort Key: with_metrics.year, with_metrics.total_views DESC | |
| | 512 | | Presorted Key: with_metrics.year | |
| | 513 | | Full-sort Groups: 1 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB | |
| | 514 | | -> WindowAgg (cost=23.34..24.74 rows=70 width=178) (actual time=0.978..0.994 rows=10 loops=1) | |
| | 515 | | -> Sort (cost=23.34..23.52 rows=70 width=170) (actual time=0.975..0.981 rows=10 loops=1) | |
| | 516 | | Sort Key: with_metrics.year, with_metrics.engagement_rate DESC | |
| | 517 | | Sort Method: quicksort Memory: 26kB | |
| | 518 | | -> Subquery Scan on with_metrics (cost=16.82..21.19 rows=70 width=170) (actual time=0.942..0.969 rows=10 loops=1) | |
| | 519 | | -> WindowAgg (cost=16.82..20.49 rows=70 width=182) (actual time=0.940..0.966 rows=10 loops=1) | |
| | 520 | | -> Sort (cost=16.82..16.99 rows=70 width=94) (actual time=0.922..0.928 rows=10 loops=1) | |
| | 521 | | Sort Key: genre_annual.genre_id, genre_annual.year | |
| | 522 | | Sort Method: quicksort Memory: 25kB | |
| | 523 | | -> Subquery Scan on genre_annual (cost=11.17..14.67 rows=70 width=94) (actual time=0.716..0.913 rows=10 loops=1) | |
| | 524 | | -> GroupAggregate (cost=11.17..13.97 rows=70 width=102) (actual time=0.715..0.910 rows=10 loops=1) | |
| | 525 | | Group Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id | |
| | 526 | | -> Sort (cost=11.17..11.35 rows=70 width=48) (actual time=0.633..0.658 rows=354 loops=1) | |
| | 527 | | Sort Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id, s.story_id | |
| | 528 | | Sort Method: quicksort Memory: 48kB | |
| | 529 | | -> Merge Join (cost=5.49..9.03 rows=70 width=48) (actual time=0.183..0.482 rows=354 loops=1) | |
| | 530 | | Merge Cond: (s.story_id = hg.story_id) | |
| | 531 | | -> Merge Left Join (cost=2.76..4339.69 rows=49821 width=38) (actual time=0.132..0.243 rows=119 loops=1) | |
| | 532 | | Merge Cond: (s.story_id = c.story_id) | |
| | 533 | | -> Merge Left Join (cost=2.47..792.53 rows=4984 width=34) (actual time=0.120..0.171 rows=45 loops=1) | |
| | 534 | | Merge Cond: (s.story_id = l.story_id) | |
| | 535 | | -> Merge Left Join (cost=0.88..778.29 rows=4984 width=30) (actual time=0.090..0.126 rows=11 loops=1) | |
| | 536 | | Merge Cond: (s.story_id = ch.story_id) | |
| | 537 | | -> Nested Loop (cost=0.73..749.17 rows=4984 width=20) (actual time=0.072..0.095 rows=5 loops=1) | |
| | 538 | | -> Merge Join (cost=0.57..623.72 rows=4984 width=20) (actual time=0.053..0.061 rows=5 loops=1) | |
| | 539 | | Merge Cond: (s.story_id = st.story_id) | |
| | 540 | | -> Index Scan using story_pkey on story s (cost=0.29..397.36 rows=10005 width=16) (actual time=0.021..0.023 rows=6 loops=1) | |
| | 541 | | -> Index Only Scan using idx_status_story_published on status st (cost=0.28..139.04 rows=4984 width=4) (actual time=0.028..0.029 rows=5 loops=1)| |
| | 542 | | Heap Fetches: 0 | |
| | 543 | | -> Memoize (cost=0.17..0.19 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=5) | |
| | 544 | | Cache Key: s.user_id | |
| | 545 | | Cache Mode: logical | |
| | 546 | | Hits: 1 Misses: 4 Evictions: 0 Overflows: 0 Memory Usage: 1kB | |
| | 547 | | -> Index Only Scan using writer_pkey on writer w (cost=0.15..0.18 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=4) | |
| | 548 | | Index Cond: (user_id = s.user_id) | |
| | 549 | | Heap Fetches: 4 | |
| | 550 | | -> Index Scan using idx_chapter_story_id on chapter ch (cost=0.14..15.83 rows=112 width=14) (actual time=0.016..0.021 rows=12 loops=1) | |
| | 551 | | -> Sort (cost=1.59..1.64 rows=19 width=8) (actual time=0.028..0.032 rows=46 loops=1) | |
| | 552 | | Sort Key: l.story_id | |
| | 553 | | Sort Method: quicksort Memory: 25kB | |
| | 554 | | -> Seq Scan on likes l (cost=0.00..1.19 rows=19 width=8) (actual time=0.014..0.018 rows=19 loops=1) | |
| | 555 | | -> Index Scan using idx_comment_story_id on comment c (cost=0.29..2786.46 rows=100011 width=8) (actual time=0.011..0.039 rows=120 loops=1) | |
| | 556 | | -> Sort (cost=2.73..2.77 rows=15 width=18) (actual time=0.042..0.063 rows=357 loops=1) | |
| | 557 | | Sort Key: hg.story_id | |
| | 558 | | Sort Method: quicksort Memory: 25kB | |
| | 559 | | -> Hash Join (cost=1.23..2.44 rows=15 width=18) (actual time=0.028..0.035 rows=15 loops=1) | |
| | 560 | | Hash Cond: (hg.genre_id = g.genre_id) | |
| | 561 | | -> Seq Scan on has_genre hg (cost=0.00..1.15 rows=15 width=8) (actual time=0.006..0.007 rows=15 loops=1) | |
| | 562 | | -> Hash (cost=1.10..1.10 rows=10 width=14) (actual time=0.012..0.013 rows=10 loops=1) | |
| | 563 | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| | 564 | | -> Seq Scan on genre g (cost=0.00..1.10 rows=10 width=14) (actual time=0.007..0.008 rows=10 loops=1) | |
| | 565 | |Planning Time: 6.187 ms | |
| | 566 | |Execution Time: 1.321 ms | |
| | 567 | }}} |
