Thread: Indexes bug

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

Serj <fenix@am.ring.ru> writes:
> [ system not using an index when it should ]

Two questions:

1. Did you do a "vacuum analyze" after making the indexes?
   Without that, the optimizer may be choosing a sequential scan
   because it doesn't know how big the tables are.

2. What postgres version are you using?

There are some bugs in the current cvs sources that affect whether
indexes get used --- look at the ongoing threads in the hackers list.
Maybe you've found another manifestation of that problem.  But if
you're using 6.3.2 then it's something different...

            regards, tom lane

> Serj <fenix@am.ring.ru> writes:
> > [ system not using an index when it should ]
>
> Two questions:
>
> 1. Did you do a "vacuum analyze" after making the indexes?
>    Without that, the optimizer may be choosing a sequential scan
>    because it doesn't know how big the tables are.
>
> 2. What postgres version are you using?
>
> There are some bugs in the current cvs sources that affect whether
> indexes get used --- look at the ongoing threads in the hackers list.
> Maybe you've found another manifestation of that problem.  But if
> you're using 6.3.2 then it's something different...

I think we now know what is happening in the current cvs tree.  The
optimizer calls op_class to find if there is an pg_opam entry for the
expression (int4eq), the current index access type(btree), and the
current index op class(int4_ops).

In the case of oideqint4, there is no pg_amop to match it, and we can't
add extra rows to pg_amop to make it work.  I suppose we could try
adding a amopopr_compat column to pg_amop, and somehow do a lookup on
that if the first one does not match.  Because of the way the system
caches are structured, we would need a new cache for that extra column,
I think.  There must be a better way.


--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

> > 2. What postgres version are you using?
> >
> > There are some bugs in the current cvs sources that affect whether
> > indexes get used --- look at the ongoing threads in the hackers list.
> > Maybe you've found another manifestation of that problem.  But if
> > you're using 6.3.2 then it's something different...
>
I try it on 6.3.2 (with all patches from /pub/patches) and on current
CVS

--
SY, Serj