My Postgres version is 7.3.4 (on a central server, so I can't upgrade if that would be one of the suggestions...)
It is indeed completely valid to make such a temporary table, but I need this function to help me automate some standard queries other people can make on the database. In other words: (1) the query has to be done in one go, instead of using 2 different selects, and (2) using the complete definition of the function in the query itself becomes pretty bloating, as this function can be used 3 or more times in a single query... I found out that it has probably something to do with Postgres not able to use an index scan on this function. Even though I ended up defining it as 'stable' or even 'immutable'. (Or I may be completely wrong, of course). I thought that making a function stable or immutable would make it available for an index search. Any additional suggestions? Thanks, jan. > -----Original Message----- > From: Jaime Casanova [mailto:[EMAIL PROTECTED] > Sent: 03 October 2005 20:22 > To: jan aerts (RI) > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] combination of function to simple query > makes query slow > > 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 2: Don't 'kill -9' the postmaster
