| 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 | | }}} |
| | 409 | |QUERY PLAN | |
| | 410 | |----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| |
| | 411 | |Sort (cost=188601.52..189051.70 rows=180072 width=296) (actual time=735.924..735.941 rows=19 loops=1) | |
| | 412 | | Sort Key: (to_char(with_metrics.year, 'YYYY'::text)) DESC, (rank() OVER (?)) | |
| | 413 | | Sort Method: quicksort Memory: 27kB | |
| | 414 | | -> WindowAgg (cost=123725.89..148260.34 rows=180072 width=296) (actual time=735.837..735.903 rows=19 loops=1) | |
| | 415 | | -> Incremental Sort (cost=123725.80..138356.38 rows=180072 width=176) (actual time=735.816..735.833 rows=19 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: 27kB Peak Memory: 27kB | |
| | 419 | | -> WindowAgg (cost=123663.61..127265.03 rows=180072 width=176) (actual time=735.768..735.813 rows=19 loops=1) | |
| | 420 | | -> Sort (cost=123663.59..124113.77 rows=180072 width=168) (actual time=735.756..735.773 rows=19 loops=1) | |
| | 421 | | Sort Key: with_metrics.year, with_metrics.engagement_rate DESC | |
| | 422 | | Sort Method: quicksort Memory: 27kB | |
| | 423 | | -> Subquery Scan on with_metrics (cost=81917.02..93171.41 rows=180072 width=168) (actual time=735.680..735.750 rows=19 loops=1) | |
| | 424 | | -> WindowAgg (cost=81917.02..91370.69 rows=180072 width=180) (actual time=735.674..735.739 rows=19 loops=1) | |
| | 425 | | -> Sort (cost=81916.91..82367.09 rows=180072 width=92) (actual time=735.611..735.627 rows=19 loops=1) | |
| | 426 | | Sort Key: genre_annual.genre_id, genre_annual.year | |
| | 427 | | Sort Method: quicksort Memory: 26kB | |
| | 428 | | -> Subquery Scan on genre_annual (cost=45259.58..56965.23 rows=180072 width=92) (actual time=474.888..735.603 rows=19 loops=1) | |
| | 429 | | -> GroupAggregate (cost=45259.58..55164.51 rows=180072 width=100) (actual time=474.881..735.582 rows=19 loops=1) | |
| | 430 | | Group Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id | |
| | 431 | | -> Sort (cost=45259.58..46009.99 rows=300163 width=46) (actual time=474.740..592.149 rows=330994 loops=1) | |
| | 432 | | Sort Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id, s.story_id | |
| | 433 | | Sort Method: external merge Disk: 17832kB | |
| | 434 | | -> Hash Right Join (cost=2402.96..8715.17 rows=300163 width=46) (actual time=104.929..272.256 rows=330994 loops=1) | |
| | 435 | | Hash Cond: (c.story_id = s.story_id) | |
| | 436 | | -> Seq Scan on comment c (cost=0.00..1935.11 rows=100011 width=8) (actual time=0.008..8.474 rows=100011 loops=1) | |
| | 437 | | -> Hash (cost=2027.61..2027.61 rows=30028 width=42) (actual time=104.842..104.854 rows=30561 loops=1) | |
| | 438 | | Buckets: 32768 Batches: 1 Memory Usage: 2253kB | |
| | 439 | | -> Hash Join (cost=1084.06..2027.61 rows=30028 width=42) (actual time=76.783..95.747 rows=30561 loops=1) | |
| | 440 | | Hash Cond: (hg.genre_id = g.genre_id) | |
| | 441 | | -> Hash Join (cost=1082.66..1928.82 rows=30028 width=34) (actual time=16.868..29.358 rows=30561 loops=1) | |
| | 442 | | Hash Cond: (hg.story_id = s.story_id) | |
| | 443 | | -> Seq Scan on has_genre hg (cost=0.00..433.28 rows=30028 width=8) (actual time=0.012..2.278 rows=30028 loops=1) | |
| | 444 | | -> Hash (cost=957.59..957.59 rows=10005 width=34) (actual time=16.819..16.828 rows=10145 loops=1) | |
| | 445 | | Buckets: 16384 Batches: 1 Memory Usage: 645kB | |
| | 446 | | -> Hash Left Join (cost=812.87..957.59 rows=10005 width=34) (actual time=7.357..14.471 rows=10145 loops=1) | |
| | 447 | | Hash Cond: (s.story_id = l.story_id) | |
| | 448 | | -> Hash Join (cost=811.44..868.43 rows=10005 width=30) (actual time=7.310..12.546 rows=10111 loops=1) | |
| | 449 | | Hash Cond: (s.story_id = st.story_id) | |
| | 450 | | -> Hash Join (cost=416.49..447.21 rows=10005 width=26) (actual time=3.835..7.095 rows=10111 loops=1) | |
| | 451 | | Hash Cond: (s.user_id = w.user_id) | |
| | 452 | | -> Hash Right Join (cost=349.11..353.53 rows=10005 width=26) (actual time=3.787..5.295 rows=10111 loops=1) | |
| | 453 | | Hash Cond: (ch.story_id = s.story_id) | |
| | 454 | | -> Seq Scan on chapter ch (cost=0.00..4.12 rows=112 width=14) (actual time=0.011..0.028 rows=112 loops=1) | |
| | 455 | | -> Hash (cost=224.05..224.05 rows=10005 width=16) (actual time=3.728..3.729 rows=10005 loops=1) | |
| | 456 | | Buckets: 16384 Batches: 1 Memory Usage: 597kB | |
| | 457 | | -> Seq Scan on story s (cost=0.00..224.05 rows=10005 width=16) (actual time=0.018..1.867 rows=10005 loops=1)| |
| | 458 | | -> Hash (cost=35.50..35.50 rows=2550 width=4) (actual time=0.027..0.028 rows=5 loops=1) | |
| | 459 | | Buckets: 4096 Batches: 1 Memory Usage: 33kB | |
| | 460 | | -> Seq Scan on writer w (cost=0.00..35.50 rows=2550 width=4) (actual time=0.011..0.013 rows=5 loops=1) | |
| | 461 | | -> Hash (cost=269.89..269.89 rows=10005 width=4) (actual time=3.443..3.443 rows=10005 loops=1) | |
| | 462 | | Buckets: 16384 Batches: 1 Memory Usage: 480kB | |
| | 463 | | -> Seq Scan on status st (cost=0.00..269.89 rows=10005 width=4) (actual time=0.031..2.005 rows=10005 loops=1) | |
| | 464 | | Filter: ((status)::text = 'published'::text) | |
| | 465 | | Rows Removed by Filter: 5026 | |
| | 466 | | -> Hash (cost=1.19..1.19 rows=19 width=8) (actual time=0.025..0.026 rows=19 loops=1) | |
| | 467 | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| | 468 | | -> Seq Scan on likes l (cost=0.00..1.19 rows=19 width=8) (actual time=0.013..0.016 rows=19 loops=1) | |
| | 469 | | -> Hash (cost=1.18..1.18 rows=18 width=12) (actual time=59.895..59.896 rows=18 loops=1) | |
| | 470 | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| | 471 | | -> Seq Scan on genre g (cost=0.00..1.18 rows=18 width=12) (actual time=0.017..0.020 rows=18 loops=1) | |
| | 472 | |Planning Time: 8.265 ms | |
| | 473 | |JIT: | |
| | 474 | | Functions: 84 | |
| | 475 | | Options: Inlining false, Optimization false, Expressions true, Deforming true | |
| | 476 | | Timing: Generation 5.934 ms (Deform 3.054 ms), Inlining 0.000 ms, Optimization 2.243 ms, Emission 57.903 ms, Total 66.080 ms | |
| | 477 | |Execution Time: 747.006 ms | |
| | 478 | |
| | 479 | }}} |
| | 480 | Average time without indexes is: 749 ms |
| 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 | | }}} |
| | 504 | |QUERY PLAN | |
| | 505 | |----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| |
| | 506 | |Sort (cost=188601.52..189051.70 rows=180072 width=296) (actual time=731.664..731.683 rows=19 loops=1) | |
| | 507 | | Sort Key: (to_char(with_metrics.year, 'YYYY'::text)) DESC, (rank() OVER (?)) | |
| | 508 | | Sort Method: quicksort Memory: 27kB | |
| | 509 | | -> WindowAgg (cost=123725.89..148260.34 rows=180072 width=296) (actual time=731.578..731.645 rows=19 loops=1) | |
| | 510 | | -> Incremental Sort (cost=123725.80..138356.38 rows=180072 width=176) (actual time=731.558..731.576 rows=19 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: 27kB Peak Memory: 27kB | |
| | 514 | | -> WindowAgg (cost=123663.61..127265.03 rows=180072 width=176) (actual time=731.509..731.556 rows=19 loops=1) | |
| | 515 | | -> Sort (cost=123663.59..124113.77 rows=180072 width=168) (actual time=731.498..731.515 rows=19 loops=1) | |
| | 516 | | Sort Key: with_metrics.year, with_metrics.engagement_rate DESC | |
| | 517 | | Sort Method: quicksort Memory: 27kB | |
| | 518 | | -> Subquery Scan on with_metrics (cost=81917.02..93171.41 rows=180072 width=168) (actual time=731.423..731.493 rows=19 loops=1) | |
| | 519 | | -> WindowAgg (cost=81917.02..91370.69 rows=180072 width=180) (actual time=731.416..731.482 rows=19 loops=1) | |
| | 520 | | -> Sort (cost=81916.91..82367.09 rows=180072 width=92) (actual time=731.353..731.369 rows=19 loops=1) | |
| | 521 | | Sort Key: genre_annual.genre_id, genre_annual.year | |
| | 522 | | Sort Method: quicksort Memory: 26kB | |
| | 523 | | -> Subquery Scan on genre_annual (cost=45259.58..56965.23 rows=180072 width=92) (actual time=470.799..731.345 rows=19 loops=1) | |
| | 524 | | -> GroupAggregate (cost=45259.58..55164.51 rows=180072 width=100) (actual time=470.793..731.331 rows=19 loops=1) | |
| | 525 | | Group Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id | |
| | 526 | | -> Sort (cost=45259.58..46009.99 rows=300163 width=46) (actual time=470.650..587.960 rows=330994 loops=1) | |
| | 527 | | Sort Key: (date_trunc('year'::text, s.story_created_at)), g.genre_id, s.story_id | |
| | 528 | | Sort Method: external merge Disk: 17832kB | |
| | 529 | | -> Hash Right Join (cost=2402.96..8715.17 rows=300163 width=46) (actual time=102.927..268.242 rows=330994 loops=1) | |
| | 530 | | Hash Cond: (c.story_id = s.story_id) | |
| | 531 | | -> Seq Scan on comment c (cost=0.00..1935.11 rows=100011 width=8) (actual time=0.008..8.499 rows=100011 loops=1) | |
| | 532 | | -> Hash (cost=2027.61..2027.61 rows=30028 width=42) (actual time=102.841..102.853 rows=30561 loops=1) | |
| | 533 | | Buckets: 32768 Batches: 1 Memory Usage: 2253kB | |
| | 534 | | -> Hash Join (cost=1084.06..2027.61 rows=30028 width=42) (actual time=74.863..93.838 rows=30561 loops=1) | |
| | 535 | | Hash Cond: (hg.genre_id = g.genre_id) | |
| | 536 | | -> Hash Join (cost=1082.66..1928.82 rows=30028 width=34) (actual time=16.771..29.144 rows=30561 loops=1) | |
| | 537 | | Hash Cond: (hg.story_id = s.story_id) | |
| | 538 | | -> Seq Scan on has_genre hg (cost=0.00..433.28 rows=30028 width=8) (actual time=0.011..2.336 rows=30028 loops=1) | |
| | 539 | | -> Hash (cost=957.59..957.59 rows=10005 width=34) (actual time=16.724..16.733 rows=10145 loops=1) | |
| | 540 | | Buckets: 16384 Batches: 1 Memory Usage: 645kB | |
| | 541 | | -> Hash Left Join (cost=812.87..957.59 rows=10005 width=34) (actual time=7.297..14.431 rows=10145 loops=1) | |
| | 542 | | Hash Cond: (s.story_id = l.story_id) | |
| | 543 | | -> Hash Join (cost=811.44..868.43 rows=10005 width=30) (actual time=7.250..12.492 rows=10111 loops=1) | |
| | 544 | | Hash Cond: (s.story_id = st.story_id) | |
| | 545 | | -> Hash Join (cost=416.49..447.21 rows=10005 width=26) (actual time=3.788..7.039 rows=10111 loops=1) | |
| | 546 | | Hash Cond: (s.user_id = w.user_id) | |
| | 547 | | -> Hash Right Join (cost=349.11..353.53 rows=10005 width=26) (actual time=3.743..5.203 rows=10111 loops=1) | |
| | 548 | | Hash Cond: (ch.story_id = s.story_id) | |
| | 549 | | -> Seq Scan on chapter ch (cost=0.00..4.12 rows=112 width=14) (actual time=0.011..0.028 rows=112 loops=1) | |
| | 550 | | -> Hash (cost=224.05..224.05 rows=10005 width=16) (actual time=3.685..3.686 rows=10005 loops=1) | |
| | 551 | | Buckets: 16384 Batches: 1 Memory Usage: 597kB | |
| | 552 | | -> Seq Scan on story s (cost=0.00..224.05 rows=10005 width=16) (actual time=0.017..1.835 rows=10005 loops=1)| |
| | 553 | | -> Hash (cost=35.50..35.50 rows=2550 width=4) (actual time=0.024..0.026 rows=5 loops=1) | |
| | 554 | | Buckets: 4096 Batches: 1 Memory Usage: 33kB | |
| | 555 | | -> Seq Scan on writer w (cost=0.00..35.50 rows=2550 width=4) (actual time=0.010..0.012 rows=5 loops=1) | |
| | 556 | | -> Hash (cost=269.89..269.89 rows=10005 width=4) (actual time=3.426..3.427 rows=10005 loops=1) | |
| | 557 | | Buckets: 16384 Batches: 1 Memory Usage: 480kB | |
| | 558 | | -> Seq Scan on status st (cost=0.00..269.89 rows=10005 width=4) (actual time=0.029..1.999 rows=10005 loops=1) | |
| | 559 | | Filter: ((status)::text = 'published'::text) | |
| | 560 | | Rows Removed by Filter: 5026 | |
| | 561 | | -> Hash (cost=1.19..1.19 rows=19 width=8) (actual time=0.025..0.026 rows=19 loops=1) | |
| | 562 | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| | 563 | | -> Seq Scan on likes l (cost=0.00..1.19 rows=19 width=8) (actual time=0.012..0.016 rows=19 loops=1) | |
| | 564 | | -> Hash (cost=1.18..1.18 rows=18 width=12) (actual time=58.071..58.072 rows=18 loops=1) | |
| | 565 | | Buckets: 1024 Batches: 1 Memory Usage: 9kB | |
| | 566 | | -> Seq Scan on genre g (cost=0.00..1.18 rows=18 width=12) (actual time=0.015..0.018 rows=18 loops=1) | |
| | 567 | |Planning Time: 8.416 ms | |
| | 568 | |JIT: | |
| | 569 | | Functions: 84 | |
| | 570 | | Options: Inlining false, Optimization false, Expressions true, Deforming true | |
| | 571 | | Timing: Generation 5.938 ms (Deform 3.089 ms), Inlining 0.000 ms, Optimization 2.169 ms, Emission 56.154 ms, Total 64.260 ms | |
| | 572 | |Execution Time: 742.778 ms | |
| | 573 | }}} |
| | 574 | Average time: 746.354 ms |
| | 575 | }}} |
| | 576 | We conclude that the indexes added for this query provided no meaningful improvement, dropping from 748 ms to 746 ms (~0.We conclude that the indexes added for this query provided no meaningful improvement, dropping from 748 ms to 746 ms (~0.3%), which is within normal measurement variance. The primary bottleneck is the sort operation spilling to disk (external merge, 17.8 MB), which cannot be resolved through indexing. The indexes are therefore not kept, as they add write overhead without any measurable read benefit. |
