forked from mtusi2026/mtusi2026lab4
45 lines
2.1 KiB
Plaintext
45 lines
2.1 KiB
Plaintext
# explain: EXPLAIN of the history page (full plans: explain-history.txt)
|
|
# date: 2026-10-22 14:22:45, commit: c942c61 + uncommitted changes
|
|
# command: python.exe student-tests/explain.py
|
|
# exit code: 0, 1.1 s
|
|
|
|
=== Data volume
|
|
=== History index
|
|
=== 1. First page: limit=50, the app asks for 51 rows to know if there is a next page
|
|
Buffers: shared hit=4
|
|
-> Index Scan using messages_history on messages (actual time=0.005..0.008 rows=51 loops=1)
|
|
Buffers: shared hit=4
|
|
Buffers: shared hit=8
|
|
Execution Time: 0.020 ms
|
|
=== 2. Page after the 10000th message by keyset cursor (this is what the app does)
|
|
Buffers: shared hit=5
|
|
-> Index Scan using messages_history on messages (actual time=0.005..0.008 rows=51 loops=1)
|
|
Buffers: shared hit=5
|
|
Execution Time: 0.029 ms
|
|
=== 2b. The same page exactly as the app sends it since lab4: with attachment_ids of every message
|
|
Buffers: shared hit=59
|
|
-> Index Scan using messages_history on messages m (actual time=0.019..0.204 rows=51 loops=1)
|
|
Buffers: shared hit=59
|
|
Buffers: shared hit=54
|
|
-> Bitmap Heap Scan on message_attachments a (actual time=0.000..0.000 rows=0 loops=51)
|
|
Buffers: shared hit=51
|
|
-> Bitmap Index Scan on message_attachments_message_id_attachment_id_key (actual time=0.000..0.000 rows=0 loops=51)
|
|
Buffers: shared hit=51
|
|
Buffers: shared hit=63
|
|
Execution Time: 0.225 ms
|
|
=== 3. The same page with OFFSET 10000 (the app does NOT do this)
|
|
Buffers: shared hit=585
|
|
Buffers: shared hit=585
|
|
-> Seq Scan on messages (actual time=0.005..1.278 rows=15000 loops=1)
|
|
Buffers: shared hit=585
|
|
Execution Time: 3.107 ms
|
|
=== 4. Query 2 without the index (index is dropped only inside this transaction)
|
|
Buffers: shared hit=585
|
|
Buffers: shared hit=585
|
|
-> Seq Scan on messages (actual time=0.678..1.116 rows=5000 loops=1)
|
|
Buffers: shared hit=585
|
|
Buffers: shared hit=4
|
|
Execution Time: 1.450 ms
|
|
=== ROLLBACK: test data removed, index is back
|
|
PASS: full plans saved to evidence\explain-history.txt
|