Indexes bug
От | Serj |
---|---|
Тема | Indexes bug |
Дата | |
Msg-id | 35D01D33.D27F43FC@am.ring.ru обсуждение исходный текст |
Ответы |
Re: [HACKERS] Indexes bug
|
Список | 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
В списке pgsql-hackers по дате отправления: