Thread: Gained %20 performance after disabling bitmapscan

Gained %20 performance after disabling bitmapscan

From

Yavuz Selim Sertoglu

Date:

19 October 2018, 10:19:12

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 time=0.001..0.820 rows=12768 loops=1)

Planning time: 0.723 ms

Execution time: 82.995 ms

Here my index:

dbs=# \d dbs.idx_gider_belge_mukellef_id_kayit_tarihi_sube_no_defter_id

Index "dbs.idx_gider_belge_mukellef_id_kayit_tarihi_sube_no_defter_id"

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

btree, for table "dbs.gider_belge", predicate (deleted IS NOT TRUE)

YASAL UYARI:

Bu E-mail mesaji ve ekleri, isimleri yazili alicilar disindaki kisilere aciklanmamasi, dagitilmamasi ve iletilmemesi gereken kisiye ozel ve gizli bilgiler icerebilir. Mesajin muhatabi degilseniz lutfen gonderici ile irtibat kurunuz, mesaj ve eklerini siliniz.

E-mail sistemlerinin tasidigi guvenlik risklerinden dolayi, mesajlarin gizlilikleri ve butunlukleri bozulabilir, mesaj virus icerebilir. Bilinen viruslere karsi kontrolleri yapilmis olarak yollanan mesajin sisteminizde yaratabilecegi olasi zararlardan Sirketimiz sorumlu tutulamaz.

DISCLAIMER:

This email and its attachments may contain private and confidential information intended for the use of the addressee only, which should not be announced, copied or forwarded. If you are not the intended recipient, please contact the sender, delete the message and its attachments. Due to security risks of email systems, the confidentiality and integrity of the message may be damaged, the message may contain viruses. This message is scanned for known viruses and our Company will not be liable for possible system damages caused by the message.

Re: Gained %20 performance after disabling bitmapscan

From

Justin Pryzby

Date:

19 October 2018, 16:44:55

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
conditionsand 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
otherqueries...
 

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

Re: Gained %20 performance after disabling bitmapscan

From

Tom Lane

Date:

19 October 2018, 16:52:04

Yavuz Selim Sertoglu <yavuzselim.sertoglu@medyasoft.com.tr> 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
withouttime 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

From

Vladimir Ryabtsev

Date:

19 October 2018, 21:09:03

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

From

Jeff Janes

Date:

19 October 2018, 22:40:57

On Fri, Oct 19, 2018 at 3:19 AM Yavuz Selim Sertoglu <yavuzselim.sertoglu@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

Ynt: Gained %20 performance after disabling bitmapscan

From

Yavuz Selim Sertoglu

Date:

22 October 2018, 09:52:53

Thanks for reply Tom,

AFAIK nothing changed with planner. Only max_parallel_*

[postgres@db-server ~]$ psql -c"show all" | grep parallel

force_parallel_mode | off | Forces use of parallel query facilities.

max_parallel_workers | 192 | Sets the maximum number of parallel workers than can be active at one time.

max_parallel_workers_per_gather | 96 | Sets the maximum number of parallel processes per executor node.

min_parallel_index_scan_size | 512kB | Sets the minimum amount of index data for a parallel scan.

min_parallel_table_scan_size | 8MB | Sets the minimum amount of table data for a parallel scan.

parallel_setup_cost | 1000 | Sets the planner's estimate of the cost of starting up worker processes for parallel query.

parallel_tuple_cost | 0.1 | Sets the planner's estimate of the cost of passing each tuple (row) from worker to master backend.

Queries written by developer team, I can only recommend them your suggestion.

Gönderen: Tom Lane <tgl@sss.pgh.pa.us>
Gönderildi: 19 Ekim 2018 Cuma 16:52:04
Kime: Yavuz Selim Sertoglu
Bilgi: pgsql-performance@lists.postgresql.org
Konu: Re: Gained %20 performance after disabling bitmapscan

Yavuz Selim Sertoglu <yavuzselim.sertoglu@medyasoft.com.tr> 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

YASAL UYARI:

DISCLAIMER:

Ynt: Gained %20 performance after disabling bitmapscan

From

Yavuz Selim Sertoglu

Date:

22 October 2018, 09:55:13

Thanks for the reply Vladimir,

I thought explain analyze is enough. I run vacuum analyze manually but it didn't work either.

Gönderen: Vladimir Ryabtsev <greatvovan@gmail.com>
Gönderildi: 19 Ekim 2018 Cuma 21:09:03
Kime: Yavuz Selim Sertoglu
Bilgi: pgsql-performance@lists.postgresql.org
Konu: Re: Gained %20 performance after disabling bitmapscan

Vlad

YASAL UYARI:

