Run Code  | API  | Code Wall  | Users  | Misc  | Feedback  | Login  | Theme  | Privacy  | Patreon 

Comparison of queries with regard to speed (bitmapor versus UNION vers...

Language: Layout:
absolute service time: 5,72 sec 
edit mode |  history
   QUERY PLAN
1 Sort (cost=420.21..420.71 rows=200 width=48) (actual time=0.615..0.630 rows=236 loops=1)
2 Sort Key: send_date
3 Sort Method: quicksort Memory: 36kB
4 -> Bitmap Heap Scan on messages (cost=10.31..412.56 rows=200 width=48) (actual time=0.099..0.550 rows=236 loops=1)
5 Recheck Cond: ((recipient_id = 123) OR (sender_id = 123))
6 Heap Blocks: exact=189
7 -> BitmapOr (cost=10.31..10.31 rows=200 width=0) (actual time=0.068..0.068 rows=0 loops=1)
8 -> Bitmap Index Scan on messages_recipient_id_idx (cost=0.00..5.04 rows=100 width=0) (actual time=0.036..0.036 rows=131 loops=1)
9 Index Cond: (recipient_id = 123)
10 -> Bitmap Index Scan on messages_pkey (cost=0.00..5.17 rows=100 width=0) (actual time=0.029..0.029 rows=107 loops=1)
11 Index Cond: (sender_id = 123)
12 Planning time: 0.376 ms
13 Execution time: 0.703 ms
   QUERY PLAN
1 Sort (cost=538.87..539.37 rows=200 width=48) (actual time=0.471..0.489 rows=236 loops=1)
2 Sort Key: messages.send_date
3 Sort Method: quicksort Memory: 36kB
4 -> HashAggregate (cost=529.22..531.22 rows=200 width=48) (actual time=0.362..0.392 rows=236 loops=1)
5 Group Key: messages.sender_id, messages.recipient_id, messages.send_date, messages.message_text
6 -> Append (cost=5.07..527.22 rows=200 width=48) (actual time=0.041..0.255 rows=238 loops=1)
7 -> Bitmap Heap Scan on messages (cost=5.07..262.55 rows=100 width=48) (actual time=0.041..0.139 rows=131 loops=1)
8 Recheck Cond: (recipient_id = 123)
9 Heap Blocks: exact=113
10 -> Bitmap Index Scan on messages_recipient_id_idx (cost=0.00..5.04 rows=100 width=0) (actual time=0.024..0.024 rows=131 loops=1)
11 Index Cond: (recipient_id = 123)
12 -> Bitmap Heap Scan on messages messages_1 (cost=5.19..262.67 rows=100 width=48) (actual time=0.042..0.098 rows=107 loops=1)
13 Recheck Cond: (sender_id = 123)
14 Heap Blocks: exact=96
15 -> Bitmap Index Scan on messages_pkey (cost=0.00..5.17 rows=100 width=0) (actual time=0.027..0.027 rows=107 loops=1)
16 Index Cond: (sender_id = 123)
17 Planning time: 0.178 ms
18 Execution time: 0.629 ms
   QUERY PLAN
1 Sort (cost=534.87..535.37 rows=200 width=48) (actual time=0.335..0.352 rows=238 loops=1)
2 Sort Key: messages.send_date
3 Sort Method: quicksort Memory: 36kB
4 -> Append (cost=5.07..527.22 rows=200 width=48) (actual time=0.037..0.274 rows=238 loops=1)
5 -> Bitmap Heap Scan on messages (cost=5.07..262.55 rows=100 width=48) (actual time=0.037..0.108 rows=131 loops=1)
6 Recheck Cond: (recipient_id = 123)
7 Heap Blocks: exact=113
8 -> Bitmap Index Scan on messages_recipient_id_idx (cost=0.00..5.04 rows=100 width=0) (actual time=0.021..0.021 rows=131 loops=1)
9 Index Cond: (recipient_id = 123)
10 -> Bitmap Heap Scan on messages messages_1 (cost=5.19..262.67 rows=100 width=48) (actual time=0.059..0.151 rows=107 loops=1)
11 Recheck Cond: (sender_id = 123)
12 Heap Blocks: exact=96
13 -> Bitmap Index Scan on messages_pkey (cost=0.00..5.17 rows=100 width=0) (actual time=0.044..0.044 rows=107 loops=1)
14 Index Cond: (sender_id = 123)
15 Planning time: 0.166 ms
16 Execution time: 0.399 ms