Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YYbu6-00029e-TU for pgsql-performance@arkaria.postgresql.org; Thu, 19 Mar 2015 14:58:35 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YYbu6-0005x7-0M for pgsql-performance@arkaria.postgresql.org; Thu, 19 Mar 2015 14:58:34 +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 1YYbu4-0005ww-Qe for pgsql-performance@postgresql.org; Thu, 19 Mar 2015 14:58:32 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YYbu1-0004R4-Tl for pgsql-performance@postgresql.org; Thu, 19 Mar 2015 14:58:31 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id t2JEwQAa031349; Thu, 19 Mar 2015 10:58:26 -0400 From: Tom Lane To: Jake Magner cc: pgsql-performance@postgresql.org Subject: Re: Merge Join chooses very slow index scan In-reply-to: <30571.1426775332@sss.pgh.pa.us> References: <1426746214298-5842523.post@n5.nabble.com> <30571.1426775332@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Thu, 19 Mar 2015 10:28:52 -0400" Date: Thu, 19 Mar 2015 10:58:26 -0400 Message-ID: <31348.1426777106@sss.pgh.pa.us> 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 I wrote: > [ assorted possible workarounds ] Actually, an easy fix might be to create a 2-column index on usagestats(type, tid). I think the planner should be able to use that to produce sorted output for the mergejoin, and you'd get the best of both worlds, because the indexscan will stop immediately when it's exhausted the rows with type = 'vehicle'. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance