Обсуждение: index theory

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 oneindex 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.83rows=1 width=24)  Index Cond: ((i1 = 'a'::character varying) AND (i1 = 'b'::character varying))
 

It's right and I undererstand why not use both indexes. But I talked
about it with one Oracle user and he said me Oracle knows use both indexes 
and results from both index scans are mergeted to final result -- this is maybe 
used if full access to table (too big rows?) is more expensive than 2x index 
scan and final merge. Is in PG possible something like this? And within 
query/table? I know about it in JOIN (and subselect maybe) only, but in 
the "standard" WHERE?

test=# explain SELECT * FROM ii a JOIN ii b ON a.i1=b.i2;                               QUERY PLAN
         
 
--------------------------------------------------------------------------Merge Join  (cost=0.00..171.50 rows=5000
width=48) Merge Cond: ("outer".i1 = "inner".i2)  ->  Index Scan using i1 on ii a  (cost=0.00..52.00 rows=1000 width=24)
->  Index Scan using i2 on ii b  (cost=0.00..52.00 rows=1000 width=24)
 

Thanks,
   Karel
-- Karel Zak  <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz,
http://mape.jcu.cz

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.

--  Rod Taylor

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@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz,
http://mape.jcu.cz

Karel Zak kirjutas K, 16.10.2002 kell 15:19:
> 
>  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))
> 
> 
>  It's right and I undererstand why not use both indexes. But I talked
> about it with one Oracle user and he said me Oracle knows use both indexes 
> and results from both index scans are mergeted to final result -- this is maybe 
> used if full access to table (too big rows?) is more expensive than 2x index 
> scan and final merge. Is in PG possible something like this?

There has been some talk about using bitmaps generated from indexes as
an intermediate step.

----------
Hannu