From: Jaime Casanova <systemguards@gmail.com>
To: jan aerts (RI) <jan.aerts@bbsrc.ac.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: combination of function to simple query makes query slow
Date: Mon, 3 Oct 2005 14:21:42 -0500
Message-ID: <c2d9e70e0510031221g1e21a5ebw6c3d151f55dced74@mail.gmail.com> (raw)
In-Reply-To: <84DA9D8AC9B05F4B889E7C70238CB451024A8D1B@rie2ksrv1.ri.bbsrc.ac.uk>
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=> 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 ;)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: systemguards@gmail.com, jan.aerts@bbsrc.ac.uk
Subject: Re: combination of function to simple query makes query slow
In-Reply-To: <c2d9e70e0510031221g1e21a5ebw6c3d151f55dced74@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox