Re: Performance : Optimize query

On Wed, 13 Nov 2002, Areski wrote:

> Hi Everybody,
>
>
> I have this  following query : select count(*) from "Data" where
> "IDOrigin"='29';
> It's take more less 2 minutes to run... It's really slow...
> Some one maybe knows about how to optimize "select count"
>
> Below, there are the expalin analyse of the query.
>
>
> EXPLAIN ANALYZE select count(*) from "Data" where "IDOrigin"='29';
> NOTICE:  QUERY PLAN:
>
> Aggregate  (cost=108945.77..108945.77 rows=1 width=0) (actual
> time=108845.29..108845.30 rows=1 loops=1)
>   ->  Seq Scan on Data (cost=0.00..107152.11 rows=717462 width=0) (actual
> time=18233.46..106927.60 rows=1457826 loops=1)
> Total runtime: 108845.50 msec.

Let's go through the normal stuff :)

Have you used vacuum analyze recently?  How many rows are in the table?
How many rows actually have IDOrigin=29 (ie, is 717462 a valid estimate)?
If it's not a reasonable estimate, you might want to raise the number of
statistic buckets the column is getting (alter table "Data" alter
column "IDOrigin" SET STATISTICS <number> where the default value is 10)
and running vacuum analyze again.