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

Hi,
    Oracle, eventhough is a timestamp based database, stores only one
version for each row in the table and the rest of the versions of the
same-row(which might have got created due to updates) in a separate
place called undo log. In postgres, all the versions are stored in the
table. So the table would be bigger than it is in Oracle. Try doing a
Vacuum Full and fire the query. You might save some seconds.
    I think we will definitely address this problem in 8.4.

Thanks,
Gokul.


On Nov 6, 2007 8:07 PM, SHARMILA JOTHIRAJAH <sharmi_jo@yahoo.com> wrote:
>
> 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 <pavel.stehule@gmail.com>
> To: SHARMILA JOTHIRAJAH <sharmi_jo@yahoo.com>
> 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 <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
> >
> >
> >
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com