Date: Fri, 19 Oct 2018 08:44:55 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Yavuz Selim Sertoglu <yavuzselim.sertoglu@medyasoft.com.tr>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Gained %20 performance after disabling bitmapscan
Message-ID: <20181019134455.GI24215@telsasoft.com>
References: 
 <AM6PR07MB520725A8D1B1287AE313928FD7F90@AM6PR07MB5207.eurprd07.prod.outlook.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <AM6PR07MB520725A8D1B1287AE313928FD7F90@AM6PR07MB5207.eurprd07.prod.outlook.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

On Fri, Oct 19, 2018 at 07:19:12AM +0000, Yavuz Selim Sertoglu wrote:
> I have a problem with my query. Query always using parallel bitmap heap scan. I've created an index with all where conditions and id but query does not this index and continue to use bitmapscan. So I decided disable bitmap scan for testing. And after that, things became strange. Cost is higher, execution time is lower.
> But I want to use index_only_scan because index have all column that query need. No need to access table.
> It is doing index_only_scan when disabling bitmap scan but I cannot disable bitmap scan for cluster wide. There are other queries...

My first comment is that bitmap IOS is supported on PG11, which was
released..yesterday:

https://www.postgresql.org/docs/11/static/release-11.html
|Allow bitmap scans to perform index-only scans when possible (Alexander Kuzmenkov)

Also, I wonder whether parallel query is helping here or hurting (SET
max_parallel_workers_per_gather=0)?  If it's hurting, should you adjust cost
parameters or perhaps disable it globally ?

Justin