On 10/3/05, jan aerts (RI) <[EMAIL PROTECTED]> wrote: > Some more information: > > An EXPLAIN of the following query > my_db=> explain select m1.object1_id, m1.object2_id, m2.object1_id, > m2.object2_id > my_db-> from c_mappings m1, c_mappings m2 > my_db-> where m1.object1_id = 16575564 > my_db-> and m2.object1_id in (select aliases_of(m1.object2_id)); > gives: > QUERY PLAN > ------------------------------------------------------------------------ > ---------------------------- > Nested Loop (cost=0.00..99746.00 rows=1170281 width=16) > Join Filter: (subplan) > -> Index Scan using ind_cmappings_object1_id on c_mappings m1 > (cost=0.00..6.12 rows=2 width=8) > Index Cond: (object1_id = 16575564) > -> Seq Scan on c_mappings m2 (cost=0.00..36052.89 rows=1435589 > width=8) > SubPlan > -> Result (cost=0.00..0.01 rows=1 width=0) > (7 rows) > > All columns of c_mappings, as well as the columns that are accessed > through the aliases_of function, as indexed. However, notice how the > second loop uses a "Seq Scan" instead of an "Index Scan". > Is there a way to use an index scan on the results of a function? > > Thanks, > jan. >
what version is your postgres? what if you make temp table first? something like this: select * from c_mappings where object1_id = 16575564 into temp m1; select m1.object1_id, m1.object2_id, m2.object1_id, m2.object2_id from m1, c_mappings m2 where m2.object1_id in (select aliases_of(m1.object2_id)); just an idea... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match