Thread

Indexes bug

Serj <fenix@am.ring.ru> — 1998-08-11T10:30:11Z

Hi

I found some bug on next example:
----------------------------
/* step 1 -------------- */
test=> select pubid,bn into table l1 from l;
SELECT
test=> select pubid,bn into table n1 from n;
SELECT
/* pubid & bn - int4 */
test=> select count(*) from l1;
count
-----
 6776
 (1 row)
test=> select count(*) from n1;
count
-----
4478
(1 row)
/* Step 2 -------------- */
test=> explain select l1.pubid from l1,n1 where l1.pubid=n1.pubid and
l1.bn=n1.bn;
NOTICE:  QUERY PLAN:

Merge Join  (cost=0.00 size=1 width=16)
  ->  Seq Scan  (cost=0.00 size=0 width=0)
        ->  Sort  (cost=0.00 size=0 width=0)
              ->  Seq Scan on l1  (cost=0.00 size=0 width=8)
  ->  Seq Scan  (cost=0.00 size=0 width=0)
        ->  Sort  (cost=0.00 size=0 width=0)
              ->  Seq Scan on n1  (cost=0.00 size=0 width=8)

/* it's work */ 
/* Step 3 -------------- */

test=> create index l1_i on l1 (pubid,bn);
CREATE
test=> explain select l1.pubid from l1,n1 where l1.pubid=n1.pubid and
l1.bn=n1.bn;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00 size=1 width=16)
  ->  Seq Scan on n1  (cost=0.00 size=0 width=8)
  ->  Index Scan using l1_i on l1  (cost=2.00 size=6776 width=8)

/* it's work */ 
/* Step 4 -------------- */

test=> create index n1_i on n1 (pubid,bn);
CREATE
test=> explain select l1.pubid from l1,n1 where l1.pubid=n1.pubid and
l1.bn=n1.bn;
NOTICE:  QUERY PLAN:

Hash Join  (cost=817.76 size=2 width=16)
  ->  Seq Scan on l1  (cost=267.61 size=6776 width=8)
  ->  Hash  (cost=0.00 size=0 width=0)
        ->  Seq Scan on n1  (cost=176.77 size=4478 width=8)

/* it's not work fine :((( */ 
/* Step 5-------------- */
 
test=> drop index n1_i;
DROP
/* 

test=> explain select l1.pubid from l1,n1 where l1.pubid=n1.pubid and
l1.bn=n1.bn;
NOTICE:  QUERY PLAN:

Hash Join  (cost=817.76 size=2 width=16)
  ->  Seq Scan on l1  (cost=267.61 size=6776 width=8)
  ->  Hash  (cost=0.00 size=0 width=0)
        ->  Seq Scan on n1  (cost=176.77 size=4478 width=8)
 
/* Is it right ? I meen query plan must be like in "Step 3" ? */

---------------------------- 

Thnk's

SY, Serj