Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1UylO1-00083M-0y for pgsql-performance@arkaria.postgresql.org; Mon, 15 Jul 2013 16:12:29 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1UylO0-0004m3-5X for pgsql-performance@arkaria.postgresql.org; Mon, 15 Jul 2013 16:12:28 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1UylNx-0004lu-W2 for pgsql-performance@postgresql.org; Mon, 15 Jul 2013 16:12:26 +0000 Received: from mail-oa0-f52.google.com ([209.85.219.52]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1UylNu-0003VV-Pv for pgsql-performance@postgresql.org; Mon, 15 Jul 2013 16:12:24 +0000 Received: by mail-oa0-f52.google.com with SMTP id g12so15742046oah.25 for ; Mon, 15 Jul 2013 09:12:21 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=message-id:date:from:user-agent:mime-version:to:subject:references :in-reply-to:content-type:content-transfer-encoding :x-gm-message-state; bh=+FqogC4HdRgXqvGOc6uCT09GQzmY6WvxpmcNGeS5UCQ=; b=lcSPZ1cPm+WzqloG09pKK0DO5AtFxU9RwOi45pudJoc+e8uNenSW9TXDpHX/2r2WnZ GVAApejfoqbFoTt767WcF47stDfK2NroBQlGEtjpprpBWpBUSJ2VFq1p71pP8KwMD8XC Einh9RatYo9B6vMfWq8FOo6f/msedkhbNrKXun37PCGVhDkCt6W6uAjHEOfPYuSwDz20 vwpj2NUdACMpILiRvH314pxfUg/iwG6g00YOVuQBwITCAVZ83EALuvn0yhFGoeTqtuk3 y9o00jL2uI82O5L24Eso1a2d9lccvEX5Ii+anea1scPHwtZADoeg20tEwLQJNlqWwIAO VASA== X-Received: by 10.182.72.170 with SMTP id e10mr17040748obv.62.1373904741573; Mon, 15 Jul 2013 09:12:21 -0700 (PDT) Received: from brianf.consistentstate.com (216-98-193-98.static.forethought.net. [216.98.193.98]) by mx.google.com with ESMTPSA id tv3sm68658586obb.8.2013.07.15.09.12.20 for (version=TLSv1 cipher=RC4-SHA bits=128/128); Mon, 15 Jul 2013 09:12:21 -0700 (PDT) Message-ID: <51E41F64.7090102@consistentstate.com> Date: Mon, 15 Jul 2013 10:12:20 -0600 From: Brian Fehrle User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:17.0) Gecko/20130311 Thunderbird/17.0.4 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Trying to eliminate union and sort References: <51DEF924.1040809@consistentstate.com> <51DF51DA.7010509@agliodbs.com> <51E082D8.4030503@consistentstate.com> <51E086A1.2080504@agliodbs.com> In-Reply-To: <51E086A1.2080504@agliodbs.com> Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 7bit X-Gm-Message-State: ALoCoQmnsG7BxXmf+PjvcB840Tmi/8KBi/j0rQtljGVGOquG33n/O41DZk5QQx0aEp8JT6OXAxhd X-Pg-Spam-Score: -0.7 (/) 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 07/12/2013 04:43 PM, Josh Berkus wrote: >> As for the counts on the tables: >> table1 3,653,472 >> table2 2,191,314 >> table3 25,676,589 >> >> I think it's safe to assume right now that any resulting joins are not >> one-to-one > Hmmm? How is doing a subselect in the SELECT clause even working, then? > Oh my, this is sad. the query in all returns 9,955,729 rows, so the sub queries are run on each of these resulting rows, however in this entire result set, subquery 1 returns 16 distinct rows, subquery 2 returns 63 different rows, but those sub queries are run over 10 million times to return these few distinct rows. So it's running many times, but returning the same small set of data over and over again. - Brian F -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance