Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id B43162E0080 for ; Thu, 21 Feb 2008 16:58:09 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 74576-06 for ; Thu, 21 Feb 2008 16:58:01 -0400 (AST) Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.178]) by postgresql.org (Postfix) with ESMTP id 56BFC2E00B8 for ; Thu, 21 Feb 2008 16:58:05 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id m38so210494waf.5 for ; Thu, 21 Feb 2008 12:58:04 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=bLgQBNl/9M48vs8RtOEdTWtU/sSkMsMdLTKObZhSwPI=; b=wlLpENwTs1IOE2DyunxurutzQhIEuQoEeYfAGx6+fSk256hLXhNY/d/0eb+/8OPXf6WFIWIufmdEhtJabsH/IhYvT476IB+HjbqeBk6xEv6eSbu9xhUikDSdQV124wLwIdcyM6hSp1kfj7vxQ7Fh01HzyjarKrGaD6O5RlZoh+A= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=uDAU7bAlWwHI9UsUTIMAe3Rxc1ylVFIjz2k0385rBybnbz36AdJ6ovScHUWVtKEZKgNC26lGfLtF4EBydC96NlpTKD0NVYw6wa9eyhvrZr77eARxLg3dVWKXcqmaHVXdMoOPhvxywU1ZqvwqW8CO5kwwBod9oiaP2JxRKZ+MmSI= Received: by 10.115.90.1 with SMTP id s1mr4371145wal.50.1203627484195; Thu, 21 Feb 2008 12:58:04 -0800 (PST) Received: by 10.114.133.6 with HTTP; Thu, 21 Feb 2008 12:58:04 -0800 (PST) Message-ID: Date: Thu, 21 Feb 2008 14:58:04 -0600 From: "Scott Marlowe" To: "Adonias Malosso" Subject: Re: 4s query want to run faster Cc: pgsql-performance@postgresql.org In-Reply-To: <8a5d3c890802211248k7d9dfbb5ndd645358a84130a7@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <8a5d3c890802211248k7d9dfbb5ndd645358a84130a7@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/271 X-Sequence-Number: 28813 On Thu, Feb 21, 2008 at 2:48 PM, Adonias Malosso wrote: > Hi all, > > The following query takes about 4s to run in a 16GB ram server. Any ideas > why it doesn=B4t use index for the primary keys in the join conditions? > > select i.inuid, count(*) as total > from cte.instrumentounidade i > inner join cte.pontuacao p on p.inuid =3D i.inuid > inner join cte.acaoindicador ai on ai.ptoid =3D p.ptoid > inner join cte.subacaoindicador si on si.aciid =3D ai.aciid > where i.itrid =3D 2 and p.ptostatus =3D 'A' > group by i.inuid > having count(*) > 0 What does explain analyze say about that query?