Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M51081=pgsql-performance-ar@postgresql.org>)
 id 1W0BN8-0007ft-O8
 for pgsql-performance@arkaria.postgresql.org; Mon, 06 Jan 2014 14:41:42 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M51081=pgsql-performance-ar@postgresql.org>)
 id 1W0BN8-0001ff-2q
 for pgsql-performance@arkaria.postgresql.org; Mon, 06 Jan 2014 14:41:42 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <cousinmarc@gmail.com>) id 1W0BN7-0001fZ-BW
 for pgsql-performance@postgresql.org; Mon, 06 Jan 2014 14:41:41 +0000
Received: from mail-wi0-x231.google.com ([2a00:1450:400c:c05::231])
 by magus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <cousinmarc@gmail.com>) id 1W0BN4-0007h7-Ko
 for pgsql-performance@postgresql.org; Mon, 06 Jan 2014 14:41:40 +0000
Received: by mail-wi0-f177.google.com with SMTP id cc10so2889745wib.10
 for <pgsql-performance@postgresql.org>; Mon, 06 Jan 2014 06:41:37 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=message-id:date:from:user-agent:mime-version:to:cc:subject
 :references:in-reply-to:content-type:content-transfer-encoding;
 bh=/I8teK2TLxgRBjIldC1esvsJBTp/Bsale83wKkjjpMA=;
 b=OdMSo/WX6OyWzDPYsIAxDTFiOMucRBzYXFjxOO4rM+OfC1pe/t2khEZTYFDzUHUpTU
 68BdR6FoBUum3OOHFwJRDvxdOirDXFVaw0qUhZVtGoe7daDYovhRks3/hgXZ/kH1PNeP
 4M67MjyTtQErXefYpyro8/4kDqWVnDg2JL4G4zm4sIBtSomxpOPGeSuRf3ZXiTdtnXRM
 Os++y5Zp8WHKXbyJcYe4schFC0F9+sq6+AJa6QIQm3RFhkZYroLFjEYknBDsbUvB81Ra
 pMbGrqNmQk0K1LPap6u6ksS5wi8DG3jDSiT9M1ehi9RTb0y10Mrpl+fe82odCGFZcOtD
 gjnQ==
X-Received: by 10.194.57.19 with SMTP id e19mr659093wjq.93.1389019297818;
 Mon, 06 Jan 2014 06:41:37 -0800 (PST)
Received: from [192.168.0.18] (gev44-1-78-228-108-65.fbx.proxad.net.
 [78.228.108.65])
 by mx.google.com with ESMTPSA id ci4sm42778015wjc.21.2014.01.06.06.41.36
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Mon, 06 Jan 2014 06:41:36 -0800 (PST)
Message-ID: <52CAC09F.50209@gmail.com>
Date: Mon, 06 Jan 2014 15:41:35 +0100
From: Marc Cousin <cousinmarc@gmail.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.2.0
MIME-Version: 1.0
To: Jeff Janes <jeff.janes@gmail.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: query plan not optimal
References: <52B311C4.1070108@gmail.com>
	<CAMkU=1w1Db1UPySNkuDabH4TyCO=khRkYaary11fSZLpqO=EOg@mail.gmail.com>
	<52B34240.4020905@gmail.com>
 <CAMkU=1zyT5HM6npOR0jF5LyAGYtgh9KCfLWyfYLLG8fFxqK=zA@mail.gmail.com>
In-Reply-To: 
 <CAMkU=1zyT5HM6npOR0jF5LyAGYtgh9KCfLWyfYLLG8fFxqK=zA@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -2.0 (--)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 29/12/2013 19:51, Jeff Janes wrote:
> On Thursday, December 19, 2013, Marc Cousin wrote:
> 
> 
> 
>     Yeah, I had forgotten to set it up correctly on this test environment
>     (its value is correctly set in production environments). Putting it to a
>     few gigabytes here gives me this cost:
> 
>     bacula=# explain select pathid, filename from batch join path using
>     (path);
>                                      QUERY PLAN
>     ----------------------------------------------------------------------------
>      Nested Loop  (cost=0.56..2083904.10 rows=479020 width=26)
>        ->  Seq Scan on batch  (cost=0.00..11727.20 rows=479020 width=85)
>        ->  Index Scan using idx_path on path  (cost=0.56..4.32 rows=1
>     width=16)
>              Index Cond: (path = batch.path)
>     (4 lignes)
> 
>     It still chooses the hash join though, but by a smaller margin.
> 
> 
> This is still a tangent from your original point, but if I create index
> on path (path, pathid), then I can get an index only scan.  This
> actually is not much faster when everything is already cached, but the
> planner thinks it will be about 2x faster because it assumes the vm
> block accesses are free.  So this might be enough to tip it over for you.

Yeah, still a tangent :)

Many bacula users don't have index only scans (the one I was having
trouble with for example), as they are still using an older than 9.2
PostgreSQL version.

> 
> 
>     And it still only will access a very small part of path (always the same
>     5000 records) during the query, which isn't accounted for in the cost if
>     I understand correctly ?
> 
> 
> I think you are correct, that it doesn't take account of ndistinct being
> 10 to 100 fold less than ntuples on the outer loop, which theoretically
> could propagate down to the table size used in connection with
> effecitve_cache_size.
> 
> It seems to me the cost of the hash join is being greatly
> underestimated, which I think is more important than the nested loop
> being overestimated.  (And in my hands, the merge join is actually the
> winner both in the planner and in reality, but I suspect this is because
> all of your fake paths are lexically greater than all of the real paths)
Yes, probably.

> 
> Also, you talked earlier about cheating the planner by lowering
> random_page_cost.  But why is that cheating?  If caching means the cost
> is truly lower... 
It feels like cheating, as I feel I'm compensating for what looks like a
"bad" estimate of the cost: the nested loop is very fast, even if
nothing is cached at the beginning. We could put the *_page_cost
hardcoded to low values in bacula's code for this query, but it is not
that good to put it in postgresql.conf as we currently do, as some other
queries are suffering from those very low costs. Anyway, it would be
even better if it wasn't needed at all, hence this post :)


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance