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