pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
select count(*) from anIntColumn where int_value = 0; is very slow
8+ messages / 7 participants
[nested] [flat]

* select count(*) from anIntColumn where int_value = 0; is very slow
@ 2004-02-11 13:03 David Teran <david.teran@cluster9.com>
  2004-02-11 13:11 ` Re: select count(*) from anIntColumn where int_value = 0; Pavel Stehule <stehule@kix.fsv.cvut.cz>
  2004-02-11 13:12 ` Re: select count(*) from anIntColumn where int_value = 0; is very slow Rigmor Ukuhe <rigmor.ukuhe@finestmedia.com>
  0 siblings, 2 replies; 8+ messages in thread

From: David Teran @ 2004-02-11 13:03 UTC (permalink / raw)
  To: 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




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: select count(*) from anIntColumn where int_value = 0;
  2004-02-11 13:03 select count(*) from anIntColumn where int_value = 0; is very slow David Teran <david.teran@cluster9.com>
@ 2004-02-11 13:11 ` Pavel Stehule <stehule@kix.fsv.cvut.cz>
  2004-02-11 14:14   ` Re: select count(*) from anIntColumn where int_value = 0; Christopher Browne <cbbrowne@acm.org>
  1 sibling, 1 reply; 8+ messages in thread

From: Pavel Stehule @ 2004-02-11 13:11 UTC (permalink / raw)
  To: David Teran <david.teran@cluster9.com>; +Cc: pgsql-performance

Hello, 

If you has  index on id, then you can use
SELECT id FROM tabulka ORDER BY id DESC LIMIT 1;

See 4.8. FAQ 

Regards
Pavel Stehule

On Wed, 11 Feb 2004, David Teran wrote:

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




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: select count(*) from anIntColumn where int_value = 0;
  2004-02-11 13:03 select count(*) from anIntColumn where int_value = 0; is very slow David Teran <david.teran@cluster9.com>
  2004-02-11 13:11 ` Re: select count(*) from anIntColumn where int_value = 0; Pavel Stehule <stehule@kix.fsv.cvut.cz>
@ 2004-02-11 14:14   ` Christopher Browne <cbbrowne@acm.org>
  0 siblings, 0 replies; 8+ messages in thread

From: Christopher Browne @ 2004-02-11 14:14 UTC (permalink / raw)
  To: pgsql-performance

Oops! stehule@kix.fsv.cvut.cz (Pavel Stehule) was seen spray-painting on a wall:
>
> Regards
> Pavel Stehule
>
> On Wed, 11 Feb 2004, David Teran wrote:
>
>> 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
>> 
> If you has  index on id, then you can use
> SELECT id FROM tabulka ORDER BY id DESC LIMIT 1;
>
> See 4.8. FAQ 

I'm afraid that's not the answer.  That would be the faster
alternative to "select max(id) from tabulka;"

I guess the question is, is there a faster way of coping with the
"int_value = 0" part?

It seems a little odd that the index was not selected; it appears that
the count was 42115, right?

The estimated number of rows was 37459, and if the table size is ~4M,
then I would have expected the query optimizer to use the index.

Could you try doing "ANALYZE JOB_PROPERTY;" and then try again?  

One thought that comes to mind is that perhaps the statistics are
outdated.

Another thought is that perhaps there are several really common
values, and the statistics are crummy.  You might relieve that by:

  alter table job_property alter column int_value set statistics 20;
  analyze job_property;

(Or perhaps some higher value...)

If there are a few very common discrete values in a particular field,
then the default statistics may get skewed because the histogram
hasn't enough bins...
-- 
let name="cbbrowne" and tld="acm.org" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/wp.html
Rules of  the Evil  Overlord #102.  "I will not  waste time  making my
enemy's death look  like an accident -- I'm  not accountable to anyone
and my other enemies wouldn't believe it.



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: select count(*) from anIntColumn where int_value = 0; is very slow
  2004-02-11 13:03 select count(*) from anIntColumn where int_value = 0; is very slow David Teran <david.teran@cluster9.com>
@ 2004-02-11 13:12 ` Rigmor Ukuhe <rigmor.ukuhe@finestmedia.com>
  2004-02-11 13:32   ` Re: select count(*) from anIntColumn where int_value = 0; is very slow David Teran <david.teran@cluster9.com>
  1 sibling, 1 reply; 8+ messages in thread

From: Rigmor Ukuhe @ 2004-02-11 13:12 UTC (permalink / raw)
  To: David Teran <david.teran@cluster9.com>; 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




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: select count(*) from anIntColumn where int_value = 0; is very slow
  2004-02-11 13:03 select count(*) from anIntColumn where int_value = 0; is very slow David Teran <david.teran@cluster9.com>
  2004-02-11 13:12 ` Re: select count(*) from anIntColumn where int_value = 0; is very slow Rigmor Ukuhe <rigmor.ukuhe@finestmedia.com>
@ 2004-02-11 13:32   ` David Teran <david.teran@cluster9.com>
  2004-02-11 13:42     ` Re: select count(*) from anIntColumn where int_value = 0; is very slow PC Drew <drewpc@ibsncentral.com>
  2004-02-11 16:02     ` Re: select count(*) from anIntColumn where int_value = 0; scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 2 replies; 8+ messages in thread

From: David Teran @ 2004-02-11 13:32 UTC (permalink / raw)
  To: Rigmor Ukuhe <rigmor.ukuhe@finestmedia.com>; +Cc: pgsql-performance

