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

Another comparison of UNION vs OR

Language: Layout:
absolute service time: 6,36 sec 
edit mode |  history
   QUERY PLAN
1 Sort (cost=2123.86..2124.36 rows=200 width=48) (actual time=27.305..27.320 rows=227 loops=1)
2 Sort Key: send_date
3 Sort Method: quicksort Memory: 35kB
4 -> Seq Scan on messages (cost=0.00..2116.22 rows=200 width=48) (actual time=0.108..27.097 rows=227 loops=1)
5 Filter: ((recipient_id = 123) OR (sender_id = 123))
6 Rows Removed by Filter: 103454
7 Planning time: 0.474 ms
8 Execution time: 27.392 ms
   QUERY PLAN
1 Sort (cost=2135.60..2136.10 rows=201 width=48) (actual time=15.716..15.803 rows=227 loops=1)
2 Sort Key: messages.send_date
3 Sort Method: quicksort Memory: 35kB
4 -> HashAggregate (cost=2125.90..2127.91 rows=201 width=48) (actual time=15.571..15.621 rows=227 loops=1)
5 Group Key: messages.sender_id, messages.recipient_id, messages.send_date, messages.message_text
6 -> Append (cost=0.00..2123.89 rows=201 width=48) (actual time=0.061..15.371 rows=227 loops=1)
7 -> Seq Scan on messages (cost=0.00..1857.01 rows=100 width=48) (actual time=0.060..15.092 rows=117 loops=1)
8 Filter: (recipient_id = 123)
9 Rows Removed by Filter: 103564
10 -> Bitmap Heap Scan on messages messages_1 (cost=5.20..264.87 rows=101 width=48) (actual time=0.086..0.248 rows=110 loops=1)
11 Recheck Cond: (sender_id = 123)
12 Heap Blocks: exact=101
13 -> Bitmap Index Scan on messages_pkey (cost=0.00..5.18 rows=101 width=0) (actual time=0.066..0.066 rows=110 loops=1)
14 Index Cond: (sender_id = 123)
15 Planning time: 0.333 ms
16 Execution time: 16.006 ms
   QUERY PLAN
1 Sort (cost=2131.58..2132.08 rows=201 width=48) (actual time=14.847..14.865 rows=227 loops=1)
2 Sort Key: messages.send_date
3 Sort Method: quicksort Memory: 35kB
4 -> Append (cost=0.00..2123.89 rows=201 width=48) (actual time=0.076..14.731 rows=227 loops=1)
5 -> Seq Scan on messages (cost=0.00..1857.01 rows=100 width=48) (actual time=0.076..14.497 rows=117 loops=1)
6 Filter: (recipient_id = 123)
7 Rows Removed by Filter: 103564
8 -> Bitmap Heap Scan on messages messages_1 (cost=5.20..264.87 rows=101 width=48) (actual time=0.082..0.209 rows=110 loops=1)
9 Recheck Cond: (sender_id = 123)
10 Heap Blocks: exact=101
11 -> Bitmap Index Scan on messages_pkey (cost=0.00..5.18 rows=101 width=0) (actual time=0.060..0.060 rows=110 loops=1)
12 Index Cond: (sender_id = 123)
13 Planning time: 0.284 ms
14 Execution time: 14.931 ms