Search Postgresql Archives

Postgresql simple query performance question

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

 



Hi
We are in the process of testing for migration of our database from Oracle to Postgresql.
I hava a simple query

Select count(*) from foo
This table has 29384048 rows and is indexed on foo_id

The tables are vacuumed and the explain plan for postgresql is

QUERY PLAN                                                                                   

------------------------------------------
 Aggregate  (cost=1194020.60..1194020.61 rows=1 width=0) (actual time=68797.280..68797.280 rows=1 loops=1)                               
   ->  Seq Scan on foo  (cost=0.00..1120560.48 rows=29384048 width=0) (actual
time=0.232..60657.948 rows=29384048 loops=1)
 Total runtime: 68797.358 ms                                                                 
                                       

The explain plan for oracle is
                    
OPERATION            OBJECT                    ACCESS_PREDICATES     FILTER_PREDICATES    
 -------------------  ------------------------  --------------------  --------------------
 SELECT STATEMENT ()  (null)                    (null)                (null)               
  SORT (AGGREGATE)    (null)                    (null)                (null)               
   INDEX (FULL SCAN)  foo_IDX_ID  (null)        (null)   

Oracle uses index for count(*) query in this case
This query in   Oracle takes only 5 sec and in postgresql it takes 1 min 10sec          

The same query in oracle without the index and full table scan(like in postgresql) has the

explain plan like this and it takes 34 sec.

select /*+ full(foo1) */ count(*) from foo1

OPERATION                OBJECT              ACCESS_PREDICATES     FILTER_PREDICATES    
 -----------------------  ------------------  --------------------  --------------------
 SELECT STATEMENT ()      (null)              (null)                (null)               
  SORT (AGGREGATE)        (null)              (null)                (null)   
    TABLE ACCESS (FULL)   foo                 (null)                (null)


In short the query "Select count(*) from foo" takes the following time:
Postgresql - 1m 10 sec
Oracle(index scan) - 5 sec
Oracle (full table scan) - 34 sec

How can I speed up this query in postgresql ? The other postgres settings are

postgresql    
        
   max_connections = 100
   shared_buffers = 50000                 
   temp_buffers = 5000
   work_mem = 16384                         
   maintenance_work_mem = 262144  
   fsync = on                             
   wal_sync_method = fsync
   effective_cache_size = 300000   
   random_page_cost =      4               
   cpu_tuple_cost = 0.01               
   cpu_index_tuple_cost = 0.001         
   cpu_operator_cost = 0.0025  

Are there any tuning that need to be done in the OS  or database side? I had attached the iostat and vmstat results of postgresql

Thanks

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
iostat -xz 1 Result

		extended device statistics                   
device       r/s    w/s   kr/s   kw/s wait actv  svc_t  %w  %b 
                 

sd8         28.1    0.0  112.3    0.0  0.0  0.0    1.2   0   3
sd8       2538.3    0.0 67831.4    0.0  0.0  4.0    1.6   2  95
sd8         74.1    0.0 69762.0    0.0  0.0  1.7   22.4   1  99

sd8         75.9    0.0 66456.9    0.0  0.0  1.6   21.8   1  99

sd8         78.0    0.0 71911.6    0.0  0.0  1.7   21.7   1 100

sd8         84.9    0.0 75541.3    0.0  0.0  1.7   19.7   1  99

