Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eL1un-0004zn-Sl for pgsql-performance@arkaria.postgresql.org; Sat, 02 Dec 2017 07:08:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eL1un-00007R-7M for pgsql-performance@arkaria.postgresql.org; Sat, 02 Dec 2017 07:08:45 +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.84_2) (envelope-from ) id 1eL1um-00007I-T0 for pgsql-performance@lists.postgresql.org; Sat, 02 Dec 2017 07:08:45 +0000 Received: from mail-lf0-x241.google.com ([2a00:1450:4010:c07::241]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1eL1uj-000302-7v for pgsql-performance@lists.postgresql.org; Sat, 02 Dec 2017 07:08:44 +0000 Received: by mail-lf0-x241.google.com with SMTP id a12so14001961lfe.4 for ; Fri, 01 Dec 2017 23:08:40 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:cc:references:from:message-id:date:user-agent :mime-version:in-reply-to:content-language; bh=xF29KefDISsoh3QSBcMFLMNPnGHULKXN6Xkg4WCIhJ4=; b=BVHAksjQ3KO7T7PTVmYqb6d3moDRLSClrvgXT13YgsIt6gjL9kFh68kgQytwRgKl04 vleGVLDrfI30qv5WhSVib6Sud3MUOJSXHDe6fIl9yYf4vhTSu5wiH04VYMBFHqtJFni9 221OGrHOBDzoFtYkd4mZ4fL7E/qAD2vwY4VaXf3ui1l3f48Z33nCnI+j7TiIgjJHR7RQ mu9CUm4qXCYftP+K2jvCNaJGNTBQw00AnwroRbzt3pw2PKHWBF2WapB50jsuXkRoMb9t uXE0qIcgdUCE7b+w7GW4QmaNJY4O3P3Bf4vIzu/M1WhlXuzdJlgpTHN7W5w0BonSCB/7 IdLA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:cc:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=xF29KefDISsoh3QSBcMFLMNPnGHULKXN6Xkg4WCIhJ4=; b=phwPVsUGA6OQRqfpKlhg10/jp12PSrjMTDOk/95bzM6rk0BbZmE06feX/CZSdckggv GOqddVijfDtINxK+hkOAHIMM1xrrtNNa4sBktutwZO8fPqebosJ14lmJ0q+BV/aLju31 tNojnJRepvRD/TZ8+pR0UMWE4mcnz7ndy3FXbNsNxWZYIYraHsvM9oaU8FhACQWat89k W4w21l7Hycj0WVxEUhtk4JqeyvtODwKd0jEjn/d/ZUiglWOYrxi2tPNhCVC4lXEkGcBR PfBsccw85yK1wgx8YFKStoZBBH9P97giKYJ+ojYbcx56Q4w1jYrnq3ndkNTYz4J1gkk5 sC9g== X-Gm-Message-State: AJaThX6MKIUICx3QqFdV/ep6LUD6iuoZqLIqb9GzieopEnpRnzLNY/MQ Yk1wlsBvj2WJViSeDsudWCST6Mgc X-Google-Smtp-Source: AGs4zMYJcMnEomXfyJldkvIUFjCmTiaowDKya7LHN0NzjZMk0S5FE6zXD42Ph8MLyC7Hp1OhP4q8WQ== X-Received: by 10.46.101.141 with SMTP id e13mr5804602ljf.127.1512198518599; Fri, 01 Dec 2017 23:08:38 -0800 (PST) Received: from [192.168.1.151] ([178.54.41.83]) by smtp.gmail.com with ESMTPSA id w7sm1673462ljd.95.2017.12.01.23.08.37 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 01 Dec 2017 23:08:37 -0800 (PST) Subject: Re: Bitmap scan is undercosted? To: Jeff Janes Cc: Justin Pryzby , pgsql-performance@lists.postgresql.org References: <73a4936d-2814-dc08-ed0c-978f76f435b0@gmail.com> <20171201183427.GK18413@telsasoft.com> <20171201231104.GN18413@telsasoft.com> From: Vitaliy Garnashevich Message-ID: Date: Sat, 2 Dec 2017 09:08:38 +0200 User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:52.0) Gecko/20100101 Thunderbird/52.5.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------252EABB56E5295AE28B396BB" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------252EABB56E5295AE28B396BB Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 02/12/2017 07:51, Jeff Janes wrote: > On Fri, Dec 1, 2017 at 3:54 PM, Vitaliy Garnashevich > > wrote: > > On 02/12/2017 01:11, Justin Pryzby wrote: > > I tried to reproduce this issue and couldn't, under PG95 and 10.1: > > On Fri, Dec 01, 2017 at 12:34:27PM -0600, Justin Pryzby wrote: > > On Fri, Dec 01, 2017 at 07:40:08PM +0200, Vitaliy > Garnashevich wrote: > > We recently had an issue in production, where a bitmap > scan was chosen > instead of an index scan. Despite being 30x slower, > the bitmap scan had > about the same cost as the index scan. > drop table if exists aaa; > create table aaa as select (id%100)::int num, > (id%10=1)::bool flag from > generate_series(1, 10000000) id; > create index i1 on aaa  (num); > create index i2 on aaa  (flag); > analyze aaa; > > What is: > effective_io_concurrency > max_parallel_workers_per_gather (I gather you don't have this) > > effective_io_concurrency = 0 > max_parallel_workers_per_gather = 0 > > Did you notice random_page_cost = 1.5 ? > > > For the aaa.num = 39 case, the faster index scan actually does hit 15 > times more buffers than the bitmap scan does.  While 1.5 is lot lower > than 4.0, it is still much higher than the true cost of reading a page > from the buffer cache.   This why the index scan is getting punished.  > You could lower random_page_cost and seq_page_cost to 0, to remove > those considerations.  (I'm not saying you should do this on your > production system, but rather you should do it as a way to investigate > the issue.  But it might make sense on production as well) seq_page_cost = 1.0 random_page_cost = 1.0* *explain analyze select * from aaa where num = 2 and flag = true; Bitmap Heap Scan on aaa  (cost=11536.74..20856.96 rows=10257 width=5) (actual time=108.338..108.338 rows=0 loops=1)   ->  BitmapAnd  (cost=11536.74..11536.74 rows=10257 width=0) (actual time=108.226..108.226 rows=0 loops=1)         ->  Bitmap Index Scan on i1  (cost=0.00..1025.43 rows=100000 width=0) (actual time=18.563..18.563 rows=100000 loops=1)         ->  Bitmap Index Scan on i2  (cost=0.00..10505.93 rows=1025666 width=0) (actual time=78.493..78.493 rows=1000000 loops=1) Index Scan using i1 on aaa  (cost=0.44..44663.58 rows=10257 width=5) (actual time=51.264..51.264 rows=0 loops=1) Here I've used the filter num = 2, which produces rows=0 at BitmapAnd, and thus avoids a lot of work at "Bitmap Heap Scan" node, while still leaving about the same proportion in bitmap vs index - the bitmap is twice slower but twice less costly. It does not matter much which value to use for the filter, if it's other than num = 1. seq_page_cost = 0.0 random_page_cost = 0.0 explain analyze select * from aaa where num = 2 and flag = true; Bitmap Heap Scan on aaa  (cost=753.00..2003.00 rows=10257 width=5) (actual time=82.212..82.212 rows=0 loops=1)   ->  Bitmap Index Scan on i1  (cost=0.00..750.43 rows=100000 width=0) (actual time=17.401..17.401 rows=100000 loops=1) Index Scan using i1 on aaa  (cost=0.44..1750.43 rows=10257 width=5) (actual time=49.766..49.766 rows=0 loops=1) The bitmap plan was reduced to use only one bitmap scan, and finally it costs more than the index plan. But I doubt that the settings seq_page_cost = random_page_cost = 0.0 should actually be used. Probably it should be instead something like 1.0/1.0 or 1.0/1.1, but other costs increased, to have more weight. # x4 tuple/operator costs - bitmap scan still a bit cheaper set seq_page_cost = 1.0; set random_page_cost = 1.0; set cpu_tuple_cost = 0.04; set cpu_index_tuple_cost = 0.02; set cpu_operator_cost = 0.01; Bitmap Heap Scan on aaa  (cost=36882.97..46587.82 rows=10257 width=5) (actual time=106.045..106.045 rows=0 loops=1)   ->  BitmapAnd  (cost=36882.97..36882.97 rows=10257 width=0) (actual time=105.966..105.966 rows=0 loops=1)         ->  Bitmap Index Scan on i1  (cost=0.00..3276.74 rows=100000 width=0) (actual time=15.977..15.977 rows=100000 loops=1)         ->  Bitmap Index Scan on i2  (cost=0.00..33584.72 rows=1025666 width=0) (actual time=79.208..79.208 rows=1000000 loops=1) Index Scan using i1 on aaa  (cost=1.74..49914.89 rows=10257 width=5) (actual time=50.144..50.144 rows=0 loops=1) # x5 tuple/operator costs - switched to single bitmap index scan, but now it costs more than the index scan set seq_page_cost = 1.0; set random_page_cost = 1.0; set cpu_tuple_cost = 0.05; set cpu_index_tuple_cost = 0.025; set cpu_operator_cost = 0.0125; Bitmap Heap Scan on aaa  (cost=4040.00..54538.00 rows=10257 width=5) (actual time=82.338..82.338 rows=0 loops=1)   ->  Bitmap Index Scan on i1  (cost=0.00..4027.18 rows=100000 width=0) (actual time=19.541..19.541 rows=100000 loops=1) Index Scan using i1 on aaa  (cost=2.17..51665.32 rows=10257 width=5) (actual time=49.545..49.545 rows=0 loops=1) I've also tried seq_page_cost = 1.0, random_page_cost = 1.1, but that would require more than x10 increase in tuple/operator costs, to make bitmap more costly than index. > > > For this test I'm using SSD and Windows (if that matters). On > production we also use SSD, hence lower random_page_cost. But with > the default random_page_cost=4.0, the difference in cost between > the index scan plan and the bitmap scan plan is even bigger. > > > Since it is all shared buffers hits, it doesn't matter if you have SSD > for this particular test case. Agree. I've just tried to justify the value of random_page_cost, which is lower than like 2.0. > Cheers, > > Jeff --------------252EABB56E5295AE28B396BB Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
On 02/12/2017 07:51, Jeff Janes wrote:
On Fri, Dec 1, 2017 at 3:54 PM, Vitaliy Garnashevich <vgarnashevich@gmail.com> wrote:
On 02/12/2017 01:11, Justin Pryzby wrote:
I tried to reproduce this issue and couldn't, under PG95 and 10.1:

