From: Justin Pryzby <pryzby@telsasoft.com>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Vitaliy Garnashevich <vgarnashevich@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Bitmap scan is undercosted? - boolean correlation
Date: Sat, 2 Dec 2017 22:04:30 -0600
Message-ID: <20171203040430.GC14008@telsasoft.com> (raw)
In-Reply-To: <CAMkU=1yKK0M4W+bNc31PZhH15dzPakSmpr6TH9exT8OUZvmg_w@mail.gmail.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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, jeff.janes@gmail.com, tgl@sss.pgh.pa.us, vgarnashevich@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Bitmap scan is undercosted? - boolean correlation
In-Reply-To: <20171203040430.GC14008@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox