Re: Postgresql simple query performance question

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(at)gmail(dot)com>:
> > 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(at)yahoo(dot)com> 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(at)postgresql(dot)org 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