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