Hi,

> 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.
>
tried those variations already. Strange enough, after dropping and 
recreating the index everything worked fine.

regards David




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: select count(*) from anIntColumn where int_value = 0; is very slow
  2004-02-11 13:03 select count(*) from anIntColumn where int_value = 0; is very slow David Teran <david.teran@cluster9.com>
  2004-02-11 13:12 ` Re: select count(*) from anIntColumn where int_value = 0; is very slow Rigmor Ukuhe <rigmor.ukuhe@finestmedia.com>
  2004-02-11 13:32   ` Re: select count(*) from anIntColumn where int_value = 0; is very slow David Teran <david.teran@cluster9.com>
@ 2004-02-11 13:42     ` PC Drew <drewpc@ibsncentral.com>
  1 sibling, 0 replies; 8+ messages in thread

From: PC Drew @ 2004-02-11 13:42 UTC (permalink / raw)
  To: David Teran <david.teran@cluster9.com>; +Cc: pgsql-performance

Had you done a VACUUM ANALYZE at all?  There has been much discussion 
lately about the planner needing to be updated to know that the index 
is a better choice.

On Feb 11, 2004, at 6:32 AM, David Teran wrote:

> Hi,
>
>> 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.
>>
> tried those variations already. Strange enough, after dropping and 
> recreating the index everything worked fine.
>
> regards David
>
>
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to 
> majordomo@postgresql.org
>

--
PC Drew
Manager, Dominet

IBSN
1600 Broadway, Suite 400
Denver, CO 80202

Phone: 303-984-4727 x107
Cell: 720-841-4543
Fax: 303-984-4730
Email: drewpc@ibsncentral.com




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: select count(*) from anIntColumn where int_value = 0;
  2004-02-11 13:03 select count(*) from anIntColumn where int_value = 0; is very slow David Teran <david.teran@cluster9.com>
  2004-02-11 13:12 ` Re: select count(*) from anIntColumn where int_value = 0; is very slow Rigmor Ukuhe <rigmor.ukuhe@finestmedia.com>
  2004-02-11 13:32   ` Re: select count(*) from anIntColumn where int_value = 0; is very slow David Teran <david.teran@cluster9.com>
@ 2004-02-11 16:02     ` scott.marlowe <scott.marlowe@ihs.com>
  2004-02-12 02:01       ` Re: select count(*) from anIntColumn where int_value = Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  1 sibling, 1 reply; 8+ messages in thread

From: scott.marlowe @ 2004-02-11 16:02 UTC (permalink / raw)
  To: David Teran <david.teran@cluster9.com>; +Cc: Rigmor Ukuhe <rigmor.ukuhe@finestmedia.com>; pgsql-performance

On Wed, 11 Feb 2004, David Teran wrote:

> Hi,
> 
> > 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.
> >
> tried those variations already. Strange enough, after dropping and 
> recreating the index everything worked fine.

Has that table been updated a lot in its life?  If so, it may have had a 
problem with index bloat...




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: select count(*) from anIntColumn where int_value =
  2004-02-11 13:03 select count(*) from anIntColumn where int_value = 0; is very slow David Teran <david.teran@cluster9.com>
  2004-02-11 13:12 ` Re: select count(*) from anIntColumn where int_value = 0; is very slow Rigmor Ukuhe <rigmor.ukuhe@finestmedia.com>
  2004-02-11 13:32   ` Re: select count(*) from anIntColumn where int_value = 0; is very slow David Teran <david.teran@cluster9.com>
  2004-02-11 16:02     ` Re: select count(*) from anIntColumn where int_value = 0; scott.marlowe <scott.marlowe@ihs.com>
@ 2004-02-12 02:01       ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 0 replies; 8+ messages in thread

From: Christopher Kings-Lynne @ 2004-02-12 02:01 UTC (permalink / raw)
  To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: David Teran <david.teran@cluster9.com>; Rigmor Ukuhe <rigmor.ukuhe@finestmedia.com>; pgsql-performance


>>>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.
>>>
>>
>>tried those variations already. Strange enough, after dropping and 
>>recreating the index everything worked fine.
> 
> 
> Has that table been updated a lot in its life?  If so, it may have had a 
> problem with index bloat...

Try creating a partial index: create index blah on tablw where int_value=0;

Chris




^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2004-02-12 02:01 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-11 13:03 select count(*) from anIntColumn where int_value = 0; is very slow David Teran <david.teran@cluster9.com>
2004-02-11 13:11 ` Re: select count(*) from anIntColumn where int_value = 0; Pavel Stehule <stehule@kix.fsv.cvut.cz>
2004-02-11 14:14   ` Re: select count(*) from anIntColumn where int_value = 0; Christopher Browne <cbbrowne@acm.org>
2004-02-11 13:12 ` Rigmor Ukuhe <rigmor.ukuhe@finestmedia.com>
2004-02-11 13:32   ` David Teran <david.teran@cluster9.com>
2004-02-11 13:42     ` PC Drew <drewpc@ibsncentral.com>
2004-02-11 16:02     ` Re: select count(*) from anIntColumn where int_value = 0; scott.marlowe <scott.marlowe@ihs.com>
2004-02-12 02:01       ` Re: select count(*) from anIntColumn where int_value = Christopher Kings-Lynne <chriskl@familyhealth.com.au>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox