I understand that. But why is that when oracle is given a hint to do full table scan instead of using index to get the count, it is still faster than postgres when both has the same explain plan? Oracle takes 34 sec and postgres takes 1 m10 sec . Is there anything that can be done in postgresql for speeding this up?
Oracle --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) postgresql --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 Thanks again sharmila ----- Original Message ---- From: Pavel Stehule <[EMAIL PROTECTED]> To: SHARMILA JOTHIRAJAH <[EMAIL PROTECTED]> Cc: pgsql-general@postgresql.org Sent: Tuesday, November 6, 2007 9:11:02 AM Subject: Re: [GENERAL] Postgresql simple query performance question 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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
