Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1X0ALF-0004Vj-Es for pgsql-performance@arkaria.postgresql.org; Thu, 26 Jun 2014 14:07:57 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1X0ALE-0001zR-PJ for pgsql-performance@arkaria.postgresql.org; Thu, 26 Jun 2014 14:07:56 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1X0ALC-0001wc-QG for pgsql-performance@postgresql.org; Thu, 26 Jun 2014 14:07:54 +0000 Received: from qmta01.westchester.pa.mail.comcast.net ([2001:558:fe14:43:76:96:62:16]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1X0AL3-0002ZH-D7 for pgsql-performance@postgresql.org; Thu, 26 Jun 2014 14:07:53 +0000 Received: from omta20.westchester.pa.mail.comcast.net ([76.96.62.71]) by qmta01.westchester.pa.mail.comcast.net with comcast id K0gE1o0061YDfWL5127h9D; Thu, 26 Jun 2014 14:07:41 +0000 Received: from [192.168.1.172] ([68.37.13.70]) by omta20.westchester.pa.mail.comcast.net with comcast id K27h1o00P1Wh2x83g27h2c; Thu, 26 Jun 2014 14:07:41 +0000 Message-ID: <53AC2931.40003@comcast.net> Date: Thu, 26 Jun 2014 10:07:45 -0400 From: AJ Weber User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:24.0) Gecko/20100101 Thunderbird/24.3.0 MIME-Version: 1.0 To: Matheus de Oliveira CC: Shaun Thomas , "pgsql-performance@postgresql.org" Subject: Re: how to improve perf of 131MM row table? References: <53AB2CB1.8080201@comcast.net> <53AB35CC.10202@optionshouse.com> <1801a5d4-c3f1-4c0c-9c2c-ffb9d7f723a3@email.android.com> <53AB4551.4070207@optionshouse.com> <53AC1F6E.4000700@comcast.net> In-Reply-To: Content-Type: multipart/alternative; boundary="------------080405030803090101030100" DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=comcast.net; s=q20140121; t=1403791661; bh=LlYFfQDMHOwKC2VZlj4j12h2FurGnmCAmIyNnsSQk70=; h=Received:Received:Message-ID:Date:From:MIME-Version:To:Subject: Content-Type; b=XnvOwaNSQb1BLrMRa15qF5EIrX03IZL7DTv8W2qdviV3aQhd8+WxO4ADff1jLLWNa 1qOpTIM3Q2QOkRYgbGxJ9qCxqoVMm4s/QfPCou6gAiOrPEoVWODbTNUJZdEeUDAp5C WWbhytDEiBwkzogOkAF5QFR1ieR3K302i4g/Mdr1+PbVAo/ZE2NN/yVXbfX1dgrLon xxQcoTHAT01spQAQpjp9U0JjHjZ4Hrg9ohwxLrUUcNcs1FndR8DwHPM31w3PpXLvYB GZR3RUhZaF1Ew7eGI34sRrrYh+gYYKDWGbIKROuEt+wz2QfnpU7mGZqsYdtbzG1wDw f7eINBnT1Syrg== X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multi-part message in MIME format. --------------080405030803090101030100 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit I will try this, but can you clarify the syntax? I only know the VALUES clause from insert statements, and it would be one set of parens like VALUES('175769', '175771', ... ) You seem to indicate a VALUES clause that has strange parenthesis corresponding to it. Thank you for the feedback and offer to help! -AJ On 6/26/2014 9:56 AM, Matheus de Oliveira wrote: > > On Thu, Jun 26, 2014 at 10:26 AM, AJ Weber > wrote: > > OK, the sample query is attached (hopefully attachments are > allowed) as "query.sql". > The "master table" definition is attached as "table1.sql". > The "detail table" definition is attached as "table2.sql". > The EXPLAIN (ANALYZE, BUFFERS) output is here: > http://explain.depesz.com/s/vd5 > > > Could you try chaning your query and sending the EXPLAIN of the following? > > Instead of `node_id in ('175769', '175771', ...)` try `node_in IN > (VALUES('175769'), ('175771'), ... )`. > > > -- > Matheus de Oliveira > Analista de Banco de Dados > Dextra Sistemas - MPS.Br nível F! > www.dextra.com.br/postgres > --------------080405030803090101030100 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit I will try this, but can you clarify the syntax?  I only know the VALUES clause from insert statements, and it would be one set of parens like VALUES('175769', '175771', ... )

You seem to indicate a VALUES clause that has strange parenthesis corresponding to it.

Thank you for the feedback and offer to help!

-AJ

On 6/26/2014 9:56 AM, Matheus de Oliveira wrote:

On Thu, Jun 26, 2014 at 10:26 AM, AJ Weber <aweber@comcast.net> wrote:
OK, the sample query is attached (hopefully attachments are allowed) as "query.sql".
The "master table" definition is attached as "table1.sql".
The "detail table" definition is attached as "table2.sql".
The EXPLAIN (ANALYZE, BUFFERS) output is here: http://explain.depesz.com/s/vd5

Could you try chaning your query and sending the EXPLAIN of the following?

Instead of `node_id in ('175769', '175771', ...)` try `node_in IN (VALUES('175769'), ('175771'), ... )`.


--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres


--------------080405030803090101030100--