DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=opz7rM5Lrq+tWvVlar+WGMQzKmfui1cDOy71DHHxnVygK8pmGQ7q0mXW3UJFmbsQDhm9ZXUuvTxfr3I+0mkhyZWEqiU5RvqOgM+Trqu33QGAeufS9ECfBLzI+YWDaq6U2NAFSDHB1p5QmeSs0LQWGjUcOELK/o37HCltxogLlHo=
Message-ID: <c2d9e70e0510031221g1e21a5ebw6c3d151f55dced74@mail.gmail.com>
Date: Mon, 3 Oct 2005 14:21:42 -0500
From: Jaime Casanova <systemguards@gmail.com>
Reply-To: Jaime Casanova <systemguards@gmail.com>
To: "jan aerts (RI)" <jan.aerts@bbsrc.ac.uk>
Subject: Re: combination of function to simple query makes query slow
Cc: pgsql-sql@postgresql.org
In-Reply-To: 
 <84DA9D8AC9B05F4B889E7C70238CB451024A8D1B@rie2ksrv1.ri.bbsrc.ac.uk>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: 
 <84DA9D8AC9B05F4B889E7C70238CB451024A8D1B@rie2ksrv1.ri.bbsrc.ac.uk>

On 10/3/05, jan aerts (RI) <jan.aerts@bbsrc.ac.uk> wrote:
> Some more information:
>
> An EXPLAIN of the following query
>  my_db=3D> 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 =3D 16575564
>  my_db-> and m2.object1_id in (select aliases_of(m1.object2_id));
> gives:
>                                             QUERY PLAN
> ------------------------------------------------------------------------
> ----------------------------
>  Nested Loop  (cost=3D0.00..99746.00 rows=3D1170281 width=3D16)
>   Join Filter: (subplan)
>   ->  Index Scan using ind_cmappings_object1_id on c_mappings m1
> (cost=3D0.00..6.12 rows=3D2 width=3D8)
>         Index Cond: (object1_id =3D 16575564)
>   ->  Seq Scan on c_mappings m2  (cost=3D0.00..36052.89 rows=3D1435589
> width=3D8)
>   SubPlan
>     ->  Result  (cost=3D0.00..0.01 rows=3D1 width=3D0)
> (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 =3D 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 ;)