sd8         58.9    1.0 45594.3    8.0  0.0  1.1   18.4   0  65
sd8         43.1    0.0 37623.1    0.0  0.0  0.9   21.9   0  56
sd8         78.2    0.0 71490.0    0.0  0.0  1.7   21.5   1 100
sd8         76.8    0.0 74463.9    0.0  0.0  1.7   21.8   1 100
sd8         80.1    0.0 73141.8    0.0  0.0  1.6   20.3   1 100
sd8         73.1    4.0 66903.6   49.1  0.0  1.7   21.6   1 100
sd8         85.9    0.0 70751.0    0.0  0.0  1.7   20.3   1 100
sd8         78.0    0.0 71935.0    0.0  0.0  1.7   22.2   1 100
sd8         80.1    3.0 72106.0   16.5  0.0  1.7   20.2   1 100
sd8         86.7    0.0 69856.3    0.0  0.0  1.8   20.3   1 100
sd8         74.2    0.0 67650.1    0.0  0.0  1.7   22.7   1  99
sd8         77.7    0.0 71639.7    0.0  0.0  1.7   21.7   1 100
sd8         87.1    1.0 77258.4    8.0  0.0  1.7   18.9   1 100
sd8         74.0    0.0 52439.5    0.0  0.0  1.3   18.0   1  80
sd8         29.0    0.0 27037.5    0.0  0.0  0.7   23.7   0  42
sd8         73.0    0.0 70196.0    0.0  0.0  1.7   22.8   1 100
sd8         81.0    0.0 69751.6    0.0  0.0  1.6   20.5   1  99
sd8         73.1    0.0 70047.0    0.0  0.0  1.6   22.4   1  99
sd8         79.0    0.0 70940.3    0.0  0.0  1.6   20.2   1 100
sd8         90.9    0.0 73723.0    0.0  0.0  1.7   18.4   1 100
sd8         81.8    0.0 73863.1    0.0  0.0  1.7   20.7   1 100
sd8         70.2    0.0 63961.2    0.0  0.0  1.7   24.0   1 100
sd8         77.0    0.0 68823.9    0.0  0.0  1.7   21.8   1 100
sd8         86.1    0.0 67807.4    0.0  0.0  1.7   20.2   1 100
sd8         81.9    0.0 74948.2    0.0  0.0  1.7   20.3   1 100
sd8         81.1    0.0 69996.7    0.0  0.0  1.7   20.9   1 100
sd8         78.9    0.0 69753.9    0.0  0.0  1.7   21.3   1 100
sd8         15.0    0.0 14450.9    0.0  0.0  0.4   28.5   0  25
sd8         99.0    1.0 68272.5    6.0  0.0  1.7   17.4   1 100
sd8         76.0    0.0 64045.8    0.0  0.0  1.7   22.1   1  99
sd8         72.9    0.0 69894.3    0.0  0.0  1.6   22.7   1 100
sd8         80.1    0.0 74832.9    0.0  0.0  1.6   20.0   1  99
sd8         80.9    0.0 73069.2    0.0  0.0  1.6   20.0   1 100
sd8         80.0    0.0 67633.6    0.0  0.0  1.7   21.2   1 100
sd8         76.1    0.0 70024.9    0.0  0.0  1.7   22.6   1 100
sd8         82.9    0.0 74977.0    0.0  0.0  1.6   19.9   1 100
sd8         82.0    0.0 68589.8    0.0  0.0  1.7   20.5   1  99
sd8         84.0    0.0 71422.1    0.0  0.0  1.7   19.8   1 100
sd8         77.1    2.0 69987.0   10.5  0.0  1.7   21.2   1 100
sd8         77.7    0.0 68980.0    0.0  0.0  1.7   21.7   1 100
sd8         38.0    0.0 27278.3    0.0  0.0  0.7   19.1   0  41
sd8         74.1    0.0 60158.5    0.0  0.0  1.4   19.0   1  85
sd8        313.0    0.0 64260.2    0.0  0.0  2.8    9.0   1  99
sd8        422.1    6.0 9812.6   49.1  0.0  2.4    5.6   0  26

                 

