Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- EXPLAIN ANALYZE with
- matches as
- (select forum_id,thread_id,post_id from documents where document @@ to_tsquery('hello'))
- select forum_id,thread_id,post_id,bool_or(is_match)
- from (select *, true as is_match from matches
- union all
- select n.*, false
- from matches m,
- lateral ((select forum_id,thread_id,post_id from documents d1
- where (d1.forum_id,d1.thread_id)=(m.forum_id,m.thread_id)
- and d1.post_id < m.post_id
- order by d1.post_id desc limit 10)
- union all
- (select forum_id,thread_id,post_id from documents d2
- where (d2.forum_id,d2.thread_id)=(m.forum_id,m.thread_id)
- and d2.post_id > m.post_id
- order by d2.post_id asc limit 10)) n) s1
- group by forum_id,thread_id,post_id
- order by forum_id, thread_id, post_id;
- QUERY PLAN
- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Sort (cost=42489.83..42492.35 rows=1008 width=13) (actual time=5.988..6.084 rows=1219 loops=1)
- Sort Key: matches.forum_id, matches.thread_id, matches.post_id
- Sort Method: quicksort Memory: 106kB
- CTE matches
- -> Bitmap Heap Scan on documents (cost=19.97..1774.08 rows=480 width=12) (actual time=0.060..0.142 rows=85 loops=1)
- Recheck Cond: (document @@ to_tsquery('hello'::text))
- Heap Blocks: exact=71
- -> Bitmap Index Scan on documents_document_idx (cost=0.00..19.85 rows=480 width=0) (actual time=0.044..0.044 rows=85 loops=1)
- Index Cond: (document @@ to_tsquery('hello'::text))
- -> HashAggregate (cost=40655.39..40665.47 rows=1008 width=13) (actual time=5.111..5.336 rows=1219 loops=1)
- Group Key: matches.forum_id, matches.thread_id, matches.post_id
- -> Append (cost=0.00..40554.59 rows=10080 width=13) (actual time=0.063..4.327 rows=1697 loops=1)
- -> CTE Scan on matches (cost=0.00..9.60 rows=480 width=13) (actual time=0.063..0.196 rows=85 loops=1)
- -> Nested Loop (cost=0.42..40448.99 rows=9600 width=12) (actual time=0.018..3.902 rows=1612 loops=1)
- -> CTE Scan on matches m (cost=0.00..9.60 rows=480 width=12) (actual time=0.001..0.023 rows=85 loops=1)
- -> Append (cost=0.42..84.05 rows=20 width=12) (actual time=0.015..0.042 rows=19 loops=85)
- -> Limit (cost=0.42..41.92 rows=10 width=12) (actual time=0.015..0.020 rows=9 loops=85)
- -> Index Only Scan Backward using documents_forum_id_thread_id_post_id_idx on documents d1 (cost=0.42..58.53 rows=14 width=12) (actual time=0.014..0.018 rows=9 loops=85)
- Index Cond: ((forum_id = m.forum_id) AND (thread_id = m.thread_id) AND (post_id < m.post_id))
- Heap Fetches: 802
- -> Limit (cost=0.42..41.92 rows=10 width=12) (actual time=0.012..0.019 rows=10 loops=85)
- -> Index Only Scan using documents_forum_id_thread_id_post_id_idx on documents d2 (cost=0.42..58.53 rows=14 width=12) (actual time=0.012..0.017 rows=10 loops=85)
- Index Cond: ((forum_id = m.forum_id) AND (thread_id = m.thread_id) AND (post_id > m.post_id))
- Heap Fetches: 810
- Planning time: 0.490 ms
- Execution time: 6.248 ms
- (26 rows)
- =# EXPLAIN ANALYZE with
- matches as
- (select * from documents where document @@ to_tsquery('hello'))
- select *, true as is_match from matches
- union
- select n.*, false
- from matches m,
- lateral ((select * from documents d1
- where (d1.forum_id,d1.thread_id)=(m.forum_id,m.thread_id)
- and d1.post_id < m.post_id
- order by d1.post_id desc limit 10)
- union all
- (select * from documents d2
- where (d2.forum_id,d2.thread_id)=(m.forum_id,m.thread_id)
- and d2.post_id > m.post_id
- order by d2.post_id asc limit 10)) n
- order by forum_id, thread_id, post_id;
- QUERY PLAN
- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Unique (cost=43003.75..43154.95 rows=10080 width=541) (actual time=6.200..7.650 rows=1265 loops=1)
- CTE matches
- -> Bitmap Heap Scan on documents (cost=19.97..1774.08 rows=480 width=566) (actual time=0.059..0.129 rows=85 loops=1)
- Recheck Cond: (document @@ to_tsquery('hello'::text))
- Heap Blocks: exact=71
- -> Bitmap Index Scan on documents_document_idx (cost=0.00..19.85 rows=480 width=0) (actual time=0.044..0.044 rows=85 loops=1)
- Index Cond: (document @@ to_tsquery('hello'::text))
- -> Sort (cost=41229.67..41254.87 rows=10080 width=541) (actual time=6.199..6.303 rows=1697 loops=1)
- Sort Key: matches.forum_id, matches.thread_id, matches.post_id, matches.document, (true)
- Sort Method: quicksort Memory: 1502kB
- -> Append (cost=0.00..40559.39 rows=10080 width=541) (actual time=0.063..4.025 rows=1697 loops=1)
- -> CTE Scan on matches (cost=0.00..9.60 rows=480 width=44) (actual time=0.063..0.175 rows=85 loops=1)
- -> Nested Loop (cost=0.42..40448.99 rows=9600 width=566) (actual time=0.019..3.629 rows=1612 loops=1)
- -> CTE Scan on matches m (cost=0.00..9.60 rows=480 width=12) (actual time=0.000..0.027 rows=85 loops=1)
- -> Append (cost=0.42..84.05 rows=20 width=566) (actual time=0.014..0.037 rows=19 loops=85)
- -> Limit (cost=0.42..41.92 rows=10 width=566) (actual time=0.013..0.018 rows=9 loops=85)
- -> Index Scan Backward using documents_forum_id_thread_id_post_id_idx on documents d1 (cost=0.42..58.53 rows=14 width=566) (actual time=0.013..0.016 rows=9 loops=85)
- Index Cond: ((forum_id = m.forum_id) AND (thread_id = m.thread_id) AND (post_id < m.post_id))
- -> Limit (cost=0.42..41.92 rows=10 width=566) (actual time=0.011..0.017 rows=10 loops=85)
- -> Index Scan using documents_forum_id_thread_id_post_id_idx on documents d2 (cost=0.42..58.53 rows=14 width=566) (actual time=0.011..0.015 rows=10 loops=85)
- Index Cond: ((forum_id = m.forum_id) AND (thread_id = m.thread_id) AND (post_id > m.post_id))
- Planning time: 0.435 ms
- Execution time: 7.792 ms
- (23 rows)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
