|
Hello, I have postgresql instance, and built another instance from the copy of existing one, So the 2 postgresql instances are identical. However when I run a query on the 2 instances, I got different performance. I checked the execution plan, they are almost same, all data is from buffered cache. the only difference is on the Bitmap Index Scan, the preparation time of Bitmap Index Scan of fast query is about 14 . the slow one took 51. The 2 servers has same hardware configuration. is there anything I miss? could you please let me know what I should check next step? thanks
below is the query plan
Fast: QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on abc (cost=1112.79..206262.79 rows=200 width=149) (actual time=16.355..28768.019 rows=3944 loops=1)
Recheck Cond: (a='a')
Filter: (from_datetime = (SubPlan 1))
Rows Removed by Filter: 9768
Heap Blocks: exact=585
Buffers: shared hit=113169
-> Bitmap Index Scan on ix_abc (cost=0.00..1112.74 rows=40032 width=0) (actual time=16.154..16.154 rows=13712 loops=1)
Index Cond: (a='a')
Buffers: shared hit=55
SubPlan 1
-> GroupAggregate (cost=0.55..4.86 rows=1 width=68) (actual time=2.094..2.094 rows=1 loops=13712)
Group Key: abc_key
Buffers: shared hit=112529
-> Index Only Scan using "PK_abc" on abc (cost=0.55..4.84 rows=1 width=68) (actual time=0.183..1.457 rows=348 loops=13712)
Index Cond: (abc_key='abc')
Heap Fetches: 0
Buffers: shared hit=112529
Planning time: 0.244 ms
Execution time: 28768.990 ms
Slow: QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on abc (cost=1088.53..202211.78 rows=196 width=151) (actual time=55.016..46062.561 rows=3944 loops=1)
Recheck Cond: (a='a')
Filter: (from_datetime = (SubPlan 1))
Rows Removed by Filter: 9768
Heap Blocks: exact=585
Buffers: shared hit=113172
-> Bitmap Index Scan on ix_abc (cost=0.00..1088.48 rows=39206 width=0) (actual time=54.591..54.591 rows=13712 loops=1)
Index Cond: (a='a')
Buffers: shared hit=58
SubPlan 1
-> GroupAggregate (cost=0.55..4.86 rows=1 width=68) (actual time=3.347..3.347 rows=1 loops=13712)
Group Key: abc_key
Buffers: shared hit=112529
-> Index Only Scan using "PK_abc" on abc (cost=0.55..4.84 rows=1 width=68) (actual time=0.293..2.326 rows=348 loops=13712)
Index Cond: (abc_key='abc')
Heap Fetches: 0
Buffers: shared hit=112529
Planning time: 1.933 ms
Execution time: 46064.325 ms
|