vmstat 1 Result


 kthr      memory            page            disk          faults      cpu
 r b w   swap  free  re  mf pi po fr de sr f0 s0 s1 s3   in   sy   cs us sy id
 0 0 0 14057280 6820180 0 0  0  0  0  0  0  0  0  0  0 1881  256  295  0  0 100
 0 0 0 14057280 6820180 0 0  0  0  0  0  0  0  0  0  0 1898  263  314  0  0 100
 0 0 0 14057280 6820180 0 0  0  0  0  0  0  0  0  0  0 1878  255  295  0  0 99
 0 0 0 14057280 6820180 0 0  0  0  0  0  0  0  0  0  0 1894  276  307  0  0 100
 0 0 0 14057280 6820180 0 0  0  0  0  0  0  0  0  0  0 1903  259  320  0  0 99
 0 0 0 14057280 6820180 0 0  0  0  0  0  0  0  0  0  0 1877  253  304  0  1 99
 0 0 0 14057280 6820180 0 0  0  0  0  0  0  0  0  0  0 1905  284  333  0  0 100
 0 0 0 14057280 6820180 0 0  0  0  0  0  0  0  0  0  0 1871  253  290  0  0 100
 0 0 0 14057280 6820180 0 0  0  0  0  0  0  0  0  0  0 1859  257  305  0  2 98
 0 0 0 14057280 6820180 0 0  0  0  0  0  0  0  0  0  0 1849  252  284  0  0 99
 0 0 0 14057280 6820180 0 0  0  0  0  0  0  0  0  0  0 1860  252  298  0  0 99
 0 0 0 14057280 6823332 22535 0 0 0 0 0  0  0  0  0  0 71984 11566 307 3  9 88
 0 0 0 14057280 6822940 51617 0 0 0 0 0  0  0  0  0  0 156618 26061 302 7 19 74
 0 0 0 14057280 6822584 52430 0 0 0 0 0  0  0  0  0  0 159092 26461 301 6 19 75
 0 0 0 14057280 6822736 54630 0 0 0 0 0  0  0  0  0  0 165505 27574 332 7 19 74
 0 0 0 14057280 6822952 57737 0 0 0 0 0  0  0  0  0  0 175337 29122 290 7 18 74
 0 0 0 14057280 6823164 55174 0 0 0 0 0  0  0  0  0  0 167231 27848 325 7 19 74
 0 0 0 14057280 6823268 58975 0 0 0 0 0  0  0  0  0  0 178603 29745 297 8 18 74
 0 0 0 14057280 6822720 60515 0 0 0 0 0  0  0  0  0  0 183611 30515 302 8 18 74
 kthr      memory            page            disk          faults      cpu
 r b w   swap  free  re  mf pi po fr de sr f0 s0 s1 s3   in   sy   cs us sy id
 0 0 0 14057280 6822636 59845 0 0 0 0 0  0  0  0  0  0 181058 30177 288 7 18 74
 0 0 0 14057280 6820788 58760 0 0 0 0 0  0  0  0  0  0 176709 29633 301 8 18 74
 0 0 0 14057408 6822796 55444 1 0 8 8 0  0  0  0  0  0 169944 28054 347 10 19 71
 0 0 0 14057408 6823240 60964 0 0 0 0 0  0  0  0  0  0 184825 30728 319 8 18 74
 0 0 0 14057312 6821048 27751 0 54071 0 0 0 0 0 0 0  0 89122 14198 870 4 11 86
 0 0 0 14057304 6820784 18180 0 72048 0 0 0 0 0 0 0  0 59542 9351 461  2  7 90
 0 0 0 14057304 6820460 16663 0 66323 0 0 0 0 0 0 0  0 53828 8579 429  2  7 91
 0 0 0 14057356 6821880 17003 0 67978 0 0 0 0 0 0 0  0 56132 8771 431  2  7 91
 0 0 0 14057304 6820972 17299 0 70210 0 0 0 0 0 0 0  0 56322 8908 423  2  7 91
 0 0 0 14057356 6821640 19186 0 76744 0 0 0 0 0 0 0  0 62215 9847 454  2  7 91
 0 0 0 14057308 6821416 25825 0 52808 4 4 0 0 0 0 0  0 79888 13166 396 3 10 87
 0 0 0 14057304 6820976 37472 0 29852 0 0 0 0 0 0 0  0 117354 19002 363 5 13 82
 0 0 0 14057304 6820456 17873 0 72470 0 0 0 0 0 0 0  0 58556 9189 445  2  7 91
 0 0 0 14057356 6821756 18836 0 75224 0 0 0 0 0 0 0  0 61116 9685 452  2  7 91
 0 0 0 14057356 6821616 18044 0 72293 0 0 0 0 0 0 0  0 58726 9276 447  2  7 90
 0 0 0 14057356 6819224 17441 373 67335 48 48 0 0 0 0 0 0 55103 9242 452 2 7 91
 0 0 0 14057336 6822032 17405 0 69692 0 0 0 0 0 0 0  0 58951 8950 418  2  7 91
 0 0 0 14057304 6821040 18149 0 72597 0 0 0 0 0 0 0  0 59014 9340 446  2  7 91
 0 0 0 14057316 6820988 18329 0 73315 0 0 0 0 0 0 0  0 59518 9419 444  2 10 88
 kthr      memory            page            disk          faults      cpu
 r b w   swap  free  re  mf pi po fr de sr f0 s0 s1 s3   in   sy   cs us sy id
 0 0 0 14057304 6820460 17052 0 68016 0 0 0 0 0 0 0  0 55995 8780 431  2  7 91
 0 0 0 14057304 6821228 17193 0 67986 0 0 0 0 0 0 0  0 56081 8856 439  2  6 92
 0 0 0 14057304 6820964 17910 0 72353 0 0 0 0 0 0 0  0 58444 9258 481  2  7 91
 0 0 0 14057304 6820972 18998 0 75414 4 4 0 0 0 0 0  0 62595 9826 446  2  7 90
 0 0 0 14057356 6822364 24525 0 52203 0 0 0 0 0 0 0  0 76161 12573 432 3 10 88
 0 0 0 14057304 6821280 37756 0 28627 0 0 0 0 0 0 0  0 117891 19126 348 5 14 82
 0 0 0 14057328 6821000 17871 0 70550 0 0 0 0 0 0 0  0 57957 9196 451  2  8 90
 0 0 0 14057304 6820976 17174 0 68982 0 0 0 0 0 0 0  0 56304 8847 444  2  7 91
 0 0 0 14057304 6820976 17707 0 71343 0 0 0 0 0 0 0  0 57882 9115 434  2  7 91
 0 0 0 14057304 6820720 17960 0 71224 0 0 0 0 0 0 0  0 58696 9243 435  2  7 91
 0 0 0 14057356 6821916 18294 0 72974 0 0 0 0 0 0 0  0 59953 9405 438  2  8 91
 0 0 0 14057304 6821168 18697 0 75416 0 0 0 0 0 0 0  0 60672 9600 441  2  8 90
 0 0 0 14057304 6820912 15798 0 62253 0 0 0 0 0 0 0  0 51665 8160 430  1  7 92
 0 0 0 14057356 6822380 17069 0 69203 0 0 0 0 0 0 0  0 56136 8803 443  2  7 91
 0 0 0 14057356 6821692 17253 0 68230 0 0 0 0 0 0 0  0 56309 8875 421  2  7 91
 0 0 0 14057304 6821104 18386 0 74124 0 0 0 0 0 0 0  0 59601 9447 437  2  7 91
 0 0 0 14057304 6820848 17875 0 70717 0 0 0 0 0 0 0  0 58322 9201 453  2  7 91
 0 0 0 14057356 6821780 19299 0 65536 0 0 0 0 0 0 0  0 61080 9907 440  2  7 91
 0 0 0 14057408 6822968 43973 0 18859 0 0 0 0 0 0 0  0 135998 22248 351 4 16 80
 kthr      memory            page            disk          faults      cpu
 r b w   swap  free  re  mf pi po fr de sr f0 s0 s1 s3   in   sy   cs us sy id
 0 0 0 14057304 6820712 17076 0 68067 4 4 0 0 0 0 0  0 56030 8812 456  2  7 91
 0 0 0 14057356 6821536 16007 0 64139 0 0 0 0 0 0 0  0 52397 8254 411  1  7 92
 0 0 0 14057304 6821228 17836 0 70329 0 0 0 0 0 0 0  0 57914 9173 422  2  7 91
 0 0 0 14057304 6820852 18505 0 75030 0 0 0 0 0 0 0  0 60211 9508 465  2  8 90
 0 0 0 14057304 6820660 18120 0 72480 0 0 0 0 0 0 0  0 58871 9327 435  2  7 91
 0 0 0 14057356 6821184 17046 0 67964 0 0 0 0 0 0 0  0 56266 8791 420  2  7 91
 0 0 0 14057304 6821104 17988 0 71224 4 4 0 0 0 0 0  0 58744 9249 440  1  7 91
 0 0 0 14057356 6822380 17849 0 72122 0 0 0 0 0 0 0  0 57808 9178 455  2  7 90
 0 0 0 14057356 6821536 17727 0 69106 0 0 0 0 0 0 0  0 58019 9117 441  2  7 91
 0 0 0 14057304 6821300 17904 0 72068 0 0 0 0 0 0 0  0 58234 9229 436  2  7 91
 0 0 0 14057356 6822028 17299 0 69755 0 0 0 0 0 0 0  0 56670 8890 433  2  9 89
 0 0 0 14057304 6820788 16677 0 66390 0 0 0 0 0 0 0  0 54775 8580 417  1  6 92
 0 0 0 14057408 6821220 47216 0 19148 0 0 0 0 0 0 0  0 143944 23875 340 5 16 79
 0 0 0 14057304 6820844 17675 0 69798 0 0 0 0 0 0 0  0 57577 9088 434  1  7 91
 0 0 0 14057364 6822496 17994 0 53604 47 47 0 0 0 0 0 0 58822 9292 625 2  8 89
 0 0 0 14057408 6823472 0 0  0  0  0  0  0  0  0  0  0 1880  261  311  0  0 100
 0 0 0 14057408 6823472 0 0  0  0  0  0  0  0  0  0  0 1874  251  309  0  0 100
 0 0 0 14057408 6823472 0 0  0  0  0  0  0  0  0  0  0 1880  275  346  0  1 99
 0 0 0 14057408 6823472 0 0  0  0  0  0  0  0  0  0  0 1877  261  318  0  0 100
 kthr      memory            page            disk          faults      cpu
 r b w   swap  free  re  mf pi po fr de sr f0 s0 s1 s3   in   sy   cs us sy id
 0 0 0 14057408 6823472 0 0  0  0  0  0  0  0  0  0  0 1862  263  300  0  0 99
 0 0 0 14057408 6823472 0 0  0  0  0  0  0  0  0  0  0 1856  267  306  0  0 100
 0 0 0 14057408 6823472 0 0  0  0  0  0  0  0  0  0  0 1864  244  308  0  1 99
 0 0 0 14058188 6824268 0 0  0  8  8  0  0  0  0  0  0  905  210  311  0  0 100
 0 0 0 14058188 6824268 0 0  0  0  0  0  0  0  0  0  0  851  148  289  0  0 100
 0 0 0 14058188 6824268 0 1  0  0  0  0  0  0  0  0  0  903  216  370  2  0 97



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
       message can get through to the mailing list cleanly

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux