From: Yeb Havinga <yebhavinga@gmail.com>
To: Rainer Pruy <Rainer.Pruy@Acrys.COM>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Query slowing down significantly??
Date: Mon, 01 Mar 2010 20:34:38 +0100
Message-ID: <4B8C16CE.6090901@gmail.com> (raw)
In-Reply-To: <4B8BFDEF.1080006@acrys.com>
References: <4B8BB815.4060104@acrys.com>
<17910.1267460132@sss.pgh.pa.us>
<4B8BFDEF.1080006@acrys.com>
Rainer Pruy wrote:
> Thanks for the hint.
> I should have been considering that in the first place.
> (But the obvious is easily left unrecognised..)
>
> The prepared statement gives:
>
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------------------------------------
> Nested Loop (cost=0.00..25.18 rows=2 width=175) (actual time=36.116..49.998 rows=1 loops=1)
> -> Index Scan using x_context_01 on context c (cost=0.00..10.76 rows=2 width=67) (actual time=0.029..6.947 rows=12706 loops=1)
> Index Cond: ((contextid)::text = $1)
> -> Index Scan using x_fk_context_hierarchy_02 on context_hierarchy h (cost=0.00..7.20 rows=1 width=108) (actual time=0.003..0.003
> rows=0 loops=12706)
> Index Cond: (h.contextidx = c.idx)
> Filter: (((h.hierarchyname)::text = $2) AND (h.parentidx = $3))
> Total runtime: 50.064 ms
> (7 rows)
>
>
> And that is quite a bad plan given the current distribution of values.
>
Another approach might be to rewrite recursion into your hierarchy with
the in 8.4 new WITH RECURSIVE option in sql queries. The possible gains
there are way beyond anything you can accomplish with optimizing
recursive functions.
Regards,
Yeb Havinga
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-performance@postgresql.org
Cc: yebhavinga@gmail.com, Rainer.Pruy@Acrys.COM
Subject: Re: Query slowing down significantly??
In-Reply-To: <4B8C16CE.6090901@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