DISCLAIMER:

Ynt: Gained %20 performance after disabling bitmapscan

From

Yavuz Selim Sertoglu

Date:

22 October 2018, 10:20:38

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.

I've vacuumed table but it didn't work either.
After vacuum, query start to using another index.

I run query a few times so result comes from cache with both query.

If I set max_parallel_workers_per_gather to 0, it is using index scan.

Here is new explain;

select id,kdv,tutar from dbs.gider_kayitlar where mukellef_id='3800433276' and deleted is not true and sube_no='-13' and defter='sm' and 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;

QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------------------

Nested Loop (cost=27505.85..27676.06 rows=5673 width=72) (actual time=83.704..85.395 rows=12768 loops=1)

CTE ids

-> Nested Loop (cost=1.13..27364.01 rows=5673 width=46) (actual time=0.063..77.898 rows=12768 loops=1)

-> Index Scan using idx_gider_belge_mukellef_id on gider_belge (cost=0.56..8998.87 rows=5335 width=8) (actual time=0.045..23.261 rows=12369 loops=1)

Index Cond: ((mukellef_id)::text = '0123456789'::text)

Filter: ((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 (cost=0.56..3.37 rows=7 width=30) (actual time=0.004..0.004 rows=1 loops=12369)

Index Cond: (gider_belge_id = gider_belge.id)

Filter: (deleted IS NOT TRUE)

Rows Removed by Filter: 0

CTE totals

-> Aggregate (cost=141.83..141.84 rows=1 width=64) (actual time=83.700..83.700 rows=1 loops=1)

-> CTE Scan on ids ids_1 (cost=0.00..113.46 rows=5673 width=52) (actual time=0.065..81.463 rows=12768 loops=1)

-> CTE Scan on totals (cost=0.00..0.02 rows=1 width=64) (actual time=83.702..83.702 rows=1 loops=1)

-> CTE Scan on ids (cost=0.00..113.46 rows=5673 width=8) (actual time=0.001..0.796 rows=12768 loops=1)

Planning time: 0.909 ms

Execution time: 85.839 ms

shared_buffers is 256G
effective_cache_size is 768G
Database size about 90G

Gönderen: Jeff Janes <jeff.janes@gmail.com>
Gönderildi: 19 Ekim 2018 Cuma 22:40:57
Kime: Yavuz Selim Sertoglu
Bilgi: pgsql-performance@lists.postgresql.org
Konu: Re: Gained %20 performance after disabling bitmapscan

On Fri, Oct 19, 2018 at 3:19 AM Yavuz Selim Sertoglu <yavuzselim.sertoglu@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.

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?

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

YASAL UYARI:

DISCLAIMER:

Re: Gained %20 performance after disabling bitmapscan

From

Jeff Janes

Date:

26 October 2018, 20:54:32

On Mon, Oct 22, 2018 at 3:20 AM Yavuz Selim Sertoglu <yavuzselim.sertoglu@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

From

Jim Finnerty

Date:

29 December 2018, 23:11:16

changing parameters can have surprising effects.  fyi, I tried disabling
bitmapscan and running the 113 queries (iirc) of the Join Order Benchmark
against them.  Several improved.  Here the 'Baseline' is the best previously
known plan, and 'Baseline+1' is the plan with enable_bitmapscan = false. 
Notably:

NOTICE:      Baseline   [Planning time 90.349 ms, Execution time 12531.577
ms]
NOTICE:      Baseline+1 [Planning time 81.473 ms, Execution time 7646.242
ms]
NOTICE:      Total time benefit: 4894.211 ms, Execution time benefit:
4885.335 ms

shaved 4.9s off a 12.5s query, and:

NOTICE:      Baseline   [Planning time 198.983 ms, Execution time 2715.75
ms]
NOTICE:      Baseline+1 [Planning time 183.204 ms, Execution time 1395.494
ms]
NOTICE:      Total time benefit: 1336.035 ms, Execution time benefit:
1320.256 ms

gained nicely in percentage terms, and:

NOTICE:      Baseline   [Planning time 91.527 ms, Execution time 12480.151
ms]
NOTICE:      Baseline+1 [Planning time 84.192 ms, Execution time 7918.974
ms]
NOTICE:      Total time benefit: 4568.512 ms, Execution time benefit:
4561.177 ms

also had a nice 4.5s+ improvement, among other plan diffs.  

This just shows that when you inject a new planning constraint into a
workload, at least some of the plans will probably get faster.  In this case
a few of them got significantly faster either in absolute terms or in
percentage terms.  Unsurprisingly, the great majority got slower.

    /Jim



-----
Jim Finnerty, AWS, Amazon Aurora PostgreSQL
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html