Re: Gained %20 performance after disabling bitmapscan
On Mon, Oct 22, 2018 at 3:20 AM Yavuz Selim Sertoglu < yavuzselim.serto...@medyasoft.com.tr> wrote: > Thanks for the reply Jeff, > > I know 20ms is nothing but it shows me that there is a problem with my > configuration. I want to find it. > This is a dangerous assumption. This is no configuration you can come up with which will cause the planner to be within 20% of perfection in all cases. Given the other plans you've shown and discussed, I think this is just chasing our own tail. Cheers, Jeff >
Re: Gained %20 performance after disabling bitmapscan
On Fri, Oct 19, 2018 at 3:19 AM Yavuz Selim Sertoglu < yavuzselim.serto...@medyasoft.com.tr> wrote: > Hi all, > > 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. > A 20% difference in speed is unlikely to make or break you. Is it even worth worrying about? > But I want to use index_only_scan because index have all column that query > need. No need to access table. > Your table is not very well vacuumed, so there is need to access it (9010 times to get 6115 rows, which seems like quite an anti-feat; but I don't know which of those numbers are averaged over loops/parallel workers, versus summed over them). Vacuuming your table will not only make the index-only scan look faster to the planner, but also actually be faster. The difference in timing could easily be down to one query warming the cache for the other. Are these timings fully reproducible altering execution orders back and forth? And they have different degrees of parallelism, what happens if you disable parallelism to simplify the analysis? > It is doing index_only_scan when disabling bitmap scan but I cannot > disable bitmap scan for cluster wide. There are other queries... > Can you help me to solve the issue? > > Cranking up effective_cache_size can make index scans look better in comparison to bitmap scans, without changing a lot of other stuff. This still holds even for index-only-scan, in cases where the planner knows the table to be poorly vacuumed. But moving the column tested for inequality to the end of the index would be probably make much more of a difference, regardless of which plan it chooses. Cheers, Jeff >
Re: Gained %20 performance after disabling bitmapscan
Yavuz, cannot add much to other points but as for index-only scan, an (auto)vacuum must be run in order to optimizer understand it can utilize index-only scan. Please check if autovacuum was run on the table after index creation and if no, run it manually. Vlad
Re: Gained %20 performance after disabling bitmapscan
Yavuz Selim Sertoglu writes: > I have a problem with my query. Query always using parallel bitmap heap scan. Have you messed with the parallel cost parameters? It seems a bit surprising that this query wants to use parallelism at all. > Index Cond: (((mukellef_id)::text = '0123456789'::text) AND > (kayit_tarihi >= '2018-01-01 00:00:00'::timestamp without time zone) AND > (sube_no = '-13'::integer) AND ((defter)::text = 'sm'::text)) If that's your normal query pattern, then this isn't a very good index design: > Column|Type | Definition > --+-+-- > mukellef_id | character varying(12) | mukellef_id > kayit_tarihi | timestamp without time zone | kayit_tarihi > sube_no | integer | sube_no > defter | character varying(4)| defter > id | bigint | id The column order should be mukellef_id, sube_no, defter, kayit_tarihi, id so that the index entries you want are adjacent in the index. Of course, if you have other queries using this index, you might need to leave it as-is --- but this is the query you're complaining about... regards, tom lane
Re: Gained %20 performance after disabling bitmapscan
On Fri, Oct 19, 2018 at 07:19:12AM +, 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
