Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1X09hI-0002zy-Ca for pgsql-performance@arkaria.postgresql.org; Thu, 26 Jun 2014 13:26:40 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1X09hH-00083O-HN for pgsql-performance@arkaria.postgresql.org; Thu, 26 Jun 2014 13:26:39 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1X09hG-00083I-1X for pgsql-performance@postgresql.org; Thu, 26 Jun 2014 13:26:38 +0000 Received: from qmta09.westchester.pa.mail.comcast.net ([2001:558:fe14:43:76:96:62:96]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1X09h8-0006FL-78 for pgsql-performance@postgresql.org; Thu, 26 Jun 2014 13:26:36 +0000 Received: from omta01.westchester.pa.mail.comcast.net ([76.96.62.11]) by qmta09.westchester.pa.mail.comcast.net with comcast id K0vB1o0060EZKEL591SVEr; Thu, 26 Jun 2014 13:26:29 +0000 Received: from [192.168.1.172] ([98.109.148.138]) by omta01.westchester.pa.mail.comcast.net with comcast id K1S31o00R2zPoey3M1S9sa; Thu, 26 Jun 2014 13:26:24 +0000 Message-ID: <53AC1F6E.4000700@comcast.net> Date: Thu, 26 Jun 2014 09:26:06 -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: 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> In-Reply-To: <53AB4551.4070207@optionshouse.com> Content-Type: multipart/mixed; boundary="------------040008050607010709000605" DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=comcast.net; s=q20140121; t=1403789189; bh=UdmUCVz8S9mZcrurSWDKxU5AJNp0zPkANRVKmBvP5xQ=; h=Received:Received:Message-ID:Date:From:MIME-Version:To:Subject: Content-Type; b=pRycSAZhcmuEaV2xxjTLcD+IBiiOcvpBSm5o572MExI5h3gfIhwtzTS7Olv65hJO1 /61YibNcXtyDmXpdHpkFQgE6LiiyHj03b5mgPj3Jzhx6aGA53RgusyoVW6KvHYOJZu EDpelLTfawt33FfiCPb3qY6C59fUaDPoOKYAG4hSbXRX9CXPoO7F1tt9vnA+h8m2N+ eybQd0ZiTcyt7ftQRVjqBlOpNt/0G1uTbnhxU0ite9wDtohxtk0DS3rBJywVLfVxwG D9J8pBeP5auln/TusUxIq+JEP0nF304yWxTCJ8enkr5htnRufJ6SxZJmAfishYfG1y I0KuTrgsvt+rQ== 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. --------------040008050607010709000605 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit 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 Let me know if I can provide anything else, and thank you again. -AJ On 6/25/2014 5:55 PM, Shaun Thomas wrote: > On 06/25/2014 04:40 PM, Aaron Weber wrote: > >> In the meantime, I guess I wasn't clear about some other particulars >> The query's where clause is only an "IN", with a list of id's (those >> I mentioned are the PK), and the join is explicitly on the PK (so, >> indexed). > > Indexed doesn't mean indexed if the wrong datatypes are used. We need > to see the table and index definitions, and a sample query with > EXPLAIN ANALYZE output. > >> An IN with 50 int values took 23sec to return (by way of example). > > To me, this sounds like a sequence scan, or one of your key matches so > many rows, the random seeks are throwing off your performance. Of > course, I can't confirm that without EXPLAIN output. > --------------040008050607010709000605 Content-Type: text/plain; charset=windows-1252; name="query.sql" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="query.sql" c2VsZWN0CSAgICAgICAgbm9kZS5pZCAgICAgICAgICAgICAgICAgYXMgbm9k ZV9pZCwNCgkgICAgICAgICAgICBub2RlLnZlcnNpb24gICAgICAgICAgICBh cyBub2RlX3ZlcnNpb24sDQoJICAgICAgICAgICAgcHJvcC5xbmFtZV9pZCAg ICAgICAgICAgYXMgcW5hbWVfaWQsDQoJICAgICAgICAgICAgcHJvcC5sb2Nh bGVfaWQgICAgICAgICAgYXMgbG9jYWxlX2lkLA0KCSAgICAgICAgICAgIHBy b3AubGlzdF9pbmRleCAgICAgICAgIGFzIGxpc3RfaW5kZXgsDQoJICAgICAg ICAgICAgcHJvcC5hY3R1YWxfdHlwZV9uICAgICAgYXMgYWN0dWFsX3R5cGVf biwNCgkgICAgICAgICAgICBwcm9wLnBlcnNpc3RlZF90eXBlX24gICBhcyBw ZXJzaXN0ZWRfdHlwZV9uLA0KCSAgICAgICAgICAgIHByb3AuYm9vbGVhbl92 YWx1ZSAgICAgIGFzIGJvb2xlYW5fdmFsdWUsDQoJICAgICAgICAgICAgcHJv cC5sb25nX3ZhbHVlICAgICAgICAgYXMgbG9uZ192YWx1ZSwNCgkgICAgICAg ICAgICBwcm9wLmZsb2F0X3ZhbHVlICAgICAgICBhcyBmbG9hdF92YWx1ZSwN CgkgICAgICAgICAgICBwcm9wLmRvdWJsZV92YWx1ZSAgICAgICBhcyBkb3Vi bGVfdmFsdWUsDQoJICAgICAgICAgICAgcHJvcC5zdHJpbmdfdmFsdWUgICAg ICAgYXMgc3RyaW5nX3ZhbHVlLA0KCSAgICAgICAgICAgIHByb3Auc2VyaWFs aXphYmxlX3ZhbHVlIGFzIHNlcmlhbGl6YWJsZV92YWx1ZQ0KCSAgICAgICAg ZnJvbQ0KCSAgICAgICAgICAgIGFsZl9ub2RlIG5vZGUNCgkgICAgICAgICAg ICBqb2luIGFsZl9ub2RlX3Byb3BlcnRpZXMgcHJvcCBvbiAocHJvcC5ub2Rl X2lkID0gbm9kZS5pZCkNCgkgICAgICAgICBXSEVSRSAgbm9kZV9pZCBpbiAN CgkgICAgICAgICAgICAgICAgKCcxNzU3NjknLCAnMTc1NzcxJywgJzE3NTc4 MScsICcxNzU4MjUnLCAnMTc1ODgxJywgJzE3NTg5MycsICcxNzU5MTknLCAn MTc1OTMyJywgJzE3NTk2MycsICcxNzU5OTknLCAnMTc2MDIyJywgJzE3NjA3 OScsICcxNzYwOTknLCAnMTc2MTE1JywgJzE3NjExOCcsICcxNzYxNzEnLCAn MTc2MTgxJywgJzE3NjIxNycsICcxNzYyMjAnLCAnMTc2MjQzJywgJzE3NjI4 MycsICcxNzYzMTInLCAnMTc2MzI2JywgJzE3NjMzNScsICcxNzYzNzcnLCAn MTc2NDQxJywgJzE3NjQ0NCcsICcxNzY0NzUnLCAnMTc2NTMwJywgJzE3NjU3 MCcsICcxNzY2MjMnLCAnMTc2Njc0JywgJzE3NjcwMScsICcxNzY3MzAnLCAn MTc2NzQ4JywgJzE3Njc2MycsICcxNzY3NzEnLCAnMTc2ODA4JywgJzE3Njgz NicsICcxNzY4NTEnLCAnMTc2ODY0JywgJzE3Njg4MScsICcxNzY5MjknLCAn MTc2OTQ1JywgJzE3Njk0NycsICcxNzY5NjAnLCAnMTc3MDA2JywgJzE3NzAz OScsICcxNzcwNzknLCAnMTc3MTMxJywgJzE3NzE0NCcpDQo= --------------040008050607010709000605 Content-Type: text/plain; charset=windows-1252; name="table1.sql" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="table1.sql" LS0gVGFibGU6IGFsZl9ub2RlDQoNCi0tIERST1AgVEFCTEUgYWxmX25vZGU7 DQoNCkNSRUFURSBUQUJMRSBhbGZfbm9kZQ0KKA0KICBpZCBiaWdpbnQgTk9U IE5VTEwsDQogIHZlcnNpb24gYmlnaW50IE5PVCBOVUxMLA0KICBzdG9yZV9p ZCBiaWdpbnQgTk9UIE5VTEwsDQogIHV1aWQgY2hhcmFjdGVyIHZhcnlpbmco MzYpIE5PVCBOVUxMLA0KICB0cmFuc2FjdGlvbl9pZCBiaWdpbnQgTk9UIE5V TEwsDQogIG5vZGVfZGVsZXRlZCBib29sZWFuIE5PVCBOVUxMLA0KICB0eXBl X3FuYW1lX2lkIGJpZ2ludCBOT1QgTlVMTCwNCiAgbG9jYWxlX2lkIGJpZ2lu dCBOT1QgTlVMTCwNCiAgYWNsX2lkIGJpZ2ludCwNCiAgYXVkaXRfY3JlYXRv ciBjaGFyYWN0ZXIgdmFyeWluZygyNTUpLA0KICBhdWRpdF9jcmVhdGVkIGNo YXJhY3RlciB2YXJ5aW5nKDMwKSwNCiAgYXVkaXRfbW9kaWZpZXIgY2hhcmFj dGVyIHZhcnlpbmcoMjU1KSwNCiAgYXVkaXRfbW9kaWZpZWQgY2hhcmFjdGVy IHZhcnlpbmcoMzApLA0KICBhdWRpdF9hY2Nlc3NlZCBjaGFyYWN0ZXIgdmFy eWluZygzMCksDQogIENPTlNUUkFJTlQgYWxmX25vZGVfcGtleSBQUklNQVJZ IEtFWSAoaWQpLA0KICBDT05TVFJBSU5UIGZrX2FsZl9ub2RlX2FjbCBGT1JF SUdOIEtFWSAoYWNsX2lkKQ0KICAgICAgUkVGRVJFTkNFUyBhbGZfYWNjZXNz X2NvbnRyb2xfbGlzdCAoaWQpIE1BVENIIFNJTVBMRQ0KICAgICAgT04gVVBE QVRFIE5PIEFDVElPTiBPTiBERUxFVEUgTk8gQUNUSU9OLA0KICBDT05TVFJB SU5UIGZrX2FsZl9ub2RlX2xvYyBGT1JFSUdOIEtFWSAobG9jYWxlX2lkKQ0K ICAgICAgUkVGRVJFTkNFUyBhbGZfbG9jYWxlIChpZCkgTUFUQ0ggU0lNUExF DQogICAgICBPTiBVUERBVEUgTk8gQUNUSU9OIE9OIERFTEVURSBOTyBBQ1RJ T04sDQogIENPTlNUUkFJTlQgZmtfYWxmX25vZGVfc3RvcmUgRk9SRUlHTiBL RVkgKHN0b3JlX2lkKQ0KICAgICAgUkVGRVJFTkNFUyBhbGZfc3RvcmUgKGlk KSBNQVRDSCBTSU1QTEUNCiAgICAgIE9OIFVQREFURSBOTyBBQ1RJT04gT04g REVMRVRFIE5PIEFDVElPTiwNCiAgQ09OU1RSQUlOVCBma19hbGZfbm9kZV90 cW4gRk9SRUlHTiBLRVkgKHR5cGVfcW5hbWVfaWQpDQogICAgICBSRUZFUkVO Q0VTIGFsZl9xbmFtZSAoaWQpIE1BVENIIFNJTVBMRQ0KICAgICAgT04gVVBE QVRFIE5PIEFDVElPTiBPTiBERUxFVEUgTk8gQUNUSU9OLA0KICBDT05TVFJB SU5UIGZrX2FsZl9ub2RlX3R4biBGT1JFSUdOIEtFWSAodHJhbnNhY3Rpb25f aWQpDQogICAgICBSRUZFUkVOQ0VTIGFsZl90cmFuc2FjdGlvbiAoaWQpIE1B VENIIFNJTVBMRQ0KICAgICAgT04gVVBEQVRFIE5PIEFDVElPTiBPTiBERUxF VEUgTk8gQUNUSU9ODQopDQpXSVRIICgNCiAgRklMTEZBQ1RPUj05MCwgDQog IE9JRFM9RkFMU0UNCik7DQpBTFRFUiBUQUJMRSBhbGZfbm9kZQ0KICBPV05F UiBUTyBhbGZyZXNjbzsNCg0KLS0gSW5kZXg6IGZrX2FsZl9ub2RlX2FjbA0K DQotLSBEUk9QIElOREVYIGZrX2FsZl9ub2RlX2FjbDsNCg0KQ1JFQVRFIElO REVYIGZrX2FsZl9ub2RlX2FjbA0KICBPTiBhbGZfbm9kZQ0KICBVU0lORyBi dHJlZQ0KICAoYWNsX2lkKTsNCg0KLS0gSW5kZXg6IGZrX2FsZl9ub2RlX2xv Yw0KDQotLSBEUk9QIElOREVYIGZrX2FsZl9ub2RlX2xvYzsNCg0KQ1JFQVRF IElOREVYIGZrX2FsZl9ub2RlX2xvYw0KICBPTiBhbGZfbm9kZQ0KICBVU0lO RyBidHJlZQ0KICAobG9jYWxlX2lkKTsNCg0KLS0gSW5kZXg6IGZrX2FsZl9u b2RlX3N0b3JlDQoNCi0tIERST1AgSU5ERVggZmtfYWxmX25vZGVfc3RvcmU7 DQoNCkNSRUFURSBJTkRFWCBma19hbGZfbm9kZV9zdG9yZQ0KICBPTiBhbGZf bm9kZQ0KICBVU0lORyBidHJlZQ0KICAoc3RvcmVfaWQpOw0KDQotLSBJbmRl eDogZmtfYWxmX25vZGVfdHFuDQoNCi0tIERST1AgSU5ERVggZmtfYWxmX25v ZGVfdHFuOw0KDQpDUkVBVEUgSU5ERVggZmtfYWxmX25vZGVfdHFuDQogIE9O IGFsZl9ub2RlDQogIFVTSU5HIGJ0cmVlDQogICh0eXBlX3FuYW1lX2lkKTsN Cg0KLS0gSW5kZXg6IGZrX2FsZl9ub2RlX3R4bg0KDQotLSBEUk9QIElOREVY IGZrX2FsZl9ub2RlX3R4bjsNCg0KQ1JFQVRFIElOREVYIGZrX2FsZl9ub2Rl X3R4bg0KICBPTiBhbGZfbm9kZQ0KICBVU0lORyBidHJlZQ0KICAodHJhbnNh Y3Rpb25faWQpOw0KDQotLSBJbmRleDogaWR4X2FsZl9ub2RlX2RlbA0KDQot LSBEUk9QIElOREVYIGlkeF9hbGZfbm9kZV9kZWw7DQoNCkNSRUFURSBJTkRF WCBpZHhfYWxmX25vZGVfZGVsDQogIE9OIGFsZl9ub2RlDQogIFVTSU5HIGJ0 cmVlDQogIChub2RlX2RlbGV0ZWQpOw0KDQotLSBJbmRleDogaWR4X2FsZl9u b2RlX3R4bl9kZWwNCg0KLS0gRFJPUCBJTkRFWCBpZHhfYWxmX25vZGVfdHhu X2RlbDsNCg0KQ1JFQVRFIElOREVYIGlkeF9hbGZfbm9kZV90eG5fZGVsDQog IE9OIGFsZl9ub2RlDQogIFVTSU5HIGJ0cmVlDQogICh0cmFuc2FjdGlvbl9p ZCwgbm9kZV9kZWxldGVkKTsNCg0KLS0gSW5kZXg6IHN0b3JlX2lkDQoNCi0t IERST1AgSU5ERVggc3RvcmVfaWQ7DQoNCkNSRUFURSBVTklRVUUgSU5ERVgg c3RvcmVfaWQNCiAgT04gYWxmX25vZGUNCiAgVVNJTkcgYnRyZWUNCiAgKHN0 b3JlX2lkLCB1dWlkKTsNCg== --------------040008050607010709000605 Content-Type: text/plain; charset=windows-1252; name="table2.sql" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="table2.sql" LS0gVGFibGU6IGFsZl9ub2RlX3Byb3BlcnRpZXMNCg0KLS0gRFJPUCBUQUJM RSBhbGZfbm9kZV9wcm9wZXJ0aWVzOw0KDQpDUkVBVEUgVEFCTEUgYWxmX25v ZGVfcHJvcGVydGllcw0KKA0KICBub2RlX2lkIGJpZ2ludCBOT1QgTlVMTCwN CiAgYWN0dWFsX3R5cGVfbiBpbnRlZ2VyIE5PVCBOVUxMLA0KICBwZXJzaXN0 ZWRfdHlwZV9uIGludGVnZXIgTk9UIE5VTEwsDQogIGJvb2xlYW5fdmFsdWUg Ym9vbGVhbiwNCiAgbG9uZ192YWx1ZSBiaWdpbnQsDQogIGZsb2F0X3ZhbHVl IHJlYWwsDQogIGRvdWJsZV92YWx1ZSBkb3VibGUgcHJlY2lzaW9uLA0KICBz dHJpbmdfdmFsdWUgY2hhcmFjdGVyIHZhcnlpbmcoMTAyNCksDQogIHNlcmlh bGl6YWJsZV92YWx1ZSBieXRlYSwNCiAgcW5hbWVfaWQgYmlnaW50IE5PVCBO VUxMLA0KICBsaXN0X2luZGV4IGludGVnZXIgTk9UIE5VTEwsDQogIGxvY2Fs ZV9pZCBiaWdpbnQgTk9UIE5VTEwsDQogIENPTlNUUkFJTlQgYWxmX25vZGVf cHJvcGVydGllc19wa2V5IFBSSU1BUlkgS0VZIChub2RlX2lkLCBxbmFtZV9p ZCwgbGlzdF9pbmRleCwgbG9jYWxlX2lkKSwNCiAgQ09OU1RSQUlOVCBma19h bGZfbnByb3BfbG9jIEZPUkVJR04gS0VZIChsb2NhbGVfaWQpDQogICAgICBS RUZFUkVOQ0VTIGFsZl9sb2NhbGUgKGlkKSBNQVRDSCBTSU1QTEUNCiAgICAg IE9OIFVQREFURSBOTyBBQ1RJT04gT04gREVMRVRFIE5PIEFDVElPTiwNCiAg Q09OU1RSQUlOVCBma19hbGZfbnByb3BfbiBGT1JFSUdOIEtFWSAobm9kZV9p ZCkNCiAgICAgIFJFRkVSRU5DRVMgYWxmX25vZGUgKGlkKSBNQVRDSCBTSU1Q TEUNCiAgICAgIE9OIFVQREFURSBOTyBBQ1RJT04gT04gREVMRVRFIE5PIEFD VElPTiwNCiAgQ09OU1RSQUlOVCBma19hbGZfbnByb3BfcW4gRk9SRUlHTiBL RVkgKHFuYW1lX2lkKQ0KICAgICAgUkVGRVJFTkNFUyBhbGZfcW5hbWUgKGlk KSBNQVRDSCBTSU1QTEUNCiAgICAgIE9OIFVQREFURSBOTyBBQ1RJT04gT04g REVMRVRFIE5PIEFDVElPTg0KKQ0KV0lUSCAoDQogIEZJTExGQUNUT1I9OTAs IA0KICBPSURTPUZBTFNFDQopOw0KQUxURVIgVEFCTEUgYWxmX25vZGVfcHJv cGVydGllcw0KICBPV05FUiBUTyBhbGZyZXNjbzsNCg0KLS0gSW5kZXg6IGZr X2FsZl9ucHJvcF9sb2MNCg0KLS0gRFJPUCBJTkRFWCBma19hbGZfbnByb3Bf bG9jOw0KDQpDUkVBVEUgSU5ERVggZmtfYWxmX25wcm9wX2xvYw0KICBPTiBh bGZfbm9kZV9wcm9wZXJ0aWVzDQogIFVTSU5HIGJ0cmVlDQogIChsb2NhbGVf aWQpOw0KDQotLSBJbmRleDogZmtfYWxmX25wcm9wX24NCg0KLS0gRFJPUCBJ TkRFWCBma19hbGZfbnByb3BfbjsNCg0KQ1JFQVRFIElOREVYIGZrX2FsZl9u cHJvcF9uDQogIE9OIGFsZl9ub2RlX3Byb3BlcnRpZXMNCiAgVVNJTkcgYnRy ZWUNCiAgKG5vZGVfaWQpOw0KDQotLSBJbmRleDogZmtfYWxmX25wcm9wX3Fu DQoNCi0tIERST1AgSU5ERVggZmtfYWxmX25wcm9wX3FuOw0KDQpDUkVBVEUg SU5ERVggZmtfYWxmX25wcm9wX3FuDQogIE9OIGFsZl9ub2RlX3Byb3BlcnRp ZXMNCiAgVVNJTkcgYnRyZWUNCiAgKHFuYW1lX2lkKTsNCg0K --------------040008050607010709000605 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance --------------040008050607010709000605--