pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
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: Wed, 18 Jul 2018 01:58:06 +0200
Message-ID: <abef0126-ff75-850f-0a25-063a8ffa07ad@2ndquadrant.com> (raw)
In-Reply-To: <cbc9e160-c908-105e-cd54-6c0c69152b49@postgrespro.ru>
References: <49348328-1a02-7e45-e50f-e1d4d0243c5c@2ndquadrant.com>
	<b423b2fe-8aff-d64c-152a-8c77cf45faa7@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>


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 ...

>  postgres=# explain analyze SELECT count(*) FROM foo WHERE a=1 and (b=1
> or b=2);
> QUERY PLAN
> 
> --------------------------------------------------------------------------------------------------------------------------------------------
> 
>  Aggregate  (cost=10964.76..10964.77 rows=1 width=8) (actual
> time=49.251..49.251 rows=1 loops=1)
>    ->  Bitmap Heap Scan on foo  (cost=513.60..10906.48 rows=23310
> width=0) (actual time=17.368..39.928 rows=90000 loops=1)

ok, 23k vs. 90k

> 
> If we add statistic for a and b  columns:
> 
>      create statistics ab on a,b from foo;
>      analyze foo;
> 
> then expected results is about 30% larger then real: 120k vs 90k:
> 

Eh? The plan however says 9k vs. 30k ...

>                ->  Parallel Bitmap Heap Scan on foo
> (cost=2561.33..13424.24 rows=9063 width=0) (actual time=15.551..26.057
> rows=30000 loops=3)

...

> With compound index statistic estimation is almost equal to real value:
> 
>    ->  Bitmap Heap Scan on foo  (cost=1880.20..13411.66 rows=23310
> width=0) (actual time=38.776..61.050 rows=90000 loops=1)

Well, this says 23k vs. 90k.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services




view thread (167+ messages)  latest in thread

Message-ID: <abef0126-ff75-850f-0a25-063a8ffa07ad@2ndquadrant.com>
Permalink:  ../abef0126-ff75-850f-0a25-063a8ffa07ad@2ndquadrant.com/
Also on:    postgresql.org/message-id/abef0126-ff75-850f-0a25-063a8ffa07ad@2ndquadrant.com

 · 

reply
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: <abef0126-ff75-850f-0a25-063a8ffa07ad@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