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
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) |