Re: Postgresql simple query performance question - Mailing list pgsql-general

In response to Reg Me Please <regmeplease@gmail.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@yahoo.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@postgresql.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


--
Bill Moran
http://www.potentialtech.com