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