While I would not spend resources in fine tuning the count(*), I would spend some to underastand why and how the other ones do it better. Just to be better. Il Tuesday 06 November 2007 15:29:34 Bill Moran ha scritto: > In response to Reg Me Please <regmeplease@xxxxxxxxx>: > > I have no doubt you're right, Pavel. > > But why not? > > It could be a simple enhacement. > > It's not simple. Do some searches on the mailing lists and you will > find discussion of why it's difficult to do. > > > Il Tuesday 06 November 2007 15:11:02 Pavel Stehule ha scritto: > > > 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 > > > > -- > > Reg me Please > > > > ---------------------------(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 -- Reg me Please ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
