Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from
 <pgsql-performance-owner+M54735=pgsql-performance-ar@postgresql.org>)
	id 1cUDyJ-00075n-2x
	for pgsql-performance@arkaria.postgresql.org; Thu, 19 Jan 2017 14:45:51 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.84_2)
	(envelope-from
 <pgsql-performance-owner+M54735=pgsql-performance-ar@postgresql.org>)
	id 1cUDyI-0003TI-EN
	for pgsql-performance@arkaria.postgresql.org; Thu, 19 Jan 2017 14:45:50 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.84_2)
	(envelope-from <gustavo.montesino@trtsp.jus.br>)
	id 1cUDyH-0003Sy-ON
	for pgsql-performance@postgresql.org; Thu, 19 Jan 2017 14:45:49 +0000
Received: from as02.trtsp.jus.br ([177.85.155.221])
	by makus.postgresql.org with smtp (Exim 4.84_2)
	(envelope-from <gustavo.montesino@trtsp.jus.br>)
	id 1cUDyE-0004uO-CL
	for pgsql-performance@postgresql.org; Thu, 19 Jan 2017 14:45:48 +0000
Received: from localhost (localhost [127.0.0.1])
	by as02.trtsp.jus.br (Vertical IP Secure Mail) with ESMTP id C7F9E117626F;
	Thu, 19 Jan 2017 12:45:42 -0200 (BRST)
X-VERTICAL-IP-Quarantine-ID: <zMCfSwQfZmTj>
X-VERTICAL-IP-SCANNED: Vertical IP Secure Mail
X-VERTICAL-IP-Spam-Score: -3.977
Received: from correio.trtsp.jus.br (correio4.trtsp.jus.br [10.2.1.40])
	by as02-out.trtsp.jus.br (Vertical IP Secure Mail) with SMTP id 1CF2C149C3AC;
	Thu, 19 Jan 2017 12:45:42 -0200 (BRST)
Received: from [10.2.71.120] (unknown [10.2.71.120])
	by correio.trtsp.jus.br (Postfix) with ESMTPSA id 8DA2C2C1AEB;
	Thu, 19 Jan 2017 12:45:43 -0200 (BRST)
Subject: Re: Optimization inner join
To: Tom Lane <tgl@sss.pgh.pa.us>
References: <7ad1128e-7fdb-9976-823c-d4abdf554fa2@gmail.com>
 <497e3581-bcce-4efc-b281-6b17a93479c4@mtasv.net>
 <2cd4d430-54ad-4ab8-baae-b597c4a18c43@gmail.com>
 <5880BDEA.9050804@trtsp.jus.br> <16727.1484835221@sss.pgh.pa.us>
Cc: Clailson <clailson.dba@gmail.com>, phillip@couto.in,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
From: Gustavo Rezende Montesino <gustavo.montesino@trtsp.jus.br>
Message-ID: <5880D11A.7060900@trtsp.jus.br>
Date: Thu, 19 Jan 2017 12:45:46 -0200
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:38.0) Gecko/20100101
 Thunderbird/38.6.0
MIME-Version: 1.0
In-Reply-To: <16727.1484835221@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: quoted-printable
X-Pg-Spam-Score: -5.1 (-----)
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

Em 19/01/2017 12:13, Tom Lane escreveu:
> Gustavo Rezende Montesino <gustavo.montesino@trtsp.jus.br> writes:
>> Being the client in question, I would like to make a little remark: What
>> we thought could be optimized here at first is on the row estimate of
>> the index scan; which could take null_frac into account. To put things
>> into perspective, our similar case in production has a table with 6
>> million lines where only 9.5k aren=C2=B4t null for the join field, an the
>> over-estimation is throwing away good plans (like ~150ms execution time)
>> in favor of pretty bad ones (~80s execution time).
> Please provide a concrete test case for that.  AFAIK the null fraction
> should be accounted for in join size estimates.  Here's a little test
> case showing that it is:

Hello,

Expanding a little on you example:

postgres=3D# create table t1 as select generate_series(1,1000000) as f1;
SELECT 1000000
postgres=3D# create table t2 as select generate_series(1,1000000) as f1;
SELECT 1000000
postgres=3D# insert into t2 select null from generate_series(1,1000000);
INSERT 0 1000000
postgres=3D# create index on t1(f1);
CREATE INDEX
postgres=3D# create index on t2(f1);
CREATE INDEX
postgres=3D# analyze t1;
ANALYZE
postgres=3D# analyze t2;
ANALYZE
postgres=3D# explain select * from t1,t2 where t1.f1=3Dt2.f1;
                                        QUERY PLAN
---------------------------------------------------------------------------=
--------------
  Merge Join  (cost=3D2.68..59298.81 rows=3D499433 width=3D8)
    Merge Cond: (t1.f1 =3D t2.f1)
    ->  Index Only Scan using t1_f1_idx on t1 (cost=3D0.42..24916.42=20
rows=3D1000000 width=3D4)
    ->  Index Only Scan using t2_f1_idx on t2 (cost=3D0.43..48837.43=20
rows=3D2000000 width=3D4)
(4 rows)
postgres=3D# explain select * from t1,t2 where t1.f1=3Dt2.f1 and t2.f1 is=20
not null;
                                        QUERY PLAN
---------------------------------------------------------------------------=
--------------
  Merge Join  (cost=3D1.85..44588.02 rows=3D249434 width=3D8)
    Merge Cond: (t1.f1 =3D t2.f1)
    ->  Index Only Scan using t1_f1_idx on t1 (cost=3D0.42..24916.42=20
rows=3D1000000 width=3D4)
    ->  Index Only Scan using t2_f1_idx on t2 (cost=3D0.43..26890.60=20
rows=3D998867 width=3D4)
          Index Cond: (f1 IS NOT NULL)
(5 rows)


Notice the difference in the estimated costs. In our real case this=20
difference leads
to a (very) bad plan choice.

BTW, execution itself is indeed faster without the not null clause.

These tests where on 9.3, but our production with the "real" case is in=20
9.6. Behavior seems
to be the same on both.


Regards,

Gustavo R. Montesino



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