Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M51010=pgsql-performance-ar@postgresql.org>)
 id 1Vtipe-0003wM-IH
 for pgsql-performance@arkaria.postgresql.org; Thu, 19 Dec 2013 19:00:26 +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+M51010=pgsql-performance-ar@postgresql.org>)
 id 1Vtipe-0005K8-1h
 for pgsql-performance@arkaria.postgresql.org; Thu, 19 Dec 2013 19:00:26 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <cousinmarc@gmail.com>) id 1Vtipd-0005K2-2l
 for pgsql-performance@postgresql.org; Thu, 19 Dec 2013 19:00:25 +0000
Received: from mail-wi0-x22f.google.com ([2a00:1450:400c:c05::22f])
 by makus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <cousinmarc@gmail.com>) id 1VtipZ-0001dz-JP
 for pgsql-performance@postgresql.org; Thu, 19 Dec 2013 19:00:24 +0000
Received: by mail-wi0-f175.google.com with SMTP id hi5so7362819wib.2
 for <pgsql-performance@postgresql.org>; Thu, 19 Dec 2013 11:00:19 -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=4IVLXSPuq/MRI2GzDT2dfst8AolsbuaSk4avbpBYKLY=;
 b=myAxGgaK896MLv+d5nMePy2XalaUOAux42oE90utqr75Kh+6AG5ACfnIpOrgw1nfWH
 Hng+5MACy09BJgCWv+/P1txBPih56a8S/i785sjjyUx6LKF11WEafNc+tCJLrZuhKSKk
 5V/OjSUQU0pv4Axy24YlYAEpaJXWPsqNDRHwYCXP5XeFUrGp4L62WjDDjhUSd2vWPKQU
 eAp1LU6y7uVdBabAD6k2kF+JC0B2SQ0DNDzVinIL19//yj9g+LP+dHmKM/QIip6ifNyk
 PM5cewCFR19d7LwsnUQcygOwBB6glAcyA6SEWPeUxkN+YvL/iYI45P2UOs8lgbiDtYww
 eUog==
X-Received: by 10.180.100.70 with SMTP id ew6mr4365026wib.9.1387479619739;
 Thu, 19 Dec 2013 11:00:19 -0800 (PST)
Received: from [192.168.0.47] (gev44-1-78-228-108-65.fbx.proxad.net.
 [78.228.108.65])
 by mx.google.com with ESMTPSA id ko3sm694286wjb.23.2013.12.19.11.00.17
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Thu, 19 Dec 2013 11:00:18 -0800 (PST)
Message-ID: <52B34240.4020905@gmail.com>
Date: Thu, 19 Dec 2013 20:00:16 +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>
In-Reply-To: 
 <CAMkU=1w1Db1UPySNkuDabH4TyCO=khRkYaary11fSZLpqO=EOg@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 19/12/2013 19:33, Jeff Janes wrote:
>                                                                 QUERY PLAN
>     ----------------------------------------------------------------------------------------------------------------------------------
>      Nested Loop  (cost=0.56..4001768.10 rows=479020 width=26) (actual
>     time=2.303..15371.237 rows=479020 loops=1)
>        Output: path.pathid, batch.filename
>        Buffers: shared hit=2403958 read=7539
>        ->  Seq Scan on public.batch  (cost=0.00..11727.20 rows=479020
>     width=85) (actual time=0.340..160.142 rows=479020 loops=1)
>              Output: batch.path, batch.filename
>              Buffers: shared read=6937
>        ->  Index Scan using idx_path on public.path  (cost=0.56..8.32
>     rows=1 width=16) (actual time=0.030..0.031 rows=1 loops=479020)
>              Output: path.pathid, path.path
>              Index Cond: (path.path = batch.path)
>              Buffers: shared hit=2403958 read=602
>      Total runtime: 15439.043 ms
> 
> 
>     As you can see, more than twice as fast, and a very high hit ratio
>     on the path table, even if we start from a cold cache (I did, here,
>     both PostgreSQL and OS). We have an excellent hit ratio because the
>     batch table contains few different path (several files in a
>     directory), and is already quite clustered, as it comes from a
>     backup, which is of course performed directory by directory.
> 
> 
> What is your effective_cache_size set to?
> 
> Cheers,
> 
> Jeff
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.

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 ?


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