Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1ffgwd-0000uK-DP for pgsql-hackers@arkaria.postgresql.org; Wed, 18 Jul 2018 07:32:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ffgwb-0000cm-8b for pgsql-hackers@arkaria.postgresql.org; Wed, 18 Jul 2018 07:32:17 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1ffgwa-0000cf-Td for pgsql-hackers@lists.postgresql.org; Wed, 18 Jul 2018 07:32:17 +0000 Received: from mail.postgrespro.ru ([93.174.131.138]) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ffgwW-00082W-Gq for pgsql-hackers@postgresql.org; Wed, 18 Jul 2018 07:32:16 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id A3ACA21C1FFE; Wed, 18 Jul 2018 10:32:10 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru X-Spam-Flag: NO X-Spam-Score: 0 X-Spam-Level: X-Spam-Status: No, score=x tagged_above=-99 required=4 WHITELISTED tests=[] autolearn=unavailable Received: from [192.168.2.78] (unknown [176.56.120.148]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id 7F0C221C1F6B; Wed, 18 Jul 2018 10:32:08 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1531899130; bh=TGOhnPwWVX2GSY9RLiqCyY1oLFm4EcatgNyK29ZpukE=; h=Subject:To:Cc:References:From:Date:In-Reply-To; b=aS0hC5cp4G3KPdJJH2xyoEZYrjNwR+cuZ85ui2vXBr6vJwjt0pUq95HeYveDQB8/P EmQdF7TmhPGpwdFYbuTzkg6+Dl/LSnt9AbFnc3UJL9ufO5LEShs1wOj4gu9NjsNs2f PHlYDFJCTkqyN1R6OfWXN+FIkCh405KofuZcdt/k= Subject: Re: [HACKERS] PATCH: multivariate histograms and MCV lists To: Tomas Vondra , Dean Rasheed Cc: Bruce Momjian , Alvaro Herrera , Andres Freund , Thomas Munro , Mark Dilger , Adrien Nayrat , Pg Hackers References: <49348328-1a02-7e45-e50f-e1d4d0243c5c@2ndquadrant.com> <7d12b5d1-ac77-67e3-c67e-7fcbd3753e9c@2ndquadrant.com> <20180407141201.GA10394@momjian.us> <4416a567-4452-c5fd-7117-5cc7827b5fad@2ndquadrant.com> <80bc98b2-1c43-1fe8-b316-d447bb68cff2@2ndquadrant.com> From: Konstantin Knizhnik Message-ID: Date: Wed, 18 Jul 2018 10:32:06 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.9.1 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk 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))