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 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? 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(at)zf(dot)jcu(dot)cz>
http://home.zf.jcu.cz/~zakkr/

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