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

In response to SHARMILA JOTHIRAJAH <sharmi_jo@yahoo.com>:

> 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 is asked a lot.  The quick answer is that PostgreSQL method of MVCC
makes it impossible to make this query fast.  Perhaps, someday, some
brilliant developer will come up with an optimization, but that hasn't
happened yet.

There may be some tweaks you can make to your tuning, see inline below.
However, if you really need a fast, accurate count of rows in that
table, I recommend you create a trigger to track it.

> 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

How much memory does this system have?  What version of PostgreSQL are you
using?  If you're using an 8.X version and have more 2G of RAM, this
value is likely too low.  Start with 1/4 the available RAM and tune from
there.

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



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