Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1a7XU3-0007zz-7U for pgsql-performance@arkaria.postgresql.org; Fri, 11 Dec 2015 23:52:19 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1a7XU2-0001Bv-QR for pgsql-performance@arkaria.postgresql.org; Fri, 11 Dec 2015 23:52:18 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1a7XSO-0007kU-AW for pgsql-performance@postgresql.org; Fri, 11 Dec 2015 23:50:36 +0000 Received: from mail-bn1bon0054.outbound.protection.outlook.com ([157.56.111.54] helo=na01-bn1-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1a7XSL-0002lG-5A for pgsql-performance@postgresql.org; Fri, 11 Dec 2015 23:50:35 +0000 Authentication-Results: spf=none (sender IP is ) smtp.mailfrom=Jim.Nasby@bluetreble.com; Received: from aca8637d.ipt.aol.com (162.199.209.141) by SN1PR11MB0687.namprd11.prod.outlook.com (10.163.207.145) with Microsoft SMTP Server (TLS) id 15.1.337.19; Fri, 11 Dec 2015 23:50:28 +0000 Subject: Re: Advise needed for a join query with a where conditional To: ankur_adwyze , References: <1449808702878-5877097.post@n5.nabble.com> From: Jim Nasby Message-ID: <566B6140.9040703@BlueTreble.com> Date: Fri, 11 Dec 2015 17:50:24 -0600 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:38.0) Gecko/20100101 Thunderbird/38.4.0 MIME-Version: 1.0 In-Reply-To: <1449808702878-5877097.post@n5.nabble.com> Content-Type: text/plain; charset="windows-1252"; format=flowed Content-Transfer-Encoding: 7bit X-Originating-IP: [162.199.209.141] X-ClientProxiedBy: CO2PR06CA021.namprd06.prod.outlook.com (10.141.242.21) To SN1PR11MB0687.namprd11.prod.outlook.com (25.163.207.145) X-Microsoft-Exchange-Diagnostics: 1; SN1PR11MB0687; 2:YpBO+5MXWFDyQvU+yj68aNWKx6GGPWSdKeIodSa/I1Ai1erTrDrn+CxhLOe7teXuLXEPGSx05quZgTIff8dTgHv0T64PNLeCQmwaUjKhTDfNzw/udQ0Q7e+YZK6VUmor1l4R3v+U+pu8BhmIngtYnA==; 3:Ch11wwgUzBbESOHdLYn2FoWbXifwl0+ygBKe46SBDyjOpc6vOLbK220yZlQ+xSRNMpS+ap9o5M2JzlAJWf1kx5kX8Jiyqc/n2u0fSo4rGNYtAEC4DHdM9soH8omkIRD1; 25:Ha0kDPlLP6rWYAbBZuAyGM2wuaEyrSAVt887xrbu1Yb1oboJF5pSIjJxeqjlRSrE01ckUiSzhVlIuEUvNX3n6pnmcfYEF7jVEu+fA3SBpDVag/X9RPWtyKn9icnADOJJCSTE95oYmANjrA4+M3zkUSFXdCOcoLVTTjv/QxqyRrzT5Qar713no3fcLYm+yqExjvRGHoW6vPH7p5j91aausmiNIwWt02HH6wJHkLbOBJIVW04d7x9iM6ECcLTqwtAgQePJ0I9XvL+g3gxIby0g0g==; 20:FgdMEEtdOVPTCkyhpuHV0rJqzj0olDVTqTzjQXTrgPdKINECvNm44UWnUEOOeN30dKkVQgctsjtS+kIK8CuwcL6i8taOYRVzTMUpg18GEj+fPcwyAq27pxZObzz+nOXuzHNBM8lQfYW2tcW2h1aztleHEkUiSs9KMFU52EnHjVM= X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:SN1PR11MB0687; X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0; PCL:0; RULEID:(601004)(2401047)(5005006)(520078)(8121501046)(3002001)(10201501046); SRVR:SN1PR11MB0687; BCL:0; PCL:0; RULEID:; SRVR:SN1PR11MB0687; X-Microsoft-Exchange-Diagnostics: 1; SN1PR11MB0687; 4:A/O9G+Fd6iaDUKsAbqsEB1cwaB1na0aEFbXYzHbssDTMiiGIsijBBJcyz1iW+edVt8YJhYkWXjjRgEJv/vjyv/NHcc0ZaouWOjCnxuloZmONA3MAWS/NAhBhOH+zWQ0CaJIVOfg+Q7vocACiNnGiak89cE+0DNhrUk/uRS+C/pIXDpmjMEN9BmM3oj7V4752/2F9dSRu8nNZZdQozreSlny1W4dLlkS1HtI4rYbjhKRs033a4yUaQ9k+AfHkIGmgaEWHvfZAyWPNDoILUZsy2G8/X+mfbaf2RA6S4DvNWOmrfMyP52PBooozGhoHd4wXAYDXfzsGzdcrIG+kN/Qv++NaGzDZEsbeBIjU4Z9e/FYqkpGrOhK4JT+e+my7rjkA X-Forefront-PRVS: 0787459938 X-Forefront-Antispam-Report: SFV:NSPM; SFS:(10009020)(6009001)(377454003)(479174004)(189002)(24454002)(199003)(76176999)(54356999)(19580395003)(87266999)(1096002)(59896002)(47776003)(80316001)(3846002)(65806001)(586003)(6116002)(65956001)(66066001)(87976001)(64126003)(42186005)(86362001)(50986999)(230700001)(83506001)(92566002)(23746002)(106356001)(36756003)(77096005)(5008740100001)(101416001)(105586002)(4001350100001)(40100003)(189998001)(107886002)(5004730100002)(5001770100001)(33656002)(122386002)(50466002)(15975445007)(65816999)(97736004)(2950100001)(85282002)(336755003)(18886065003); DIR:OUT; SFP:1101; SCL:1; SRVR:SN1PR11MB0687; H:aca8637d.ipt.aol.com; FPR:; SPF:None; PTR:InfoNoRecords; MX:1; A:1; LANG:en; Received-SPF: None (protection.outlook.com: bluetreble.com does not designate permitted sender hosts) X-Microsoft-Exchange-Diagnostics: =?Windows-1252?Q?1; SN1PR11MB0687; 23:mgnAX84/aFr0iIf+42YCeJ0JdBma11qvBD5dv?= =?Windows-1252?Q?SH/l5skAH/DFR9EWzvJeP2Tna9tixINS9ptOAplCvi/hK9tyfyNZMSjW?= =?Windows-1252?Q?trJWXGyAlP9oqhU7LjD928hkB8jdee3cBiW8dOaFNaLrb6B1+MHA2rjr?= =?Windows-1252?Q?mmtvodiCi9dGyjry7IPoGDHTNkseDBqNn5izPOawBzpkxo0ZMajYTQez?= =?Windows-1252?Q?vNcHFnPsarBVC7ySeeN06FN4fTiZGDK1lA3r4YDR/Iuka3Eq0xlnL0LJ?= =?Windows-1252?Q?9OGLx/by0sHhf/cKqCzbUY99ysON2DMZNYoZTfb2MZl7isLSdDZAVG/u?= =?Windows-1252?Q?Qd4gp1nOJbKwGq4x+rSKmWTFAcyTWne9kxvyFtNvD3Pk2jNAjScdya6j?= =?Windows-1252?Q?PbRicHUQnKI1XWl3l4Du7nHPGW5ETiyBoLSnllKtjPAyBB2qISbgbj0N?= =?Windows-1252?Q?HIwOCs9ISpCLfH9g38m/MkIhf5Iwf6CLIe+hKv/J++1qxTDjTU3c/UpY?= =?Windows-1252?Q?f3THxMtrbHQxgoLEL52x+HYcdXYF8wBlrqZ0FScICi5jmaohwQwBOn24?= =?Windows-1252?Q?9Lb8zr3TW7Mc2DmDdoIjIDsyVHYZEWPnkwMsgINqAqxKwmJm3nmFOW9v?= =?Windows-1252?Q?ZBXDZm0Df0kBdv8MZ8BGDT8TbtTrhtW4YceOB3s+lAmykJV64smEqrwI?= =?Windows-1252?Q?GTMtjUeCO6nBdvzBZ4Fer2kowfClcrK0A195PcVPv2oKytYkFiKZ88KP?= =?Windows-1252?Q?yTVPjFbLOxA6JFN856MZDH/ZGu8ifECkHnP+ThLp996rtq3VJdOt6r3a?= =?Windows-1252?Q?NxsSGN609/8azx/re56SkQAJu91MjgpC6zWMJoOSe0OcYvMav1ofJLZC?= =?Windows-1252?Q?51fM1CsojUCtKORzlcktr/qifUQwgOGMz6Q3njsE+7BZzLAfNRgvHg+Z?= =?Windows-1252?Q?FNh15Qn0y0dvu4wsZqHLWrW/GotAzBsWytKZ4GdqNAzlzz48XcAydQfY?= =?Windows-1252?Q?5N20r/eDFau4xOoGbro+UXyNfap9ViH8kiCTd1LUYGjso3uIT2fMNIj7?= =?Windows-1252?Q?+LC0JtdZtoFekA5kOhbcBV2qmNgAk+49NLsgAy6H3rGTWFQ5quf8ftJQ?= =?Windows-1252?Q?+yGEMII43yxyNtiIzOAeIVOHvDdfjmxdBi8kjigTEaieCEtCbkhOZ0/z?= =?Windows-1252?Q?29Qo+1fQvO6txN65/FYiGxssNxuW/DikOWuaOFLjCBmpe3/zDLhp2rmx?= =?Windows-1252?Q?/Wp4f6/WR317ksTkG7JaRZ9jNQXdyeUyKEpv8eNssKY2QCHKa8Le7zFh?= =?Windows-1252?Q?OScWLuDEDtwcf+/i1hj1Hc7CdJjQO04kLsjd/j0OtYId2pcUAyIPCzbP?= =?Windows-1252?Q?oHO6lHntNnCbgfAs7AFNvaXWAa6f6pHcTsBTvlwAb37N64djCmO3eo?= =?Windows-1252?Q?=3D?= X-Microsoft-Exchange-Diagnostics: 1; SN1PR11MB0687; 5:Sq/jFPGvkzXHbqstU47M2ufh2Rb+/jfdEJA3xEpc89CJtpv1aDVvwtXJvNn2JQHcPzmxBLzoDOEP91SHTALFB7SseOktUGV51LUbo9QfZMdn+GRFxcyrmX3/DqXD+gQumtdpzNKP4q6u64CPBcuOjQ==; 24:prOwrXrOzUJ40wCce2NSNdoRFPLCRKH9Jm1lsSqo28mb43i6MES9SAwC/G73T2DgXfsM6xd9V37ExjFo+VrV5IDV9cFkqrWf3TPJ1O9LJEg= SpamDiagnosticOutput: 1:23 SpamDiagnosticMetadata: NSPM X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 11 Dec 2015 23:50:28.2694 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: SN1PR11MB0687 X-Pg-Spam-Score: -1.9 (-) 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 On 12/10/15 10:38 PM, ankur_adwyze wrote: > I have a Rails app with Postgres db. For certain reports, I have to join > multiple tables. However, certain join queries are dog slow and I am > wondering if I am missing any index. My guess is that the planner is coming up with a bad estimate. Please post the output of EXPLAIN ANALYZE, preferably via http://explain.depesz.com/ -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance