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 <[email protected]>
Gönderildi: 19 Ekim 2018 Cuma 22:40:57
Kime: Yavuz Selim Sertoglu
Bilgi: [email protected]
Konu: Re: Gained %20 performance after disabling bitmapscan
On Fri, Oct 19, 2018 at 3:19 AM Yavuz Selim Sertoglu
<[email protected]<mailto:[email protected]>>
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.