On Fri, Dec 01, 2017 at 12:34:27PM -0600, Justin Pryzby wrote:
On Fri, Dec 01, 2017 at 07:40:08PM +0200, Vitaliy Garnashevich wrote:
We recently had an issue in production, where a bitmap scan was chosen
instead of an index scan. Despite being 30x slower, the bitmap scan had
about the same cost as the index scan.
drop table if exists aaa;
create table aaa as select (id%100)::int num, (id%10=1)::bool flag from
generate_series(1, 10000000) id;
create index i1 on aaa  (num);
create index i2 on aaa  (flag);
analyze aaa;
What is:
effective_io_concurrency
max_parallel_workers_per_gather (I gather you don't have this)
 effective_io_concurrency = 0
max_parallel_workers_per_gather = 0

Did you notice random_page_cost = 1.5 ?

For the aaa.num = 39 case, the faster index scan actually does hit 15 times more buffers than the bitmap scan does.  While 1.5 is lot lower than 4.0, it is still much higher than the true cost of reading a page from the buffer cache.   This why the index scan is getting punished.  You could lower random_page_cost and  seq_page_cost to 0, to remove those considerations.  (I'm not saying you should do this on your production system, but rather you should do it as a way to investigate the issue.  But it might make sense on production as well)
seq_page_cost = 1.0
random_page_cost = 1.0
explain analyze select * from aaa where num = 2 and flag = true;

Bitmap Heap Scan on aaa  (cost=11536.74..20856.96 rows=10257 width=5) (actual time=108.338..108.338 rows=0 loops=1)
  ->  BitmapAnd  (cost=11536.74..11536.74 rows=10257 width=0) (actual time=108.226..108.226 rows=0 loops=1)
        ->  Bitmap Index Scan on i1  (cost=0.00..1025.43 rows=100000 width=0) (actual time=18.563..18.563 rows=100000 loops=1)
        ->  Bitmap Index Scan on i2  (cost=0.00..10505.93 rows=1025666 width=0) (actual time=78.493..78.493 rows=1000000 loops=1)

Index Scan using i1 on aaa  (cost=0.44..44663.58 rows=10257 width=5) (actual time=51.264..51.264 rows=0 loops=1)

Here I've used the filter num = 2, which produces rows=0 at BitmapAnd, and thus avoids a lot of work at "Bitmap Heap Scan" node, while still leaving about the same proportion in bitmap vs index - the bitmap is twice slower but twice less costly. It does not matter much which value to use for the filter, if it's other than num = 1.


seq_page_cost = 0.0
random_page_cost = 0.0
explain analyze select * from aaa where num = 2 and flag = true;

Bitmap Heap Scan on aaa  (cost=753.00..2003.00 rows=10257 width=5) (actual time=82.212..82.212 rows=0 loops=1)
  ->  Bitmap Index Scan on i1  (cost=0.00..750.43 rows=100000 width=0) (actual time=17.401..17.401 rows=100000 loops=1)

Index Scan using i1 on aaa  (cost=0.44..1750.43 rows=10257 width=5) (actual time=49.766..49.766 rows=0 loops=1)

The bitmap plan was reduced to use only one bitmap scan, and finally it costs more than the index plan. But I doubt that the settings seq_page_cost = random_page_cost = 0.0 should actually be used. Probably it should be instead something like 1.0/1.0 or 1.0/1.1, but other costs increased, to have more weight.


# x4 tuple/operator costs - bitmap scan still a bit cheaper
set seq_page_cost = 1.0;
set random_page_cost = 1.0;
set cpu_tuple_cost = 0.04;
set cpu_index_tuple_cost = 0.02;
set cpu_operator_cost = 0.01;

Bitmap Heap Scan on aaa  (cost=36882.97..46587.82 rows=10257 width=5) (actual time=106.045..106.045 rows=0 loops=1)
  ->  BitmapAnd  (cost=36882.97..36882.97 rows=10257 width=0) (actual time=105.966..105.966 rows=0 loops=1)
        ->  Bitmap Index Scan on i1  (cost=0.00..3276.74 rows=100000 width=0) (actual time=15.977..15.977 rows=100000 loops=1)
        ->  Bitmap Index Scan on i2  (cost=0.00..33584.72 rows=1025666 width=0) (actual time=79.208..79.208 rows=1000000 loops=1)

Index Scan using i1 on aaa  (cost=1.74..49914.89 rows=10257 width=5) (actual time=50.144..50.144 rows=0 loops=1)


# x5 tuple/operator costs - switched to single bitmap index scan, but now it costs more than the index scan
set seq_page_cost = 1.0;
set random_page_cost = 1.0;
set cpu_tuple_cost = 0.05;
set cpu_index_tuple_cost = 0.025;
set cpu_operator_cost = 0.0125;

Bitmap Heap Scan on aaa  (cost=4040.00..54538.00 rows=10257 width=5) (actual time=82.338..82.338 rows=0 loops=1)
  ->  Bitmap Index Scan on i1  (cost=0.00..4027.18 rows=100000 width=0) (actual time=19.541..19.541 rows=100000 loops=1)

Index Scan using i1 on aaa  (cost=2.17..51665.32 rows=10257 width=5) (actual time=49.545..49.545 rows=0 loops=1)


I've also tried seq_page_cost = 1.0,  random_page_cost = 1.1, but that would require more than x10 increase in tuple/operator costs, to make bitmap more costly than index.



For this test I'm using SSD and Windows (if that matters). On production we also use SSD, hence lower random_page_cost. But with the default random_page_cost=4.0, the difference in cost between the index scan plan and the bitmap scan plan is even bigger.

Since it is all shared buffers hits, it doesn't matter if you have SSD for this particular test case.
Agree. I've just tried to justify the value of random_page_cost, which is lower than like 2.0.

 
Cheers,

Jeff


--------------252EABB56E5295AE28B396BB--