From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
To: Dean Rasheed <dean.a.rasheed@gmail.com>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: Thomas Munro <thomas.munro@enterprisedb.com>
Cc: Mark Dilger <hornschnorter@gmail.com>
Cc: Adrien Nayrat <adrien.nayrat@dalibo.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] PATCH: multivariate histograms and MCV lists
Date: Tue, 11 Sep 2018 00:12:36 +0200
Message-ID: <3fcfd5e5-6849-34e6-22ab-1b62d191bedb@2ndquadrant.com> (raw)
In-Reply-To: <e96fe815-c55f-14f1-06e1-078ac4babfd0@postgrespro.ru>
References: <49348328-1a02-7e45-e50f-e1d4d0243c5c@2ndquadrant.com>
<CAEZATCW=081sEDERoty8knpjwjnCQP+zCh-bFSb8xVxT8KKwHQ@mail.gmail.com>
<7d12b5d1-ac77-67e3-c67e-7fcbd3753e9c@2ndquadrant.com>
<abfb7b66-af22-ac32-a74d-3fcf9bdb5c5c@2ndquadrant.com>
<20180407141201.GA10394@momjian.us>
<CAEZATCXYHJoocBq3yqEG_BNDgLuvDiCxcK9Tg-qbQXrs9pnnHg@mail.gmail.com>
<4416a567-4452-c5fd-7117-5cc7827b5fad@2ndquadrant.com>
<f52637bd-f761-f91b-c8ac-85e0c159807f@2ndquadrant.com>
<CAEZATCVa-86yMzwVYVnrvfagO07YpXPR5X1uisvVasGHd3k-4A@mail.gmail.com>
<d6e5e15d-3f53-239a-7064-bb99f037a0da@2ndquadrant.com>
<CAEZATCUtvWzQ8BPgNcY8u6tnyAf6cvVoVC6RRdqbMuDNHfuS2g@mail.gmail.com>
<f0625b09-bf50-5703-6241-51aea96a3f6a@2ndquadrant.com>
<CAEZATCWORz=bXEPVJNxK49Ws9zeBHqsEdd3JsrrZWMvQ8oXuSA@mail.gmail.com>
<80bc98b2-1c43-1fe8-b316-d447bb68cff2@2ndquadrant.com>
<cbc9e160-c908-105e-cd54-6c0c69152b49@postgrespro.ru>
<abef0126-ff75-850f-0a25-063a8ffa07ad@2ndquadrant.com>
<e96fe815-c55f-14f1-06e1-078ac4babfd0@postgrespro.ru>
Hi,
On 07/18/2018 09:32 AM, Konstantin Knizhnik wrote:
>
>
> On 18.07.2018 02:58, Tomas Vondra wrote:
>> On 07/18/2018 12:41 AM, Konstantin Knizhnik wrote:
>>> ...
>>>
>>> Teodor Sigaev has proposed an alternative approach for calculating
>>> selectivity of multicolumn join or compound index search.
>>> Usually DBA creates compound indexes which can be used by optimizer to
>>> build efficient query execution plan based on index search.
>>> We can stores statistic for compound keys of such indexes and (as it is
>>> done now for functional indexes) and use it to estimate selectivity
>>> of clauses. I have implemented this idea and will publish patch in
>>> separate thread soon.
>>> Now I just want to share some results for the Tomas examples.
>>>
>>> So for Vanilla Postges without extra statistic estimated number of rows
>>> is about 4 times smaller than real.
>>>
>> Can you please post plans with parallelism disabled, and perhaps without
>> the aggregate? Both makes reading the plans unnecessarily difficult ...
>
>
> Sorry, below are plans with disabled parallel execution on simpler
> query(a=1 and b=1):
>
> explain analyze SELECT count(*) FROM foo WHERE a=1 and b=1;
>
>
>
> Vanilla:
>
> Aggregate (cost=11035.86..11035.87 rows=1 width=8) (actual
> time=22.746..22.746 rows=1 loops=1)
> -> Bitmap Heap Scan on foo (cost=291.35..11001.97 rows=13553
> width=0) (actual time=9.055..18.711 rows=50000 loops=1)
> Recheck Cond: ((a = 1) AND (b = 1))
> Heap Blocks: exact=222
> -> Bitmap Index Scan on foo_a_b_idx (cost=0.00..287.96
> rows=13553 width=0) (actual time=9.005..9.005 rows=50000 loops=1)
> Index Cond: ((a = 1) AND (b = 1))
>
>
> ----------------------------------------------------------------------
>
> Vanilla + extra statistic (create statistics ab on a,b from foo):
>
> Aggregate (cost=12693.35..12693.36 rows=1 width=8) (actual
> time=22.747..22.748 rows=1 loops=1)
> -> Bitmap Heap Scan on foo (cost=1490.08..12518.31 rows=70015
> width=0) (actual time=9.399..18.636 rows=50000 loops=1)
> Recheck Cond: ((a = 1) AND (b = 1))
> Heap Blocks: exact=222
> -> Bitmap Index Scan on foo_a_b_idx (cost=0.00..1472.58
> rows=70015 width=0) (actual time=9.341..9.341 rows=50000 loops=1)
> Index Cond: ((a = 1) AND (b = 1))
>
> ----------------------------------------------------------------------
>
> Multicolumn index statistic:
>
> Aggregate (cost=11946.35..11946.36 rows=1 width=8) (actual
> time=25.117..25.117 rows=1 loops=1)
> -> Bitmap Heap Scan on foo (cost=1080.47..11819.51 rows=50736
> width=0) (actual time=11.568..21.362 rows=50000 loops=1)
> Recheck Cond: ((a = 1) AND (b = 1))
> Heap Blocks: exact=222
> -> Bitmap Index Scan on foo_a_b_idx (cost=0.00..1067.79
> rows=50736 width=0) (actual time=11.300..11.300 rows=50000 loops=1)
> Index Cond: ((a = 1) AND (b = 1))
>
I wonder what happened to this alternative approach, relying on stats
from multicolumn indexes ...
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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-hackers@postgresql.org
Cc: tomas.vondra@2ndquadrant.com, k.knizhnik@postgrespro.ru, dean.a.rasheed@gmail.com, bruce@momjian.us, alvherre@2ndquadrant.com, andres@anarazel.de, thomas.munro@enterprisedb.com, hornschnorter@gmail.com, adrien.nayrat@dalibo.com
Subject: Re: [HACKERS] PATCH: multivariate histograms and MCV lists
In-Reply-To: <3fcfd5e5-6849-34e6-22ab-1b62d191bedb@2ndquadrant.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