Re: Performance of PK queries when AND, OR or IN is used

[Brish]

Here is a script that runs on h2 that tries to show some bad index use
(based heavily on the queries in this benchmark):

create table t (
  pk bigint not null primary key
);

@LOOP 1048576 insert into t values (?);

analyze;

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;
explain SELECT count(pk) FROM t
  WHERE pk = 1 AND pk > 900000;

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;

drop table t;


The plans it comes with with are:

SELECT PK
FROM PUBLIC.T /* PUBLIC.PRIMARY_KEY_5: PK = 1 */
WHERE PK = 1
-- 0 ms

SELECT PK
FROM PUBLIC.T /* PUBLIC.PRIMARY_KEY_5: PK >= 1 AND PK <= 1000000 */
WHERE PK IN(1, 1000000)
-- 2781 ms

SELECT PK
FROM PUBLIC.T /* PUBLIC.T_TABLE_SCAN */
WHERE (PK = 1) OR (PK = 1000000)
-- 14266 ms

SELECT PK
FROM PUBLIC.T /* PUBLIC.PRIMARY_KEY_5: PK = 1 AND PK = 1000000 */
WHERE (PK = 1) AND (PK = 1000000)
-- 0 ms

SELECT COUNT(PK)
FROM PUBLIC.T /* PUBLIC.PRIMARY_KEY_5: PK = 1 AND PK > 900000 */
WHERE (PK = 1) AND (PK > 900000)
-- 0 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
-~----------~----~----~----~------~----~------~--~---