Re: combination of function to simple query makes query slow - Mailing list pgsql-sql

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:systemguards@gmail.com]
> 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) <jan.aerts@bbsrc.ac.uk> 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 ;)
>