| 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: | Whole Thread | Raw Message | 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) |