This script tests postgres without indexes.
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;
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:
Seq Scan on t (cost=0.00..17746.76 rows=1 width=8)
Filter: (pk = 1)
-- 302.308 ms
Seq Scan on t (cost=0.00..17746.76 rows=2 wid
Filter: (pk = ANY ('{1,1000000}'::bigint[]))
-- 402.829 ms
Seq Scan on t (cost=0.00..20368.11 rows=2 width=8)
Filter: ((pk = 1) OR (pk = 1000000))
-- 375.981 ms
Result (cost=0.00..17746.76 rows=1 width=8)
One-Time Filter: false
-> Seq Scan on t (cost=0.00..17746.76 rows=1 width=8)
Filter: (pk = 1)
-- 1.064 ms
Aggregate (cost=20368.12..20368.13 rows=1 width=8)
-> Seq Scan on t (cost=0.00..20368.11 rows=1 width=8)
Filter: ((pk > 900000) AND (pk = 1))
-- 317.203 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 [email protected]
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
-~----------~----~----~----~------~----~------~--~---
