Skip site navigation (1) Skip section navigation (2)

select count(*) from anIntColumn where int_value = 0; is very slow

From: David Teran <david(dot)teran(at)cluster9(dot)com>
To: PgSQL Performance ML <pgsql-performance(at)postgresql(dot)org>
Subject: select count(*) from anIntColumn where int_value = 0; is very slow
Date: 2004-02-11 13:03:15
Message-ID: A79C4495-5C92-11D8-B407-000A95A6F0DC@cluster9.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi

we have a table with about 4 million rows. One column has an int value, 
there is a btree index on it. We tried to execute the following 
statement and it is very slow on a dual G5 2GHZ with 4 GB of RAM.

explain analyze select count(*) from job_property where int_value = 0;

Aggregate  (cost=144348.80..144348.80 rows=1 width=0) (actual 
time=13536.852..13536.852 rows=1 loops=1)
   ->  Seq Scan on job_property  (cost=0.00..144255.15 rows=37459 
width=0) (actual time=19.422..13511.653 rows=42115 loops=1)
         Filter: (int_value = 0)
Total runtime: 13560.862 ms



Is this more or less normal or can we optimize this a little bit? 
FrontBase (which we compare currently) takes 2 seconds first time and 
about 0.2 seconds on second+ queries.

regards David


Responses

pgsql-performance by date

Next:From: Pavel StehuleDate: 2004-02-11 13:11:13
Subject: Re: select count(*) from anIntColumn where int_value = 0;
Previous:From: Josh BerkusDate: 2004-02-09 05:34:18
Subject: Re: Why is query selecting sequential?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group