Ynt: Gained %20 performance after disabling bitmapscan

[Yavuz Selim Sertoglu <yavuzselim.sertoglu@xxxxxxxxxxxxxxxx>]

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@xxxxxxxxx>
Gönderildi: 19 Ekim 2018 Cuma 22:40:57
Kime: Yavuz Selim Sertoglu
Bilgi: pgsql-performance@xxxxxxxxxxxxxxxxxxxx
Konu: Re: Gained %20 performance after disabling bitmapscan

 

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

---

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.