Skip to content

Instantly share code, notes, and snippets.

@bharath-kotha
Created August 7, 2022 06:39
Show Gist options
  • Save bharath-kotha/e529859c22a09e3a57077b9f7d50df9f to your computer and use it in GitHub Desktop.
Save bharath-kotha/e529859c22a09e3a57077b9f7d50df9f to your computer and use it in GitHub Desktop.
How SELECT statement can cause disk write IO - Query Plan 2
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1038449.15..1038460.82 rows=100 width=206) (actual time=23225.822..26199.674 rows=100 loops=1)
Buffers: shared hit=9749 read=201159, temp read=182633 written=195896
-> Gather Merge (cost=1038449.15..2983029.28 rows=16666666 width=206) (actual time=23225.820..26199.659 rows=100 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=9749 read=201159, temp read=182633 written=195896
-> Sort (cost=1037449.13..1058282.46 rows=8333333 width=206) (actual time=23219.185..23219.219 rows=75 loops=3)
Sort Key: book.title, book.id
Sort Method: top-N heapsort Memory: 42kB
Worker 0: Sort Method: top-N heapsort Memory: 42kB
Worker 1: Sort Method: top-N heapsort Memory: 41kB
Buffers: shared hit=9749 read=201159, temp read=182633 written=195896
-> Parallel Hash Join (cost=255098.79..718955.14 rows=8333333 width=206) (actual time=16346.933..21339.523 rows=6666667 loops=3)
Hash Cond: (book.author_id = author.id)
Buffers: shared hit=9679 read=201159, temp read=182633 written=195896
-> Parallel Seq Scan on book (cost=0.00..230392.33 rows=8333333 width=30) (actual time=0.986..6066.543 rows=6666667 loops=3)
Buffers: shared hit=870 read=146189
-> Parallel Hash (cost=105361.13..105361.13 rows=4166613 width=168) (actual time=5283.160..5283.161 rows=3333333 loops=3)
Buckets: 32768 Batches: 1024 Memory Usage: 832kB
Buffers: shared hit=8725 read=54970, temp written=61372
-> Parallel Seq Scan on author (cost=0.00..105361.13 rows=4166613 width=168) (actual time=0.029..2729.765 rows=3333333 loops=3)
Buffers: shared hit=8725 read=54970
Planning Time: 8.556 ms
Execution Time: 26199.972 ms
(24 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment