Created
December 25, 2015 10:21
-
-
Save vadv/355a0d191482fdbada62 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
no-lock patch: | |
pgbench=# explain (analyze,buffers) select * from big_table b inner join small_table s on b.i = s.i where b.date > '2015-11-30 15:25:00' and b.i = 1000001; | |
QUERY PLAN | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |
Nested Loop (cost=0.00..16.94 rows=4 width=16) (actual time=0.057..0.060 rows=1 loops=1) | |
Buffers: shared hit=8 | |
-> Append (cost=0.00..8.45 rows=2 width=12) (actual time=0.024..0.025 rows=1 loops=1) | |
Buffers: shared hit=4 | |
-> Seq Scan on big_table b (cost=0.00..0.00 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=1) | |
Filter: ((date > '2015-11-30 15:25:00'::timestamp without time zone) AND (i = 1000001)) | |
-> Index Scan using idx_big_table_part_1 on big_table_part_1 b_1 (cost=0.42..8.45 rows=1 width=12) (actual time=0.019..0.020 rows=1 loops=1) | |
Index Cond: (i = 1000001) | |
Filter: (date > '2015-11-30 15:25:00'::timestamp without time zone) | |
Buffers: shared hit=4 | |
-> Materialize (cost=0.00..8.45 rows=2 width=4) (actual time=0.031..0.032 rows=1 loops=1) | |
Buffers: shared hit=4 | |
-> Append (cost=0.00..8.44 rows=2 width=4) (actual time=0.018..0.019 rows=1 loops=1) | |
Buffers: shared hit=4 | |
-> Seq Scan on small_table s (cost=0.00..0.00 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1) | |
Filter: (i = 1000001) | |
-> Index Only Scan using idx_small_table_part_3 on small_table_part_3 s_1 (cost=0.42..8.44 rows=1 width=4) (actual time=0.016..0.017 rows=1 loops=1) | |
Index Cond: (i = 1000001) | |
Heap Fetches: 1 | |
Buffers: shared hit=4 | |
Planning time: 12.275 ms | |
Execution time: 0.160 ms | |
(22 rows) | |
vanilla: | |
pgbench=# explain (analyze,buffers) select * from big_table b inner join small_table s on b.i = s.i where b.date > '2015-11-30 15:25:00' and b.i = 1000001; | |
QUERY PLAN | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |
Nested Loop (cost=0.00..16.94 rows=4 width=16) (actual time=0.025..0.026 rows=1 loops=1) | |
Buffers: shared hit=8 | |
-> Append (cost=0.00..8.45 rows=2 width=12) (actual time=0.013..0.013 rows=1 loops=1) | |
Buffers: shared hit=4 | |
-> Seq Scan on big_table b (cost=0.00..0.00 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=1) | |
Filter: ((date > '2015-11-30 15:25:00'::timestamp without time zone) AND (i = 1000001)) | |
-> Index Scan using idx_big_table_part_1 on big_table_part_1 b_1 (cost=0.42..8.45 rows=1 width=12) (actual time=0.010..0.010 rows=1 loops=1) | |
Index Cond: (i = 1000001) | |
Filter: (date > '2015-11-30 15:25:00'::timestamp without time zone) | |
Buffers: shared hit=4 | |
-> Materialize (cost=0.00..8.45 rows=2 width=4) (actual time=0.011..0.011 rows=1 loops=1) | |
Buffers: shared hit=4 | |
-> Append (cost=0.00..8.44 rows=2 width=4) (actual time=0.007..0.007 rows=1 loops=1) | |
Buffers: shared hit=4 | |
-> Seq Scan on small_table s (cost=0.00..0.00 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1) | |
Filter: (i = 1000001) | |
-> Index Only Scan using idx_small_table_part_3 on small_table_part_3 s_1 (cost=0.42..8.44 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1) | |
Index Cond: (i = 1000001) | |
Heap Fetches: 1 | |
Buffers: shared hit=4 | |
Planning time: 36.212 ms | |
Execution time: 0.089 ms | |
(22 rows) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment