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

----- 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
>
>
>