X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 399B456401 for ; Tue, 8 Mar 2005 11:40:05 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 37144-01 for ; Tue, 8 Mar 2005 11:40:00 +0000 (GMT) Received: from anchor-post-34.mail.demon.net (anchor-post-34.mail.demon.net [194.217.242.92]) by svr1.postgresql.org (Postfix) with ESMTP id 265EE5641A for ; Tue, 8 Mar 2005 11:39:57 +0000 (GMT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-34.mail.demon.net with esmtp (Exim 4.42) id 1D8d4B-0009Nq-D5; Tue, 08 Mar 2005 11:39:59 +0000 Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 6951716CC0; Tue, 8 Mar 2005 11:39:55 +0000 (GMT) Message-ID: <422D8F0E.8000906@archonet.com> Date: Tue, 08 Mar 2005 11:39:58 +0000 From: Richard Huxton User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Gaetano Mendola Cc: "pgsql-performance@postgresql.org" Subject: Re: bad plan References: <422D7F23.7000109@bigfoot.com> In-Reply-To: <422D7F23.7000109@bigfoot.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.061 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/124 X-Sequence-Number: 10829 Gaetano Mendola wrote: > running a 7.4.5 engine, I'm facing this bad plan: > > empdb=# explain analyze SELECT name,url,descr,request_status,url_status,size_mb,estimated_start,request_time_stamp > empdb-# FROM v_sc_user_request > empdb-# WHERE > empdb-# login = 'babinow1' > empdb-# LIMIT 10 ; > -> Subquery Scan vsp (cost=985.73..1016.53 rows=1103 width=12) (actual time=25.328..1668.754 rows=493 loops=31) > -> Merge Join (cost=985.73..1011.01 rows=1103 width=130) (actual time=25.321..1666.666 rows=493 loops=31) > Merge Cond: ("outer".id_program = "inner".id_program) The problem to address is in this subquery. That's a total of 31 x (1668.754 - 25.328) = 50seconds (about). Since your query is so simple, I'm guessing v_sc_user_request is a view. Can you provide the definition? -- Richard Huxton Archonet Ltd