Hello PostgreSQL doesn't use index for COUN(*) http://www.varlena.com/GeneralBits/18.php http://sql-info.de/en/postgresql/postgres-gotchas.html#1_7 Regards Pavel Stehule On 06/11/2007, SHARMILA JOTHIRAJAH <sharmi_jo@xxxxxxxxx> wrote: > > 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 > > ---------------------------(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 > > > ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
