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

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

From: "Rigmor Ukuhe" <rigmor(dot)ukuhe(at)finestmedia(dot)com>
To: "David Teran" <david(dot)teran(at)cluster9(dot)com>,"PgSQL Performance ML" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: select count(*) from anIntColumn where int_value = 0; is very slow
Date: 2004-02-11 13:12:00
Message-ID: OEEHLFAIJHHMABJPIANICEFLCIAA.rigmor.ukuhe@finestmedia.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 your int_value data type int4? If not then use "... from job_property
where int_value = '0'"
Indexes are used only if datatypes matches.

Rigmor Ukuhe


>
>
>
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.564 / Virus Database: 356 - Release Date: 19.01.2004


In response to

Responses

pgsql-performance by date

Next:From: David TeranDate: 2004-02-11 13:32:00
Subject: Re: select count(*) from anIntColumn where int_value = 0; is very slow
Previous:From: Pavel StehuleDate: 2004-02-11 13:11:13
Subject: Re: select count(*) from anIntColumn where int_value = 0;

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