Date: Sat, 2 Dec 2017 22:04:30 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
	Vitaliy Garnashevich <vgarnashevich@gmail.com>,
	pgsql-performance@lists.postgresql.org
Subject: Re: Bitmap scan is undercosted? - boolean correlation
Message-ID: <20171203040430.GC14008@telsasoft.com>
References: <73a4936d-2814-dc08-ed0c-978f76f435b0@gmail.com>
 <20171201183427.GK18413@telsasoft.com>
 <20171201231104.GN18413@telsasoft.com>
 <d2b6a335-8c62-4983-3b5b-c8a75cf37d6f@gmail.com>
 <CAMkU=1wkjV3CjWE=Cksp_qSZhNbxTOcyc=dDuwhnDzVxGMu6qQ@mail.gmail.com>
 <f8edc163-7ebe-cdb8-d068-685355045f24@gmail.com>
 <CAMkU=1zGL7UZpaycNH1Zs=K-TuaJx4P5EmP2mZM3DHzy9JinHg@mail.gmail.com>
 <18188.1512258290@sss.pgh.pa.us>
 <CAMkU=1yKK0M4W+bNc31PZhH15dzPakSmpr6TH9exT8OUZvmg_w@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAMkU=1yKK0M4W+bNc31PZhH15dzPakSmpr6TH9exT8OUZvmg_w@mail.gmail.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

On Sat, Dec 02, 2017 at 05:27:51PM -0800, Jeff Janes wrote:
> I think the non-extended stats code also has trouble with booleans.
> pg_stats gives me a correlation  of 0.8 or higher for the flag column.

It's not due to the boolean though; you see the same thing if you do:
CREATE INDEX aaa_f ON aaa((flag::text));
ANALYZE aaa;
correlation | 0.81193

or:
ALTER TABLE aaa ADD flag2 int; UPDATE aaa SET flag2= flag::int
correlation | 0.81193

I think it's caused by having so few (2) values to correlate.

most_common_vals       | {f,t}
most_common_freqs      | {0.9014,0.0986}
correlation            | 0.822792

It thinks there's somewhat-high correlation since it gets a list of x and y
values (integer positions by logical and physical sort order) and 90% of the x
list (logical value) are the same value ('t'), and the CTIDs are in order on
the new index, so 90% of the values are 100% correlated.

It improves (by which I mean here that it spits out a lower number) if it's not
a 90/10 split:

CREATE TABLE aaa5 AS SELECT (id%100)::int num, (id%10>5)::bool flag FROM generate_series(1, 10000000) id;
CREATE INDEX ON aaa5 (flag);

tablename   | aaa5
attname     | flag
correlation | 0.522184

Justin