Indexovani - teorie

[Karel Zak]

On Wed, Oct 16, 2002 at 12:44:49PM +0200, Honza Pazdziora wrote:

> Operator OR je zcela opacna situace. Protoze to, ze mate index nad
> (sl1, sl2) Vam vyhledani vsech zaznamu, kde se sl1 = 'xxx' nijak
> neurychli. Zato ale pote, co server nasel vsechny zaznamy, kde sl1
> = 'xxx', musi (pokud nad sl2 nebude index) projit jeste jednou celou
> tabulku a zjistit, kdeze se sl2 = 'yyy'. Cili vysledkem pravdepodobne

 Todle neni uplne presne. Nerekl bych, ze musi projit jeste jednou
 celou tabulku, ale porovnani vsech polozek dane tabulky, ktere jsou 
 obsazene ve WHERE se provadi pri jednom pruchodu.

# explain SELECT * FROM ii WHERE i1='a' OR i2='b';
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Seq Scan on ii  (cost=0.00..25.00 rows=10 width=28)
   Filter: ((i1 = 'a'::character varying) OR (i2 = 'b'::character varying))


Podobne je to pokud neexistuje spolecny index. K tabulce se pristupuje 
pomoci indexu a data v tabulce se dale filtruji podle polozek v indexu 
neobsazenych. Mam mocit, ze zde zaznelo, ze pokud jsou indexy na obe 
polozky dotazu tak je to stejne jako jejich spolecny index. To je 
pochopitelne hloupost. Pouzije se jen jeden index a na dalsi cast 
se pouzije "filtrovani" -- pokud by se pouzilo vice indexu tak by se 
pak muselo delat nejake filtrovani/merge mezi vysledky z jednotlivych indexu
(coz se deje pri pouziti napr. subselectu nebo nejakych joinu). Viz:

Neexistuje spolecny index, ale existuji samostatne indexy:

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


Pouziji se oba indexy a vysledek se prefiltruje:

test=# explain SELECT * FROM ii WHERE i1='a' 
               AND i2 IN (SELECT i2 FROM ii WHERE i2='b');
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Index Scan using i1_idx on ii  (cost=0.00..102.42 rows=2 width=28)
   Index Cond: (i1 = 'a'::character varying)
   Filter: (subplan)
   SubPlan
     ->  Materialize  (cost=17.07..17.07 rows=5 width=14)
           ->  Index Scan using i2_idx on ii  (cost=0.00..17.07 rows=5 width=14)
                 Index Cond: (i2 = 'b'::character varying)

Pouziji se oba indexy a vysledek a pak mergne:

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=56)
   Merge Cond: ("outer".i1 = "inner".i2)
   ->  Index Scan using i1_idx on ii a  (cost=0.00..52.00 rows=1000 width=28)
   ->  Index Scan using i2_idx on ii b  (cost=0.00..52.00 rows=1000 width=28)


> Samozrejme, pokud ty atributy budou mit vhodnou kardinalitu nebo budou
> splneny spousty jinych podminek, tak bude lepsi projit treba celou
> tabulku nez pouzivat jakekoli indexy, cili tohle berte pouze jako
> obecne doporuceni jak zacit. Konkretni situaci si budete muset
> zanalyzovat sam.

 Todle je hodne dobra pripominka (viz. index na boolean:-) . Obcas 
se lide zlobi, ze jim napr. PG ignoruje jejich skvele indexy a 
pritom jejich pouziti by bylo v danem pripade na obtiz.

    Karel

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