In response to SHARMILA JOTHIRAJAH <sharmi_jo@xxxxxxxxx>: > 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 is asked a lot. The quick answer is that PostgreSQL method of MVCC makes it impossible to make this query fast. Perhaps, someday, some brilliant developer will come up with an optimization, but that hasn't happened yet. There may be some tweaks you can make to your tuning, see inline below. However, if you really need a fast, accurate count of rows in that table, I recommend you create a trigger to track it. > 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 How much memory does this system have? What version of PostgreSQL are you using? If you're using an 8.X version and have more 2G of RAM, this value is likely too low. Start with 1/4 the available RAM and tune from there. > 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 -- Bill Moran http://www.potentialtech.com ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
