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
Gained %20 performance after disabling bitmapscan
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. 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... Can you help me to solve the issue? PostgreSQL Version: PostgreSQL 10.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit Here my query: explain analyze with ids as ( select g.id,g.kdv,g.tutar from dbs.gider g left join dbs.gider_belge gb on gb.id=g.gider_belge_id where gb.mukellef_id='0123456789' and g.deleted is not true and gb.deleted is not true and gb.sube_no='-13' and gb.defter='sm' and gb.kayit_tarihi>='2018-01-01 00:00:00'), totals as (select sum(kdv) tkdv,sum(tutar) ttutar from ids) select ids.id,totals.tkdv,totals.ttutar from ids,totals; Here default explain analyze output: Nested Loop (cost=25939.84..26244.15 rows=10143 width=72) (actual time=92.936..94.708 rows=12768 loops=1) CTE ids -> Gather (cost=1317.56..25686.25 rows=10143 width=20) (actual time=12.774..87.854 rows=12768 loops=1) Workers Planned: 2 Workers Launched: 2 -> Nested Loop (cost=317.56..23671.95 rows=4226 width=20) (actual time=5.382..80.240 rows=4256 loops=3) -> Parallel Bitmap Heap Scan on gider_belge gb (cost=316.99..10366.28 rows=3835 width=8) (actual time=5.223..29.208 rows=4077 loops=3) Recheck 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) AND (deleted IS NOT TRUE)) Heap Blocks: exact=7053 -> Bitmap Index Scan on idx_gider_belge_mukellef_id_kayit_tarihi_sube_no_defter_id (cost=0.00..314.69 rows=9205 width=0) (actual time=8.086..8.086 rows=12230 loops=1) 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)) -> Index Scan using idx_gider_gider_belge_id on gider g (cost=0.56..3.41 rows=6 width=28) (actual time=0.012..0.012 rows=1 loops=12230) Index Cond: (gider_belge_id = gb.id) Filter: (deleted IS NOT TRUE) Rows Removed by Filter: 0 CTE totals -> Aggregate (cost=253.58..253.59 rows=1 width=64) (actual time=92.925..92.925 rows=1 loops=1) -> CTE Scan on ids ids_1 (cost=0.00..202.86 rows=10143 width=40) (actual time=12.776..90.976 rows=12768 loops=1) -> CTE Scan on totals (cost=0.00..0.02 rows=1 width=64) (actual time=92.926..92.927 rows=1 loops=1) -> CTE Scan on ids (cost=0.00..202.86 rows=10143 width=8) (actual time=0.001..0.820 rows=12768 loops=1) Planning time: 0.691 ms Execution time: 113.107 ms Here explain analyze output after disabling bitmapscan: Nested Loop (cost=31493.51..31797.85 rows=10144 width=72) (actual time=73.359..75.107 rows=12768 loops=1) CTE ids -> Gather (cost=1001.13..31239.89 rows=10144 width=20) (actual time=0.741..67.391 rows=12768 loops=1) Workers Planned: 1 Workers Launched: 1 -> Nested Loop (cost=1.13..29225.49 rows=5967 width=20) (actual time=0.185..62.422 rows=6384 loops=2) -> Parallel Index Only Scan using idx_gider_belge_mukellef_id_kayit_tarihi_sube_no_defter_id on gider_belge gb (cost=0.56..10437.97 rows=5415 width=8) (actual time=0.092..15.913 rows=61 15 loops=2) Index Cond: ((mukellef_id = '0123456789'::text) AND (kayit_tarihi >= '2018-01-01 00:00:00'::timestamp without time zone) AND (sube_no = '-13'::integer) AND (defter = 'sm'::text)) Heap Fetches: 9010 -> Index Scan using idx_gider_gider_belge_id on gider g (cost=0.56..3.41 rows=6 width=28) (actual time=0.007..0.007 rows=1 loops=12230) Index Cond: (gider_belge_id = gb.id) Filter: (deleted IS NOT TRUE) Rows Removed by Filter: 0 CTE totals -> Aggregate (cost=253.60..253.61 rows=1 width=64) (actual time=73.354..73.354 rows=1 loops=1) -> CTE Scan on ids ids_1 (cost=0.00..202.88 rows=10144 width=40) (actual time=0.743..70.975 rows=12768 loops=1) -> CTE Scan on totals (cost=0.00..0.02 rows=1 width=64) (actual time=73.356..73.357 rows=1 loops=1) -> CTE Scan on ids (cost=0.00..202.88 rows=10144 width=8) (actual
