Re: Postgresql simple query performance question

[SHARMILA JOTHIRAJAH <sharmi_jo@xxxxxxxxx>]

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@xxxxxxxxx>
To: SHARMILA JOTHIRAJAH <sharmi_jo@xxxxxxxxx>
Cc: pgsql-general@xxxxxxxxxxxxxx
Sent: Tuesday, November 6, 2007 9:11:02 AM
Subject: Re: 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@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
>
>
>

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com