query performance question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux