Re: index theory

On Wed, Oct 16, 2002 at 09:25:37AM -0400, Rod Taylor wrote:
> On Wed, 2002-10-16 at 09:19, Karel Zak wrote:
> >
> > Hi,
> >
> > I have SQL query:
> >
> > SELECT * FROM ii WHERE i1='a' AND i2='b';
> >
> > There're indexes on i1 and i2. I know best solution is use one
> > index on both (i1, i2).
> >
> > The EXPLAIN command show that optimalizer wants to use one index:
> >
> > test=# explain SELECT * FROM ii WHERE i1='a' AND i1='b';
> > QUERY PLAN
> > ---------------------------------------------------------------------------------
> > Index Scan using i1 on ii (cost=0.00..4.83 rows=1 width=24)
> > Index Cond: ((i1 = 'a'::character varying) AND (i1 = 'b'::character varying))
>
> I think you typo'd. i1='a' AND i1='b' turns into 'a' = 'b' which
> certainly isn't true in any alphabets I know of.

Oh... sorry, right is:

test=# explain SELECT * FROM ii WHERE i1='a' AND i2='b';
QUERY PLAN
---------------------------------------------------------------
Index Scan using i2 on ii (cost=0.00..17.08 rows=1 width=24)
Index Cond: (i2 = 'b'::character varying)
Filter: (i1 = 'a'::character varying)

The query is not important ... it's dummy example only. I think about two
indexes on one table for access to table.

Karel

--
Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>
http://home.zf.jcu.cz/~zakkr/

C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz