Indexes bug

From: Serj <fenix(at)am(dot)ring(dot)ru>
To: "pgsql-hackers(at)postgreSQL(dot)org" <pgsql-hackers(at)postgreSQL(dot)org>
Subject: Indexes bug
Date: 1998-08-11 10:30:11
Message-ID: 35D01D33.D27F43FC@am.ring.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Browse pgsql-hackers by date

  From Date Subject
Next Message Maarten Boekhold 1998-08-11 11:08:01 6.3.2. patch for functional indices
Previous Message Bruce Momjian 1998-08-11 06:30:13 Re: [HACKERS] Re: type coersion (was OR clause status)