Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
create table foo(bar integer not null, baz integer not null, qux text);
insert into foo(bar, baz, qux) select random()*10, random()*10, 'some much much much much much much much longer random text '||g from generate_series(1,10000) g;
create index bar_baz on foo(bar, baz);
postgres=> explain (buffers, analyze, verbose) select max(qux) from foo where bar>-1000 and baz=0;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=120.64..120.65 rows=1 width=32) (actual time=3.051..3.051 rows=1 loops=1)
Output: max(qux)
Buffers: shared hit=119 read=30
-> Bitmap Heap Scan on stack.foo (cost=73.59..120.60 rows=17 width=32) (actual time=1.400..1.771 rows=520 loops=1)
Output: bar, baz, qux
Recheck Cond: ((foo.bar > (-1000)) AND (foo.baz = 0))
Buffers: shared hit=119 read=30
-> Bitmap Index Scan on bar_baz (cost=0.00..73.58 rows=17 width=0) (actual time=1.368..1.368 rows=520 loops=1)
Index Cond: ((foo.bar > (-1000)) AND (foo.baz = 0))
Buffers: shared read=30
Total runtime: 3.096 ms
(11 rows)
postgres=> explain (buffers, analyze, verbose) select max(qux) from foo where bar>-1000 and baz=0;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=120.64..120.65 rows=1 width=32) (actual time=2.960..2.960 rows=1 loops=1)
Output: max(qux)
Buffers: shared hit=148
-> Bitmap Heap Scan on stack.foo (cost=73.59..120.60 rows=17 width=32) (actual time=1.319..1.666 rows=520 loops=1)
Output: bar, baz, qux
Recheck Cond: ((foo.bar > (-1000)) AND (foo.baz = 0))
Buffers: shared hit=148
-> Bitmap Index Scan on bar_baz (cost=0.00..73.58 rows=17 width=0) (actual time=1.289..1.289 rows=520 loops=1)
Index Cond: ((foo.bar > (-1000)) AND (foo.baz = 0))
Buffers: shared hit=29
Total runtime: 3.000 ms
(11 rows)
postgres=> cluster foo using bar_baz;
CLUSTER
postgres=> explain (buffers, analyze, verbose) select max(qux) from foo where bar>-1000 and baz=0;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=120.64..120.65 rows=1 width=32) (actual time=2.930..2.930 rows=1 loops=1)
Output: max(qux)
Buffers: shared hit=19 read=30
-> Bitmap Heap Scan on stack.foo (cost=73.59..120.60 rows=17 width=32) (actual time=1.452..1.664 rows=520 loops=1)
Output: bar, baz, qux
Recheck Cond: ((foo.bar > (-1000)) AND (foo.baz = 0))
Buffers: shared hit=19 read=30
-> Bitmap Index Scan on bar_baz (cost=0.00..73.58 rows=17 width=0) (actual time=1.440..1.440 rows=520 loops=1)
Index Cond: ((foo.bar > (-1000)) AND (foo.baz = 0))
Buffers: shared read=30
Total runtime: 2.976 ms
(11 rows)
postgres=> explain (buffers, analyze, verbose) select max(qux) from foo where bar>-1000 and baz=0;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=120.64..120.65 rows=1 width=32) (actual time=2.798..2.799 rows=1 loops=1)
Output: max(qux)
Buffers: shared hit=48
-> Bitmap Heap Scan on stack.foo (cost=73.59..120.60 rows=17 width=32) (actual time=1.314..1.517 rows=520 loops=1)
Output: bar, baz, qux
Recheck Cond: ((foo.bar > (-1000)) AND (foo.baz = 0))
Buffers: shared hit=48
-> Bitmap Index Scan on bar_baz (cost=0.00..73.58 rows=17 width=0) (actual time=1.303..1.303 rows=520 loops=1)
Index Cond: ((foo.bar > (-1000)) AND (foo.baz = 0))
Buffers: shared hit=29
Total runtime: 2.845 ms
(11 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.