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)


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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Reply via email to