Here is a equivalent script for postgres that shows good index use:
create table t (
pk bigint not null
);
insert into t values (1);
insert into t select pk from t;
insert into t select pk from t;
insert into t select pk from t;
insert into t select pk from t;
insert into t select pk from t;
insert into t select pk from t;
insert into t select pk from t;
insert into t select pk from t;
insert into t select pk from t;
insert into t select pk from t;
insert into t select pk from t;
insert into t select pk from t;
insert into t select pk from t;
insert into t select pk from t;
insert into t select pk from t;
insert into t select pk from t;
insert into t select pk from t;
insert into t select pk from t;
insert into t select pk from t;
insert into t select pk from t;
create sequence s;
update t set pk = nextval('s');
vacuum full t;
alter table t add primary key (pk);
analyze t;
explain SELECT pk FROM t
WHERE pk = 1;
explain SELECT pk FROM t
WHERE pk IN (1, 1000000);
explain SELECT pk FROM t
WHERE pk = 1 OR pk = 1000000;
explain SELECT pk FROM t
WHERE pk = 1 AND pk = 1000000; -- odd query!
explain SELECT count(pk) FROM t
WHERE pk = 1 AND pk > 900000;
\timing
SELECT pk FROM t WHERE pk = 1;
SELECT pk FROM t WHERE pk IN (1, 1000000);
SELECT pk FROM t WHERE pk = 1 OR pk = 1000000;
SELECT pk FROM t WHERE pk = 1 AND pk = 1000000; -- odd query!
SELECT count(pk) FROM t WHERE pk = 1 AND pk > 900000;
\timing
drop table t;
drop sequence s;
The plans postgres comes up with are:
Index Scan using t_pkey on t (cost=0.00..8.38 rows=1 width=8)
Index Cond: (pk = 1)
-- 1.290 ms
Bitmap Heap Scan on t (cost=8.63..16.53 rows=2 width=8)
Recheck Cond: (pk = ANY ('{1,1000000}'::bigint[]))
-> Bitmap Index Scan on t_pkey (cost=0.00..8.63 rows=2 width=0)
Index Cond: (pk = ANY ('{1,1000000}'::bigint[]))
-- 1.675 ms
Bitmap Heap Scan on t (cost=8.75..16.65 rows=2 width=8)
Recheck Cond: ((pk = 1) OR (pk = 1000000))
-> BitmapOr (cost=8.75..8.75 rows=2 width=0)
-> Bitmap Index Scan on t_pkey (cost=0.00..4.37 rows=1
width=0)
Index Cond: (pk = 1)
-> Bitmap Index Scan on t_pkey (cost=0.00..4.37 rows=1
width=0)
Index Cond: (pk = 1000000)
-- 1.308 ms
Result (cost=0.00..8.38 rows=1 width=8)
One-Time Filter: false
-> Index Scan using t_pkey on t (cost=0.00..8.38 rows=1 width=8)
Index Cond: (pk = 1)
-- 1.497 ms
Aggregate (cost=8.39..8.40 rows=1 width=8)
-> Index Scan using t_pkey on t (cost=0.00..8.39 rows=1 width=8)
Index Cond: ((pk > 900000) AND (pk = 1))
-- 1.187 ms
Brish
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to h2-database@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en
-~----------~----~----~----~------~----~------~--~---
