pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedselect 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