pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedcount * performance issue
62+ messages / 31 participants
[nested] [flat]
* count * performance issue
@ 2008-03-06 05:43 sathiya psql <sathiya.psql@gmail.com>
0 siblings, 2 replies; 62+ messages in thread
From: sathiya psql @ 2008-03-06 05:43 UTC (permalink / raw)
To: pgsql-performance
count(*) tooks much time...
but with the where clause we can make this to use indexing,... what where
clause we can use??
Am using postgres 7.4 in Debian OS with 1 GB RAM,
am having a table with nearly 50 lakh records,
it has more than 15 columns, i want to count how many records are there, it
is taking nearly 17 seconds to do that...
i know that to get a approximate count we can use
SELECT reltuples FROM pg_class where relname = TABLENAME;
but this give approximate count, and i require exact count...
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 05:48 Chris <dmagick@gmail.com>
parent: sathiya psql <sathiya.psql@gmail.com>
1 sibling, 0 replies; 62+ messages in thread
From: Chris @ 2008-03-06 05:48 UTC (permalink / raw)
To: sathiya psql <sathiya.psql@gmail.com>; +Cc: pgsql-performance
sathiya psql wrote:
> count(*) tooks much time...
>
> but with the where clause we can make this to use indexing,... what
> where clause we can use??
>
> Am using postgres 7.4 in Debian OS with 1 GB RAM,
>
> am having a table with nearly 50 lakh records,
Looks suspiciously like a question asked yesterday:
http://archives.postgresql.org/pgsql-performance/2008-03/msg00068.php
--
Postgresql & php tutorials
http://www.designmagick.com/
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 06:08 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: sathiya psql <sathiya.psql@gmail.com>
1 sibling, 1 reply; 62+ messages in thread
From: A. Kretschmer @ 2008-03-06 06:08 UTC (permalink / raw)
To: pgsql-performance
am Thu, dem 06.03.2008, um 11:13:01 +0530 mailte sathiya psql folgendes:
> count(*) tooks much time...
>
> but with the where clause we can make this to use indexing,... what where
> clause we can use??
An index without a WHERE can't help to avoid a seq. scan.
>
> Am using postgres 7.4 in Debian OS with 1 GB RAM,
PG 7.4 are very old... Recent versions are MUCH faster.
>
> am having a table with nearly 50 lakh records,
>
> it has more than 15 columns, i want to count how many records are there, it is
> taking nearly 17 seconds to do that...
>
> i know that to get a approximate count we can use
> SELECT reltuples FROM pg_class where relname = TABLENAME;
>
> but this give approximate count, and i require exact count...
There aren't a general solution. If you realy need the exact count of
tuples than you can play with a TRIGGER and increase/decrease the
tuple-count for this table in an extra table.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 06:15 Shoaib Mir <shoaibmir@gmail.com>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 2 replies; 62+ messages in thread
From: Shoaib Mir @ 2008-03-06 06:15 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-performance
On Thu, Mar 6, 2008 at 5:08 PM, A. Kretschmer <
andreas.kretschmer@schollglas.com> wrote:>
> > am having a table with nearly 50 lakh records,
> >
> > it has more than 15 columns, i want to count how many records are there,
> it is
> > taking nearly 17 seconds to do that...
> >
> > i know that to get a approximate count we can use
> > SELECT reltuples FROM pg_class where relname = TABLENAME;
> >
> > but this give approximate count, and i require exact count...
>
> There aren't a general solution. If you realy need the exact count of
> tuples than you can play with a TRIGGER and increase/decrease the
> tuple-count for this table in an extra table.
>
>
>
Or do something like:
ANALYZE tablename;
select reltuple from pg_class where relname = 'tablename';
That will also return the total number of rows in a table and I guess might
be much faster then doing a count(*) but yes if trigger can be an option
that can be the easiest way to do it and fastest too.
--
Shoaib Mir
Fujitsu Australia Software Technology
shoaibm[@]fast.fujitsu.com.au
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 06:19 sathiya psql <sathiya.psql@gmail.com>
parent: Shoaib Mir <shoaibmir@gmail.com>
1 sibling, 1 reply; 62+ messages in thread
From: sathiya psql @ 2008-03-06 06:19 UTC (permalink / raw)
To: Shoaib Mir <shoaibmir@gmail.com>; +Cc: A. Kretschmer <andreas.kretschmer@schollglas.com>; pgsql-performance
buy every time i need to put ANALYZE...
this takes the same time as count(*) takes, what is the use ??
On Thu, Mar 6, 2008 at 11:45 AM, Shoaib Mir <shoaibmir@gmail.com> wrote:
> On Thu, Mar 6, 2008 at 5:08 PM, A. Kretschmer <
> andreas.kretschmer@schollglas.com> wrote:>
>
> > > am having a table with nearly 50 lakh records,
> > >
> > > it has more than 15 columns, i want to count how many records are
> > there, it is
> > > taking nearly 17 seconds to do that...
> > >
> > > i know that to get a approximate count we can use
> > > SELECT reltuples FROM pg_class where relname = TABLENAME;
> > >
> > > but this give approximate count, and i require exact count...
> >
> > There aren't a general solution. If you realy need the exact count of
> > tuples than you can play with a TRIGGER and increase/decrease the
> > tuple-count for this table in an extra table.
> >
> >
> >
> Or do something like:
>
> ANALYZE tablename;
> select reltuple from pg_class where relname = 'tablename';
>
> That will also return the total number of rows in a table and I guess
> might be much faster then doing a count(*) but yes if trigger can be an
> option that can be the easiest way to do it and fastest too.
>
> --
> Shoaib Mir
> Fujitsu Australia Software Technology
> shoaibm[@]fast.fujitsu.com.au
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 06:26 Shoaib Mir <shoaibmir@gmail.com>
parent: sathiya psql <sathiya.psql@gmail.com>
0 siblings, 1 reply; 62+ messages in thread
From: Shoaib Mir @ 2008-03-06 06:26 UTC (permalink / raw)
To: sathiya psql <sathiya.psql@gmail.com>; +Cc: A. Kretschmer <andreas.kretschmer@schollglas.com>; pgsql-performance
On Thu, Mar 6, 2008 at 5:19 PM, sathiya psql <sathiya.psql@gmail.com> wrote:
> buy every time i need to put ANALYZE...
> this takes the same time as count(*) takes, what is the use ??
>
>
>
Dont you have autovacuuming running in the background which is taking care
of the analyze as well?
If not then hmm turn it on and doing manual analyze then shouldnt I guess
take much time!
But yes, I will say if its possible go with the trigger option as that might
be more helpful and a very fast way to do that.
--
Shoaib Mir
Fujitsu Australia Software Technology
shoaibm[@]fast.fujitsu.com.au
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 06:26 Mark Mielke <mark@mark.mielke.cc>
parent: Shoaib Mir <shoaibmir@gmail.com>
1 sibling, 1 reply; 62+ messages in thread
From: Mark Mielke @ 2008-03-06 06:26 UTC (permalink / raw)
To: Shoaib Mir <shoaibmir@gmail.com>; +Cc: A. Kretschmer <andreas.kretschmer@schollglas.com>; pgsql-performance
>
> There aren't a general solution. If you realy need the exact count of
> tuples than you can play with a TRIGGER and increase/decrease the
> tuple-count for this table in an extra table.
>
Of course, this means accepting the cost of obtaining update locks on
the count table.
The original poster should understand that they can either get a fast
estimated count, or they can get a slow accurate count (either slow in
terms of select using count(*) or slow in terms of updates using
triggers and locking).
Other systems have their own issues. An index scan may be faster than a
table scan for databases that can accurately determine counts using only
the index, but it's still a relatively slow operation, and people don't
normally need an accurate count for records in the range of 100,000+? :-)
Cheers,
mark
--
Mark Mielke <mark@mielke.cc>
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 06:31 sathiya psql <sathiya.psql@gmail.com>
parent: Shoaib Mir <shoaibmir@gmail.com>
0 siblings, 1 reply; 62+ messages in thread
From: sathiya psql @ 2008-03-06 06:31 UTC (permalink / raw)
To: Shoaib Mir <shoaibmir@gmail.com>; +Cc: A. Kretschmer <andreas.kretschmer@schollglas.com>; pgsql-performance
will you please tell, what is autovacuuming... and wat it ll do... is there
any good article in this....
On Thu, Mar 6, 2008 at 11:56 AM, Shoaib Mir <shoaibmir@gmail.com> wrote:
> On Thu, Mar 6, 2008 at 5:19 PM, sathiya psql <sathiya.psql@gmail.com>
> wrote:
>
> > buy every time i need to put ANALYZE...
> > this takes the same time as count(*) takes, what is the use ??
> >
> >
> >
> Dont you have autovacuuming running in the background which is taking care
> of the analyze as well?
>
> If not then hmm turn it on and doing manual analyze then shouldnt I guess
> take much time!
>
> But yes, I will say if its possible go with the trigger option as that
> might be more helpful and a very fast way to do that.
>
>
> --
> Shoaib Mir
> Fujitsu Australia Software Technology
> shoaibm[@]fast.fujitsu.com.au
>
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 06:32 Shoaib Mir <shoaibmir@gmail.com>
parent: sathiya psql <sathiya.psql@gmail.com>
0 siblings, 0 replies; 62+ messages in thread
From: Shoaib Mir @ 2008-03-06 06:32 UTC (permalink / raw)
To: sathiya psql <sathiya.psql@gmail.com>; +Cc: A. Kretschmer <andreas.kretschmer@schollglas.com>; pgsql-performance
On Thu, Mar 6, 2008 at 5:31 PM, sathiya psql <sathiya.psql@gmail.com> wrote:
> will you please tell, what is autovacuuming... and wat it ll do... is
> there any good article in this....
>
>
>
Read this -->
http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html#AUTOVACUUM
--
Shoaib Mir
Fujitsu Australia Software Technology
shoaibm[@]fast.fujitsu.com.au
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 06:36 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Mark Mielke <mark@mark.mielke.cc>
0 siblings, 3 replies; 62+ messages in thread
From: A. Kretschmer @ 2008-03-06 06:36 UTC (permalink / raw)
To: Mark Mielke <mark@mark.mielke.cc>; pgsql-performance
am Thu, dem 06.03.2008, um 1:26:46 -0500 mailte Mark Mielke folgendes:
>
>
> There aren't a general solution. If you realy need the exact count of
> tuples than you can play with a TRIGGER and increase/decrease the
> tuple-count for this table in an extra table.
>
>
> Of course, this means accepting the cost of obtaining update locks on the count
> table.
>
> The original poster should understand that they can either get a fast estimated
> count, or they can get a slow accurate count (either slow in terms of select
> using count(*) or slow in terms of updates using triggers and locking).
>
> Other systems have their own issues. An index scan may be faster than a table
> scan for databases that can accurately determine counts using only the index,
No. The current index-implementation contains no information about the
row-visibility within the current transaction. You need to scan the
whole data-table to obtain if the current row are visible within the
current transaction.
> but it's still a relatively slow operation, and people don't normally need an
> accurate count for records in the range of 100,000+? :-)
right.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 06:43 sathiya psql <sathiya.psql@gmail.com>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
2 siblings, 2 replies; 62+ messages in thread
From: sathiya psql @ 2008-03-06 06:43 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: Mark Mielke <mark@mark.mielke.cc>; pgsql-performance
is there any way to explicitly force the postgres to use index scan
On Thu, Mar 6, 2008 at 12:06 PM, A. Kretschmer <
andreas.kretschmer@schollglas.com> wrote:
> am Thu, dem 06.03.2008, um 1:26:46 -0500 mailte Mark Mielke folgendes:
> >
> >
> > There aren't a general solution. If you realy need the exact
> count of
> > tuples than you can play with a TRIGGER and increase/decrease
> the
> > tuple-count for this table in an extra table.
> >
> >
> > Of course, this means accepting the cost of obtaining update locks on
> the count
> > table.
> >
> > The original poster should understand that they can either get a fast
> estimated
> > count, or they can get a slow accurate count (either slow in terms of
> select
> > using count(*) or slow in terms of updates using triggers and locking).
> >
> > Other systems have their own issues. An index scan may be faster than a
> table
> > scan for databases that can accurately determine counts using only the
> index,
>
> No. The current index-implementation contains no information about the
> row-visibility within the current transaction. You need to scan the
> whole data-table to obtain if the current row are visible within the
> current transaction.
>
>
> > but it's still a relatively slow operation, and people don't normally
> need an
> > accurate count for records in the range of 100,000+? :-)
>
> right.
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
>
> http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-performance
>
^ permalink raw reply [nested|flat] 62+ messages in thread
* Fwd: count * performance issue
@ 2008-03-06 06:48 sathiya psql <sathiya.psql@gmail.com>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
2 siblings, 0 replies; 62+ messages in thread
From: sathiya psql @ 2008-03-06 06:48 UTC (permalink / raw)
To: pgsql-performance
---------- Forwarded message ----------
From: sathiya psql <sathiya.psql@gmail.com>
Date: Thu, Mar 6, 2008 at 12:17 PM
Subject: Re: [PERFORM] count * performance issue
To: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
Cc: psql-performance@postgresql.org
TRIGGER i can use if i want the count of the whole table, but i require for
some of the rows with WHERE condition....
so how to do that ???
On Thu, Mar 6, 2008 at 12:06 PM, A. Kretschmer <
andreas.kretschmer@schollglas.com> wrote:
> am Thu, dem 06.03.2008, um 1:26:46 -0500 mailte Mark Mielke folgendes:
> >
> >
> > There aren't a general solution. If you realy need the exact
> count of
> > tuples than you can play with a TRIGGER and increase/decrease
> the
> > tuple-count for this table in an extra table.
> >
> >
> > Of course, this means accepting the cost of obtaining update locks on
> the count
> > table.
> >
> > The original poster should understand that they can either get a fast
> estimated
> > count, or they can get a slow accurate count (either slow in terms of
> select
> > using count(*) or slow in terms of updates using triggers and locking).
> >
> > Other systems have their own issues. An index scan may be faster than a
> table
> > scan for databases that can accurately determine counts using only the
> index,
>
> No. The current index-implementation contains no information about the
> row-visibility within the current transaction. You need to scan the
> whole data-table to obtain if the current row are visible within the
> current transaction.
>
>
> > but it's still a relatively slow operation, and people don't normally
> need an
> > accurate count for records in the range of 100,000+? :-)
>
> right.
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
>
> http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-performance
>
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 06:54 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: sathiya psql <sathiya.psql@gmail.com>
1 sibling, 0 replies; 62+ messages in thread
From: A. Kretschmer @ 2008-03-06 06:54 UTC (permalink / raw)
To: pgsql-performance
am Thu, dem 06.03.2008, um 12:13:17 +0530 mailte sathiya psql folgendes:
> is there any way to explicitly force the postgres to use index scan
Not realy, PG use a cost-based optimizer and use an INDEX if it make
sense.
>
> On Thu, Mar 6, 2008 at 12:06 PM, A. Kretschmer <
> andreas.kretschmer@schollglas.com> wrote:
please, no silly top-posting with the complete quote below.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 06:57 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
2 siblings, 2 replies; 62+ messages in thread
From: A. Kretschmer @ 2008-03-06 06:57 UTC (permalink / raw)
To: pgsql-performance
am Thu, dem 06.03.2008, um 12:17:55 +0530 mailte sathiya psql folgendes:
> TRIGGER i can use if i want the count of the whole table, but i require for
> some of the rows with WHERE condition....
>
> so how to do that ???
Okay, in this case a TRIGGER are a bad idea. You can use an INDEX on
this row. Can you show us the output for a EXPLAIN ANALYSE SELECT
count(*) from <your_table> WHERE <your_row> = ... ?
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 07:06 sathiya psql <sathiya.psql@gmail.com>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
1 sibling, 2 replies; 62+ messages in thread
From: sathiya psql @ 2008-03-06 07:06 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-performance
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=205756.95..205756.95 rows=1 width=0) (actual time=
114675.042..114675.042 rows=1 loops=1)
-> Seq Scan on call_log (cost=0.00..193224.16 rows=5013112 width=0)
(actual time=11.754..91429.594 rows=5061619 loops=1)
Filter: (call_id > 0)
Total runtime: 114699.797 ms
(4 rows)
it is now taking 114 seconds, i think because of load in my system.... any
way will you explain., what is this COST, actual time and other stuffs....
On Thu, Mar 6, 2008 at 12:27 PM, A. Kretschmer <
andreas.kretschmer@schollglas.com> wrote:
> am Thu, dem 06.03.2008, um 12:17:55 +0530 mailte sathiya psql folgendes:
> > TRIGGER i can use if i want the count of the whole table, but i require
> for
> > some of the rows with WHERE condition....
> >
> > so how to do that ???
>
> Okay, in this case a TRIGGER are a bad idea. You can use an INDEX on
> this row. Can you show us the output for a EXPLAIN ANALYSE SELECT
> count(*) from <your_table> WHERE <your_row> = ... ?
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
>
> http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-performance
>
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 07:18 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: sathiya psql <sathiya.psql@gmail.com>
1 sibling, 0 replies; 62+ messages in thread
From: A. Kretschmer @ 2008-03-06 07:18 UTC (permalink / raw)
To: pgsql-performance
am Thu, dem 06.03.2008, um 12:36:48 +0530 mailte sathiya psql folgendes:
>
> QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------------------
> Aggregate (cost=205756.95..205756.95 rows=1 width=0) (actual time=
> 114675.042..114675.042 rows=1 loops=1)
> -> Seq Scan on call_log (cost=0.00..193224.16 rows=5013112 width=0)
> (actual time=11.754..91429.594 rows=5061619 loops=1)
> Filter: (call_id > 0)
> Total runtime: 114699.797 ms
> (4 rows)
'call_id > 0' are your where-condition? An INDEX can't help, all rows
with call_id > 0 are in the result, and i guess, that's all records in
the table.
>
>
> it is now taking 114 seconds, i think because of load in my system.... any way
> will you explain., what is this COST, actual time and other stuffs....
08:16 < akretschmer> ??explain
08:16 < rtfm_please> For information about explain
08:16 < rtfm_please> see http://explain-analyze.info
08:16 < rtfm_please> or http://www.depesz.com/index.php/2007/08/06/better-explain-analyze/
08:16 < rtfm_please> or http://www.postgresql.org/docs/current/static/sql-explain.html
and
http://redivi.com/~bob/oscon2005_pgsql_pdf/OSCON_Explaining_Explain_Public.pdf
Read this to learn more about explain.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 11:50 Dave Cramer <pg@fastcrypt.com>
parent: sathiya psql <sathiya.psql@gmail.com>
1 sibling, 1 reply; 62+ messages in thread
From: Dave Cramer @ 2008-03-06 11:50 UTC (permalink / raw)
To: sathiya psql <sathiya.psql@gmail.com>; +Cc: A. Kretschmer <andreas.kretschmer@schollglas.com>; Mark Mielke <mark@mark.mielke.cc>; pgsql-performance
On 6-Mar-08, at 1:43 AM, sathiya psql wrote:
> is there any way to explicitly force the postgres to use index scan
>
>
If you want to count all the rows in the table there is only one way
to do it (without keeping track yourself with a trigger ); a seq scan.
An index will not help you.
The only thing that is going to help you is really fast disks, and
more memory, and you should consider moving to 8.3 for all the other
performance benefits.
Dave
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 12:13 Dave Cramer <pg@fastcrypt.com>
parent: Dave Cramer <pg@fastcrypt.com>
0 siblings, 1 reply; 62+ messages in thread
From: Dave Cramer @ 2008-03-06 12:13 UTC (permalink / raw)
To: sathiya psql <sathiya.psql@gmail.com>; +Cc: pgsql-performance
Hi,
On 6-Mar-08, at 6:58 AM, sathiya psql wrote:
> The only thing that is going to help you is really fast disks, and
> more memory, and you should consider moving to 8.3 for all the other
> performance benefits.
> Is 8.3 is a stable version or what is the latest stable version of
> postgres ??
>
Yes it is the latest stable version.
> moving my database from 7.4 to 8.3 will it do any harm ??
>
You will have to test this yourself. There may be issues
> what are all the advantages of moving from 7.4 to 8.3
>
Every version of postgresql has improved performance, and robustness;
so you will get better overall performance. However I want to caution
you this is not a panacea. It will NOT solve your seq scan problem.
> Dave
>
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 12:43 sathiya psql <sathiya.psql@gmail.com>
parent: Dave Cramer <pg@fastcrypt.com>
0 siblings, 4 replies; 62+ messages in thread
From: sathiya psql @ 2008-03-06 12:43 UTC (permalink / raw)
To: Dave Cramer <pg@fastcrypt.com>; +Cc: pgsql-performance
> Yes it is the latest stable version.
>
is there any article saying the difference between this 7.3 and 8.4
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 12:48 Harald Armin Massa <haraldarminmassa@gmail.com>
parent: sathiya psql <sathiya.psql@gmail.com>
3 siblings, 0 replies; 62+ messages in thread
From: Harald Armin Massa @ 2008-03-06 12:48 UTC (permalink / raw)
To: sathiya psql <sathiya.psql@gmail.com>; +Cc: pgsql-performance
Of course, the official documentation covers that information in its
release notes
http://www.postgresql.org/docs/8.3/static/release.html
best wishes
Harald
On Thu, Mar 6, 2008 at 1:43 PM, sathiya psql <sathiya.psql@gmail.com> wrote:
>
>
> >
> >
> >
> > Yes it is the latest stable version.
>
> is there any article saying the difference between this 7.3 and 8.4
>
>
>
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 12:49 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: sathiya psql <sathiya.psql@gmail.com>
3 siblings, 0 replies; 62+ messages in thread
From: A. Kretschmer @ 2008-03-06 12:49 UTC (permalink / raw)
To: pgsql-performance
am Thu, dem 06.03.2008, um 18:13:50 +0530 mailte sathiya psql folgendes:
>
> Yes it is the latest stable version.
>
>
> is there any article saying the difference between this 7.3 and 8.4
http://developer.postgresql.org/pgdocs/postgres/release.html
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 12:49 Alvaro Herrera <alvherre@commandprompt.com>
parent: sathiya psql <sathiya.psql@gmail.com>
3 siblings, 0 replies; 62+ messages in thread
From: Alvaro Herrera @ 2008-03-06 12:49 UTC (permalink / raw)
To: sathiya psql <sathiya.psql@gmail.com>; +Cc: Dave Cramer <pg@fastcrypt.com>; pgsql-performance
sathiya psql escribió:
> > Yes it is the latest stable version.
>
> is there any article saying the difference between this 7.3 and 8.4
http://www.postgresql.org/docs/8.3/static/release.html
In particular,
http://www.postgresql.org/docs/8.3/static/release-8-3.html
http://www.postgresql.org/docs/8.3/static/release-8-2.html
http://www.postgresql.org/docs/8.3/static/release-8-1.html
http://www.postgresql.org/docs/8.3/static/release-8-0.html
which are all the major releases between 7.4 and 8.3.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 13:10 Mark Mielke <mark@mark.mielke.cc>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
1 sibling, 0 replies; 62+ messages in thread
From: Mark Mielke @ 2008-03-06 13:10 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-performance
A. Kretschmer wrote:
> am Thu, dem 06.03.2008, um 12:17:55 +0530 mailte sathiya psql folgendes:
>
>> TRIGGER i can use if i want the count of the whole table, but i require for
>> some of the rows with WHERE condition....
>>
>> so how to do that ???
>>
>
> Okay, in this case a TRIGGER are a bad idea. You can use an INDEX on
> this row. Can you show us the output for a EXPLAIN ANALYSE SELECT
> count(*) from <your_table> WHERE <your_row> = ... ?
>
Actually - in this case, TRIGGER can be a good idea. If your count table
can include the where information, then you no longer require an
effective table-wide lock for updates.
In the past I have used sequential articles numbers within a topic for
an online community. Each topic row had an article_count. To generate a
new article, I could update the article_count and use the number I had
generated as the article number. To query the number of articles in a
particular topic, article_count was available. Given thousands of
topics, and 10s of thousands of articles, the system worked pretty good.
Not in the millions range as the original poster, but I saw no reason
why this wouldn't scale.
For the original poster: You might be desperate and looking for help
from the only place you know to get it from, but some of your recent
answers have shown that you are either not reading the helpful responses
provided to you, or you are unwilling to do your own research. If that
continues, I won't be posting to aid you.
Cheers,
mark
--
Mark Mielke <mark@mielke.cc>
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 13:18 Greg Smith <gsmith@gregsmith.com>
parent: sathiya psql <sathiya.psql@gmail.com>
3 siblings, 1 reply; 62+ messages in thread
From: Greg Smith @ 2008-03-06 13:18 UTC (permalink / raw)
To: sathiya psql <sathiya.psql@gmail.com>; +Cc: pgsql-performance
On Thu, 6 Mar 2008, sathiya psql wrote:
> is there any article saying the difference between this 7.3 and 8.4
I've collected a list of everything on this topic I've seen at
http://www.postgresqldocs.org/index.php/Version_8.3_Changes
The Feature Matrix linked to there will be a quicker way to see what's
happened than sorting through the release notes.
None of these changes change the fact that getting an exact count in this
situation takes either a sequential scan or triggers.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 13:27 Greg Smith <gsmith@gregsmith.com>
parent: sathiya psql <sathiya.psql@gmail.com>
1 sibling, 0 replies; 62+ messages in thread
From: Greg Smith @ 2008-03-06 13:27 UTC (permalink / raw)
To: sathiya psql <sathiya.psql@gmail.com>; +Cc: pgsql-performance
On Thu, 6 Mar 2008, sathiya psql wrote:
> any way will you explain., what is this COST, actual time and other
> stuffs....
There's a long list of links to tools and articles on this subject at
http://www.postgresqldocs.org/index.php/Using_EXPLAIN
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 15:28 Craig James <craig_james@emolecules.com>
parent: Greg Smith <gsmith@gregsmith.com>
0 siblings, 8 replies; 62+ messages in thread
From: Craig James @ 2008-03-06 15:28 UTC (permalink / raw)
To: pgsql-performance
In the 3 years I've been using Postgres, the problem of count() performance has come up more times than I can recall, and each time the answer is, "It's a sequential scan -- redesign your application."
My question is: What do the other databases do that Postgres can't do, and why not?
Count() on Oracle and MySQL is almost instantaneous, even for very large tables. So why can't Postgres do what they do?
On the one hand, I understand that Postgres has its architecture, and I understand the issue of row visibility, and so forth. On the other hand, my database is just sitting there, nothing going on, no connections except me, and... it takes FIFTY FIVE SECONDS to count 20 million rows, a query that either Oracle or MySQL would answer in a fraction of a second. It's hard for me to believe there isn't a better way.
This is a real problem. Countless people (including me) have spent significant effort rewriting applications because of this performance flaw in Postgres. Over and over, the response is, "You don't really need to do that ... change your application." Well, sure, it's always possible to change the application, but that misses the point. To most of us users, count() seems like it should be a trivial operation. On other relational database systems, it is a trivial operation.
This is really a significant flaw on an otherwise excellent relational database system.
My rant for today...
Craig
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 15:33 Bruce Momjian <bruce@momjian.us>
parent: Craig James <craig_james@emolecules.com>
7 siblings, 0 replies; 62+ messages in thread
From: Bruce Momjian @ 2008-03-06 15:33 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
Craig James wrote:
> This is a real problem. Countless people (including me) have
> spent significant effort rewriting applications because of this
> performance flaw in Postgres. Over and over, the response is,
> "You don't really need to do that ... change your application."
> Well, sure, it's always possible to change the application, but
> that misses the point. To most of us users, count() seems like
> it should be a trivial operation. On other relational database
> systems, it is a trivial operation.
>
> This is really a significant flaw on an otherwise excellent
> relational database system.
Have you read the TODO items related to this?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 15:36 Steinar H. Gunderson <sgunderson@bigfoot.com>
parent: Craig James <craig_james@emolecules.com>
7 siblings, 1 reply; 62+ messages in thread
From: Steinar H. Gunderson @ 2008-03-06 15:36 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
On Thu, Mar 06, 2008 at 07:28:50AM -0800, Craig James wrote:
> Count() on Oracle and MySQL is almost instantaneous, even for very large
> tables. So why can't Postgres do what they do?
In MySQL's case: Handle transactions. (Try COUNT(*) on an InnoDB table.)
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 15:41 Bill Moran <wmoran@collaborativefusion.com>
parent: Craig James <craig_james@emolecules.com>
7 siblings, 0 replies; 62+ messages in thread
From: Bill Moran @ 2008-03-06 15:41 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
In response to Craig James <craig_james@emolecules.com>:
> In the 3 years I've been using Postgres, the problem of count() performance has come up more times than I can recall, and each time the answer is, "It's a sequential scan -- redesign your application."
>
> My question is: What do the other databases do that Postgres can't do, and why not?
>
> Count() on Oracle and MySQL is almost instantaneous, even for very large tables. So why can't Postgres do what they do?
I don't know about Oracle, but MySQL has this problem as well. Use
innodb tables and see how slow it is. The only reason myisam tables
don't have this problem is because they don't implement any of the
features that make the problem difficult to solve.
> On the one hand, I understand that Postgres has its architecture, and I understand the issue of row visibility, and so forth. On the other hand, my database is just sitting there, nothing going on, no connections except me, and... it takes FIFTY FIVE SECONDS to count 20 million rows, a query that either Oracle or MySQL would answer in a fraction of a second. It's hard for me to believe there isn't a better way.
There's been discussion about putting visibility information in indexes.
I don't know how far along that effort is, but I expect that will improve
count() performance significantly.
> This is a real problem. Countless people (including me) have spent significant effort rewriting applications because of this performance flaw in Postgres. Over and over, the response is, "You don't really need to do that ... change your application." Well, sure, it's always possible to change the application, but that misses the point. To most of us users, count() seems like it should be a trivial operation. On other relational database systems, it is a trivial operation.
>
> This is really a significant flaw on an otherwise excellent relational database system.
Not really. It really is a design flaw in your application ... it doesn't
make relational sense to use the number of rows in a table for anything.
Just because other people do it frequently doesn't make it right.
That being said, it's still a useful feature, and I don't hear anyone
denying that. As I said, google around a bit WRT to PG storing
visibility information in indexes, as I think that's the way this will
be improved.
> My rant for today...
Feel better now?
--
Bill Moran
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 15:49 Greg Smith <gsmith@gregsmith.com>
parent: Steinar H. Gunderson <sgunderson@bigfoot.com>
0 siblings, 1 reply; 62+ messages in thread
From: Greg Smith @ 2008-03-06 15:49 UTC (permalink / raw)
To: pgsql-performance
On Thu, 6 Mar 2008, Steinar H. Gunderson wrote:
> On Thu, Mar 06, 2008 at 07:28:50AM -0800, Craig James wrote:
>> Count() on Oracle and MySQL is almost instantaneous, even for very large
>> tables. So why can't Postgres do what they do?
>
> In MySQL's case: Handle transactions. (Try COUNT(*) on an InnoDB table.)
Exactly. There is a good discussion of this at
http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/ and I
found the comments from Ken Jacobs were the most informative.
In short, if you want any reasonable database integrity you have to use
InnoDB with MySQL, and once you make that choice it has the same problem.
You only get this accelerated significantly when using MyISAM, which can
tell you an exact count of all the rows it hasn't corrupted yet.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 16:05 Dave Page <dpage@pgadmin.org>
parent: Greg Smith <gsmith@gregsmith.com>
0 siblings, 0 replies; 62+ messages in thread
From: Dave Page @ 2008-03-06 16:05 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>; +Cc: pgsql-performance
On Thu, Mar 6, 2008 at 3:49 PM, Greg Smith <gsmith@gregsmith.com> wrote:
>
> You only get this accelerated significantly when using MyISAM, which can
> tell you an exact count of all the rows it hasn't corrupted yet.
Please don't do that again. I'm going to have to spend the next hour
cleaning coffee out of my laptop keyboard.
:-)
--
Dave Page
EnterpriseDB UK Ltd: http://www.enterprisedb.com
PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 16:16 Mark Lewis <mark.lewis@mir3.com>
parent: Craig James <craig_james@emolecules.com>
7 siblings, 0 replies; 62+ messages in thread
From: Mark Lewis @ 2008-03-06 16:16 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
On Thu, 2008-03-06 at 07:28 -0800, Craig James wrote:
...
> My question is: What do the other databases do that Postgres can't do, and why not?
>
> Count() on Oracle and MySQL is almost instantaneous, even for very large tables. So why can't Postgres do what they do?
...
I can vouch that Oracle can still take linear time to perform a
count(*), at least in some cases.
I have also seen count(*) fast in some cases too... my understanding is
that they maintain a list of "interested transactions" on a per-relation
basis. Perhaps they do an optimization based on the index size if there
are no pending DML transactions?
-- Mark
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 16:31 D'Arcy J.M. Cain <darcy@druid.net>
parent: Craig James <craig_james@emolecules.com>
7 siblings, 0 replies; 62+ messages in thread
From: D'Arcy J.M. Cain @ 2008-03-06 16:31 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
On Thu, 06 Mar 2008 07:28:50 -0800
Craig James <craig_james@emolecules.com> wrote:
> In the 3 years I've been using Postgres, the problem of count() performance has come up more times than I can recall, and each time the answer is, "It's a sequential scan -- redesign your application."
>
> My question is: What do the other databases do that Postgres can't do, and why not?
>
> Count() on Oracle and MySQL is almost instantaneous, even for very large tables. So why can't Postgres do what they do?
It's a tradeoff. The only way to get that information quickly is to
maintain it internally when you insert or delete a row. So when do you
want to take your hit. It sounds like Oracle has made this decision
for you. In PostgreSQL you can use triggers and rules to manage this
information if you need it. You can even do stuff like track how many
of each type of something you have. That's something you can't do if
your database engine has done a generic speedup for you. You would
still have to create your own table for something like that and then
you get the hit twice.
--
D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-06 17:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Craig James <craig_james@emolecules.com>
7 siblings, 2 replies; 62+ messages in thread
From: Tom Lane @ 2008-03-06 17:29 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
Craig James <craig_james@emolecules.com> writes:
> Count() on Oracle and MySQL is almost instantaneous, even for very large tables. So why can't Postgres do what they do?
AFAIK the above claim is false for Oracle. They have the same
transactional issues we do.
regards, tom lane
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-07 02:40 Mark Kirkwood <markir@paradise.net.nz>
parent: Craig James <craig_james@emolecules.com>
7 siblings, 0 replies; 62+ messages in thread
From: Mark Kirkwood @ 2008-03-07 02:40 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
Craig James wrote:
>
> My question is: What do the other databases do that Postgres can't do,
> and why not?
>
> Count() on Oracle and MySQL is almost instantaneous, even for very
> large tables. So why can't Postgres do what they do?
>
I think Mysql can only do that for the myisam engine - innodb and
falcon are similar to Postgres.
I don't believe Oracle optimizes bare count(*) on a table either - tho
it may be able to use a suitable index (if present) to get the answer
quicker.
regards
Mark
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-07 03:00 Craig James <craig_james@emolecules.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 62+ messages in thread
From: Craig James @ 2008-03-07 03:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Tom Lane wrote:
> Craig James <craig_james@emolecules.com> writes:
>> Count() on Oracle and MySQL is almost instantaneous, even for very large tables. So why can't Postgres do what they do?
>
> AFAIK the above claim is false for Oracle. They have the same
> transactional issues we do.
My experience doesn't match this claim. When I ported my application from Oracle to Postgres, this was the single biggest performance problem. count() in Oracle was always very fast. We're not talking about a 20% or 50% difference, we're talking about a small fraction of a second (Oracle) versus a minute (Postgres) -- something like two or three orders of magnitude.
It may be that Oracle has a way to detect when there's no transaction and use a faster method. If so, this was a clever optimization -- in my experience, that represents the vast majority of the times you want to use count(). It's not very useful to count the rows of a table that many apps are actively modifying since the result may change the moment your transaction completes. Most of the time when you use count(), it's because you're the only one modifying the table, so the count will be meaningful.
Craig
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-07 05:38 paul rivers <rivers.paul@gmail.com>
parent: Craig James <craig_james@emolecules.com>
1 sibling, 0 replies; 62+ messages in thread
From: paul rivers @ 2008-03-07 05:38 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Craig James wrote:
> Tom Lane wrote:
>> Craig James <craig_james@emolecules.com> writes:
>>> Count() on Oracle and MySQL is almost instantaneous, even for very
>>> large tables. So why can't Postgres do what they do?
>>
>> AFAIK the above claim is false for Oracle. They have the same
>> transactional issues we do.
>
> My experience doesn't match this claim. When I ported my application
> from Oracle to Postgres, this was the single biggest performance
> problem. count() in Oracle was always very fast. We're not talking
> about a 20% or 50% difference, we're talking about a small fraction of
> a second (Oracle) versus a minute (Postgres) -- something like two or
> three orders of magnitude.
>
> It may be that Oracle has a way to detect when there's no transaction
> and use a faster method. If so, this was a clever optimization -- in
> my experience, that represents the vast majority of the times you want
> to use count(). It's not very useful to count the rows of a table
> that many apps are actively modifying since the result may change the
> moment your transaction completes. Most of the time when you use
> count(), it's because you're the only one modifying the table, so the
> count will be meaningful.
>
> Craig
>
>
Oracle will use a btree index on a not null set of columns to do a fast
full index scan, which can be an order of magnitude or faster compared
to a table scan. Also, Oracle can use a bitmap index (in cases where a
bitmap index isn't otherwise silly) for a bitmap fast index scan/bitmap
conversion for similar dramatic results.
For "large" tables, Oracle is not going to be as fast as MyISAM tables
in MySQL, even with these optimizations, since MyISAM doesn't have to
scan even index pages to get a count(*) answer against the full table.
Paul
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-07 23:51 Mark Kirkwood <markir@paradise.net.nz>
parent: Craig James <craig_james@emolecules.com>
1 sibling, 0 replies; 62+ messages in thread
From: Mark Kirkwood @ 2008-03-07 23:51 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Craig James wrote:
> Tom Lane wrote:
>> Craig James <craig_james@emolecules.com> writes:
>>> Count() on Oracle and MySQL is almost instantaneous, even for very
>>> large tables. So why can't Postgres do what they do?
>>
>> AFAIK the above claim is false for Oracle. They have the same
>> transactional issues we do.
>
> My experience doesn't match this claim. When I ported my application
> from Oracle to Postgres, this was the single biggest performance
> problem. count() in Oracle was always very fast. We're not talking
> about a 20% or 50% difference, we're talking about a small fraction of
> a second (Oracle) versus a minute (Postgres) -- something like two or
> three orders of magnitude.
>
To convince yourself do this in Oracle:
EXPLAIN PLAN FOR SELECT count(*) FROM table_without_any_indexes
and you will see a full table scan. If you add (suitable) indexes you'll
see something like an index full fast scan.
In fact you can make count(*) *very* slow indeed in Oracle, by having an
older session try to count a table that a newer session is modifying and
committing to. The older session's data for the count is reconstructed
from the rollback segments - which is very expensive.
regards
Mark
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-08 01:27 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 62+ messages in thread
From: Josh Berkus @ 2008-03-08 01:27 UTC (permalink / raw)
To: pgsql-performance; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Craig James <craig_james@emolecules.com>
Tom,
> > Count() on Oracle and MySQL is almost instantaneous, even for very
> > large tables. So why can't Postgres do what they do?
>
> AFAIK the above claim is false for Oracle. They have the same
> transactional issues we do.
Nope. Oracle's MVCC is implemented through rollback segments, rather than
non-overwriting the way ours is. So Oracle can just do a count(*) on the
index, then check the rollback segment for any concurrent
update/delete/insert activity and adjust the count. This sucks if there's
a *lot* of concurrent activity, but in the usual case it's pretty fast.
I've been thinking that when we apply the Dead Space Map we might be able
to get a similar effect in PostgreSQL. That is, just do a count over the
index, and visit only the heap pages flagged in the DSM. Again, for a
heavily updated table this wouldn't have any benefit, but for most cases
it would be much faster.
--
--Josh
Josh Berkus
PostgreSQL @ Sun
San Francisco
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-08 01:38 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 2 replies; 62+ messages in thread
From: Tom Lane @ 2008-03-08 01:38 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-performance@postgresql.org, Craig James <craig_james@emolecules.com>
Josh Berkus <josh@agliodbs.com> writes:
> Tom,
>>> Count() on Oracle and MySQL is almost instantaneous, even for very
>>> large tables. So why can't Postgres do what they do?
>>
>> AFAIK the above claim is false for Oracle. They have the same
>> transactional issues we do.
> Nope. Oracle's MVCC is implemented through rollback segments, rather than
> non-overwriting the way ours is. So Oracle can just do a count(*) on the
> index, then check the rollback segment for any concurrent
> update/delete/insert activity and adjust the count. This sucks if there's
> a *lot* of concurrent activity, but in the usual case it's pretty fast.
Well, scanning an index to get a count might be significantly faster
than scanning the main table, but it's hardly "instantaneous". It's
still going to take time proportional to the table size.
Unless they keep a central counter of the number of index entries;
which would have all the same serialization penalties we've talked
about before...
regards, tom lane
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-08 05:08 Greg Smith <gsmith@gregsmith.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 62+ messages in thread
From: Greg Smith @ 2008-03-08 05:08 UTC (permalink / raw)
To: pgsql-performance
On Fri, 7 Mar 2008, Tom Lane wrote:
> Well, scanning an index to get a count might be significantly faster
> than scanning the main table, but it's hardly "instantaneous". It's
> still going to take time proportional to the table size.
If this is something that's happening regularly, you'd have to hope that
most of the index is already buffered in memory somewhere though, so now
you're talking a buffer/OS cache scan that doesn't touch disk much.
Should be easier for that to be true because the index is smaller than the
table, right?
I know when I'm playing with pgbench the primary key index on the big
accounts table is 1/7 the size of the table, and when using that table
heavily shared_buffers ends up being mostly filled with that index. The
usage counts are so high on the index blocks relative to any section of
the table itself that they're very sticky in memory. And that's toy data;
on some of the webapps people want these accurate counts for the ratio of
index size to table data is even more exaggerated (think web forum).
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-08 05:31 Mark Mielke <mark@mark.mielke.cc>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 62+ messages in thread
From: Mark Mielke @ 2008-03-08 05:31 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>; Craig James <craig_james@emolecules.com>
Josh Berkus wrote:
>>> Count() on Oracle and MySQL is almost instantaneous, even for very
>>> large tables. So why can't Postgres do what they do?
>>>
>> AFAIK the above claim is false for Oracle. They have the same
>> transactional issues we do.
>>
>
> Nope. Oracle's MVCC is implemented through rollback segments, rather than
> non-overwriting the way ours is. So Oracle can just do a count(*) on the
> index, then check the rollback segment for any concurrent
> update/delete/insert activity and adjust the count. This sucks if there's
> a *lot* of concurrent activity, but in the usual case it's pretty fast
I read the "almost instantaneous" against "the above claim is false" and
"Nope.", and I am not sure from the above whether you are saying that
Oracle keeps an up-to-date count for the index (which might make it
instantaneous?), or whether you are saying it still has to scan the
index - which can take time if the index is large (therefore not
instantaneous).
Cheers,
mark
--
Mark Mielke <mark@mielke.cc>
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-08 06:13 Tom Lane <tgl@sss.pgh.pa.us>
parent: Greg Smith <gsmith@gregsmith.com>
0 siblings, 0 replies; 62+ messages in thread
From: Tom Lane @ 2008-03-08 06:13 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>; +Cc: pgsql-performance
Greg Smith <gsmith@gregsmith.com> writes:
> I know when I'm playing with pgbench the primary key index on the big
> accounts table is 1/7 the size of the table, and when using that table
> heavily shared_buffers ends up being mostly filled with that index. The
> usage counts are so high on the index blocks relative to any section of
> the table itself that they're very sticky in memory. And that's toy data;
> on some of the webapps people want these accurate counts for the ratio of
> index size to table data is even more exaggerated (think web forum).
Remember that our TOAST mechanism acts to limit the width of the
main-table row.
regards, tom lane
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-08 07:11 paul rivers <rivers.paul@gmail.com>
parent: Mark Mielke <mark@mark.mielke.cc>
0 siblings, 0 replies; 62+ messages in thread
From: paul rivers @ 2008-03-08 07:11 UTC (permalink / raw)
To: Mark Mielke <mark@mark.mielke.cc>; +Cc: josh@agliodbs.com, pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>; Craig James <craig_james@emolecules.com>
Mark Mielke wrote:
> Josh Berkus wrote:
>>>> Count() on Oracle and MySQL is almost instantaneous, even for very
>>>> large tables. So why can't Postgres do what they do?
>>>>
>>> AFAIK the above claim is false for Oracle. They have the same
>>> transactional issues we do.
>>>
>>
>> Nope. Oracle's MVCC is implemented through rollback segments, rather than
>> non-overwriting the way ours is. So Oracle can just do a count(*) on the
>> index, then check the rollback segment for any concurrent
>> update/delete/insert activity and adjust the count. This sucks if there's
>> a *lot* of concurrent activity, but in the usual case it's pretty fast
>
> I read the "almost instantaneous" against "the above claim is false" and
> "Nope.", and I am not sure from the above whether you are saying that
> Oracle keeps an up-to-date count for the index (which might make it
> instantaneous?), or whether you are saying it still has to scan the
> index - which can take time if the index is large (therefore not
> instantaneous).
>
> Cheers,
> mark
>
> --
> Mark Mielke <mark@mielke.cc>
>
Oracle scans the index pages, if the b-tree index is on non-nullable
columns, or if the bitmap index is on low-ish cardinality data.
Otherwise, it table scans. MyISAM in MySQL would be an example where a
counter is kept.
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-08 08:04 Arjen van der Meijden <acmmailing@tweakers.net>
parent: Craig James <craig_james@emolecules.com>
7 siblings, 0 replies; 62+ messages in thread
From: Arjen van der Meijden @ 2008-03-08 08:04 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
On 6-3-2008 16:28 Craig James wrote:
> On the one hand, I understand that Postgres has its architecture, and I
> understand the issue of row visibility, and so forth. On the other
> hand, my database is just sitting there, nothing going on, no
> connections except me, and... it takes FIFTY FIVE SECONDS to count 20
> million rows, a query that either Oracle or MySQL would answer in a
> fraction of a second. It's hard for me to believe there isn't a better
> way.
Can you explain to me how you'd fit this in a fraction of a second?
mysql> select count(*) from messages;
+----------+
| count(*) |
+----------+
| 21908505 |
+----------+
1 row in set (8 min 35.09 sec)
This is a table containing the messages on forumtopics and is therefore
relatively large. The hardware is quite beefy for a forum however (4
3Ghz cores, 16GB, 14+1 disk raid5). This table has about 20GB of data.
If I use a table that contains about the same amount of records as the
above and was before this query probably much less present in the
innodb-buffer (but also less frequently touched by other queries), we
see this:
mysql> select count(*) from messagesraw;
+----------+
| count(*) |
+----------+
| 21962804 |
+----------+
1 row in set (5 min 16.41 sec)
This table is about 12GB.
In both cases MySQL claimed to be 'Using index' with the PRIMARY index,
which for those tables is more or less identical.
Apparently the time is still table-size related, not necessarily
tuple-count related. As this shows:
mysql> select count(*) from articlestats;
+----------+
| count(*) |
+----------+
| 34467246 |
+----------+
1 row in set (54.14 sec)
that table is only 2.4GB, but contains 57% more records, although this
was on another database on a system with somewhat different specs (8
2.6Ghz cores, 16GB, 7+7+1 raid50), used a non-primary index and I have
no idea how well that index was in the system's cache prior to this query.
Repeating it makes it do that query in 6.65 seconds, repeating the
12GB-query doesn't make it any faster.
Anyway, long story short: MySQL's table-count stuff also seems
table-size related. As soon as the index it uses fits in the cache or it
doesn't have to use the primary index, it might be a different story,
but when the table(index) is too large to fit, it is quite slow.
Actually, it doesn't appear to be much faster than Postgresql's (8.2)
table-based counts. If I use a much slower machine (2 2Ghz opterons, 8GB
ddr memory, 5+1 old 15k rpm scsi disks in raid5) with a 1GB, 13M record
table wich is similar to the above articlestats, it is able to return a
count(*) in 3 seconds after priming the cache.
If you saw instantaneous results with MySQL, you have either seen the
query-cache at work or where using myisam. Or perhaps with a fast
system, you had small tuples with a nice index in a nicely primed cache.
Best regards,
Arjen
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-10 15:16 Gregory Stark <stark@enterprisedb.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 62+ messages in thread
From: Gregory Stark @ 2008-03-10 15:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: josh@agliodbs.com; pgsql-performance; Craig James <craig_james@emolecules.com>
"Tom Lane" <tgl@sss.pgh.pa.us> writes:
> Well, scanning an index to get a count might be significantly faster
> than scanning the main table, but it's hardly "instantaneous". It's
> still going to take time proportional to the table size.
Hm, Mark's comment about bitmap indexes makes that not entirely true. A bitmap
index can do RLE compression which makes the relationship between the size of
the table and the time taken to scan the index more complex. In the degenerate
case where there are no concurrent updates (assuming you can determine that
quickly) it might actually be constant time.
> Unless they keep a central counter of the number of index entries;
> which would have all the same serialization penalties we've talked
> about before...
Bitmap indexes do in fact have concurrency issues -- arguably they're just a
baroque version of this central counter in this case.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-10 20:54 Joe Mirabal <jmmirabal@gmail.com>
parent: Gregory Stark <stark@enterprisedb.com>
0 siblings, 3 replies; 62+ messages in thread
From: Joe Mirabal @ 2008-03-10 20:54 UTC (permalink / raw)
To: Gregory Stark <stark@enterprisedb.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; josh@agliodbs.com, pgsql-performance@postgresql.org, "Craig James" <craig_james@emolecules.com>
Gregory,
I just joined this listserv and was happy to see this posting. I have a
400GB table that I have indexed (building the index took 27 hours) , Loading
the table with 10 threads took 9 hours. I run queries on the data nad get
immediate max and min as well as other aggrgate functions very quickly,
however a select count(*) of the table takes forever usually nearly an hour
or more.
Do you have any tuning recommendations. We in our warehouse use the
count(*) as our verification of counts by day/month's etc and in Netezza its
immediate. I tried by adding oids. BUT the situation I learned was that
adding the oids in the table adds a significasnt amount of space to the data
AND the index.
As you may gather from this we are relatively new on Postgres.
Any suggestions you can give me would be most helpful.
Cheers,
Joe
On Mon, Mar 10, 2008 at 11:16 AM, Gregory Stark <stark@enterprisedb.com>
wrote:
> "Tom Lane" <tgl@sss.pgh.pa.us> writes:
>
> > Well, scanning an index to get a count might be significantly faster
> > than scanning the main table, but it's hardly "instantaneous". It's
> > still going to take time proportional to the table size.
>
> Hm, Mark's comment about bitmap indexes makes that not entirely true. A
> bitmap
> index can do RLE compression which makes the relationship between the size
> of
> the table and the time taken to scan the index more complex. In the
> degenerate
> case where there are no concurrent updates (assuming you can determine
> that
> quickly) it might actually be constant time.
>
> > Unless they keep a central counter of the number of index entries;
> > which would have all the same serialization penalties we've talked
> > about before...
>
> Bitmap indexes do in fact have concurrency issues -- arguably they're just
> a
> baroque version of this central counter in this case.
>
> --
> Gregory Stark
> EnterpriseDB http://www.enterprisedb.com
> Ask me about EnterpriseDB's Slony Replication support!
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
--
Mirabili et Veritas
Joe Mirabal
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-10 21:28 Bill Moran <wmoran@collaborativefusion.com>
parent: Joe Mirabal <jmmirabal@gmail.com>
2 siblings, 1 reply; 62+ messages in thread
From: Bill Moran @ 2008-03-10 21:28 UTC (permalink / raw)
To: Joe Mirabal <jmmirabal@gmail.com>; +Cc: pgsql-performance
In response to "Joe Mirabal" <jmmirabal@gmail.com>:
> Gregory,
>
> I just joined this listserv and was happy to see this posting. I have a
> 400GB table that I have indexed (building the index took 27 hours) , Loading
> the table with 10 threads took 9 hours. I run queries on the data nad get
> immediate max and min as well as other aggrgate functions very quickly,
> however a select count(*) of the table takes forever usually nearly an hour
> or more.
>
> Do you have any tuning recommendations. We in our warehouse use the
> count(*) as our verification of counts by day/month's etc and in Netezza its
> immediate. I tried by adding oids. BUT the situation I learned was that
> adding the oids in the table adds a significasnt amount of space to the data
> AND the index.
>
> As you may gather from this we are relatively new on Postgres.
>
> Any suggestions you can give me would be most helpful.
One approach to this problem is to create triggers that keep track of
the total count whenever rows are added or deleted. This adds some
overhead to the update process, but the correct row count is always
quickly available.
Another is to use EXPLAIN to get an estimate of the # of rows from
the planner. This works well if an estimate is acceptable, but can't
be trusted for precise counts.
Some searches through the archives should turn up details on these
methods.
--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/
wmoran@collaborativefusion.com
Phone: 412-422-3463x4023
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-10 21:52 Greg Smith <gsmith@gregsmith.com>
parent: Bill Moran <wmoran@collaborativefusion.com>
0 siblings, 0 replies; 62+ messages in thread
From: Greg Smith @ 2008-03-10 21:52 UTC (permalink / raw)
To: ; +Cc: Joe Mirabal <jmmirabal@gmail.com>; pgsql-performance
On Mon, 10 Mar 2008, Bill Moran wrote:
> Some searches through the archives should turn up details on these
> methods.
I've collected up what looked like the best resources on this topic into
the FAQ entry at http://www.postgresqldocs.org/index.php/Slow_Count
General Bits has already done two good summary articles here and I'd think
wading through the archives directly shouldn't be necessary.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-10 22:21 Scott Marlowe <scott.marlowe@gmail.com>
parent: Joe Mirabal <jmmirabal@gmail.com>
2 siblings, 0 replies; 62+ messages in thread
From: Scott Marlowe @ 2008-03-10 22:21 UTC (permalink / raw)
To: Joe Mirabal <jmmirabal@gmail.com>; +Cc: Gregory Stark <stark@enterprisedb.com>; Tom Lane <tgl@sss.pgh.pa.us>; josh@agliodbs.com, pgsql-performance@postgresql.org, "Craig James" <craig_james@emolecules.com>
On Mon, Mar 10, 2008 at 1:54 PM, Joe Mirabal <jmmirabal@gmail.com> wrote:
> Gregory,
>
> I just joined this listserv and was happy to see this posting. I have a
> 400GB table that I have indexed (building the index took 27 hours) , Loading
> the table with 10 threads took 9 hours. I run queries on the data nad get
> immediate max and min as well as other aggrgate functions very quickly,
> however a select count(*) of the table takes forever usually nearly an hour
> or more.
>
> Do you have any tuning recommendations. We in our warehouse use the
> count(*) as our verification of counts by day/month's etc and in Netezza its
> immediate. I tried by adding oids. BUT the situation I learned was that
> adding the oids in the table adds a significasnt amount of space to the data
> AND the index.
Yeah, this is a typical problem people run into with MVCC databases to
one extent or another. PostgreSQL has no native way to just make it
faster. However, if it's a table with wide rows, you can use a lookup
table to help a bit. Have a FK with cascading deletes from the master
table to a table that just holds the PK for it, and do count(*) on
that table.
Otherwise, you have the trigger solution mentioned previously.
Also, if you only need an approximate count, then you can use the
system tables to get that with something like
select reltuples from pg_class where relname='tablename';
after an analyze. It won't be 100% accurate, but it will be pretty
close most the time.
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-10 23:01 Greg Smith <gsmith@gregsmith.com>
parent: Joe Mirabal <jmmirabal@gmail.com>
2 siblings, 1 reply; 62+ messages in thread
From: Greg Smith @ 2008-03-10 23:01 UTC (permalink / raw)
To: Joe Mirabal <jmmirabal@gmail.com>; +Cc: pgsql-performance
On Mon, 10 Mar 2008, Joe Mirabal wrote:
> I run queries on the data nad get immediate max and min as well as other
> aggrgate functions very quickly, however a select count(*) of the table
> takes forever usually nearly an hour or more.
Are you sure the form of "select count(*)" you're using is actually
utilizing the index to find a useful subset? What do you get out of
EXPLAIN ANALZYE on the query?
In order for indexes to be helpful a couple of things need to happen:
1) They have to be structured correctly to be useful
2) There needs to be large enough settings for shared_buffes and
effective_cache_size that the database things it can use them efficiently
3) The tables involved need to be ANALYZEd to keep their statistics up to
date.
The parameters to run a 400GB *table* are very different from the
defaults; if you want tuning suggestions you should post the non-default
entries in your postgresql.conf file from what you've already adjusted
along with basic information about your server (PostgreSQL version, OS,
memory, disk setup).
> We in our warehouse use the count(*) as our verification of counts by
> day/month's etc
If you've got a database that size and you're doing that sort of thing on
it, you really should be considering partitioning as well.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-11 02:57 Robins Tharakan <tharakan@gmail.com>
parent: Greg Smith <gsmith@gregsmith.com>
0 siblings, 4 replies; 62+ messages in thread
From: Robins Tharakan @ 2008-03-11 02:57 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have been reading this conversation for a few days now and I just wanted
to ask this. From the release notes, one of the new additions in 8.3 is
(Allow col IS NULL to use an index (Teodor)).
Sorry, if I am missing something here, but shouldn't something like this
allow us to get a (fast) accurate count ?
SELECT COUNT(*) from table WHERE indexed_field IS NULL
+
SELECT COUNT(*) from table WHERE indexed_field IS NOT NULL
*Robins Tharakan*
---------- Forwarded message ----------
From: Greg Smith <gsmith@gregsmith.com>
Date: Tue, Mar 11, 2008 at 4:31 AM
Subject: Re: [PERFORM] count * performance issue
To: Joe Mirabal <jmmirabal@gmail.com>
Cc: pgsql-performance@postgresql.org
On Mon, 10 Mar 2008, Joe Mirabal wrote:
> I run queries on the data nad get immediate max and min as well as other
> aggrgate functions very quickly, however a select count(*) of the table
> takes forever usually nearly an hour or more.
Are you sure the form of "select count(*)" you're using is actually
utilizing the index to find a useful subset? What do you get out of
EXPLAIN ANALZYE on the query?
In order for indexes to be helpful a couple of things need to happen:
1) They have to be structured correctly to be useful
2) There needs to be large enough settings for shared_buffes and
effective_cache_size that the database things it can use them efficiently
3) The tables involved need to be ANALYZEd to keep their statistics up to
date.
The parameters to run a 400GB *table* are very different from the
defaults; if you want tuning suggestions you should post the non-default
entries in your postgresql.conf file from what you've already adjusted
along with basic information about your server (PostgreSQL version, OS,
memory, disk setup).
> We in our warehouse use the count(*) as our verification of counts by
> day/month's etc
If you've got a database that size and you're doing that sort of thing on
it, you really should be considering partitioning as well.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-11 03:01 Mark Mielke <mark@mark.mielke.cc>
parent: Robins Tharakan <tharakan@gmail.com>
3 siblings, 0 replies; 62+ messages in thread
From: Mark Mielke @ 2008-03-11 03:01 UTC (permalink / raw)
To: Robins Tharakan <tharakan@gmail.com>; +Cc: pgsql-performance
Robins Tharakan wrote:
> Hi,
>
> I have been reading this conversation for a few days now and I just
> wanted to ask this. From the release notes, one of the new additions
> in 8.3 is (Allow col IS NULL to use an index (Teodor)).
>
> Sorry, if I am missing something here, but shouldn't something like
> this allow us to get a (fast) accurate count ?
>
> SELECT COUNT(*) from table WHERE indexed_field IS NULL
> +
> SELECT COUNT(*) from table WHERE indexed_field IS NOT NULL
For PostgreSQL: You still don't know whether the row is visible until
you check the row. That it's NULL or NOT NULL does not influence this truth.
Cheers,
mark
--
Mark Mielke <mark@mielke.cc>
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-11 03:08 Joshua D. Drake <jd@commandprompt.com>
parent: Robins Tharakan <tharakan@gmail.com>
3 siblings, 0 replies; 62+ messages in thread
From: Joshua D. Drake @ 2008-03-11 03:08 UTC (permalink / raw)
To: Robins Tharakan <tharakan@gmail.com>; +Cc: pgsql-performance
On Tue, 11 Mar 2008 08:27:05 +0530
"Robins Tharakan" <tharakan@gmail.com> wrote:
> SELECT COUNT(*) from table WHERE indexed_field IS NULL
> +
> SELECT COUNT(*) from table WHERE indexed_field IS NOT NULL
If the selectivity is appropriate yes. However if you have 1 million
rows, and 200k of those rows are null (or not null), it is still going
to seqscan.
joshua d. drake
--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-11 03:11 Scott Marlowe <scott.marlowe@gmail.com>
parent: Robins Tharakan <tharakan@gmail.com>
3 siblings, 1 reply; 62+ messages in thread
From: Scott Marlowe @ 2008-03-11 03:11 UTC (permalink / raw)
To: Robins Tharakan <tharakan@gmail.com>; +Cc: pgsql-performance
On Mon, Mar 10, 2008 at 7:57 PM, Robins Tharakan <tharakan@gmail.com> wrote:
> Hi,
>
> I have been reading this conversation for a few days now and I just wanted
> to ask this. From the release notes, one of the new additions in 8.3 is
> (Allow col IS NULL to use an index (Teodor)).
>
> Sorry, if I am missing something here, but shouldn't something like this
> allow us to get a (fast) accurate count ?
>
> SELECT COUNT(*) from table WHERE indexed_field IS NULL
> +
> SELECT COUNT(*) from table WHERE indexed_field IS NOT NULL
It really depends on the distribution of the null / not nulls in the
table. If it's 50/50 there's no advantage to using the index, as you
still have to check visibility info in the table itself.
OTOH, if NULL (or converserly not null) are rare, then yes, the index
can help. I.e. if 1% of the tuples are null, the select count(*) from
table where field is null can use the index efficiently.
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-11 08:34 Albert Cervera Areny <albert@sedifa.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 62+ messages in thread
From: Albert Cervera Areny @ 2008-03-11 08:34 UTC (permalink / raw)
To: pgsql-performance; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; Robins Tharakan <tharakan@gmail.com>
A Dimarts 11 Març 2008 04:11, Scott Marlowe va escriure:
> On Mon, Mar 10, 2008 at 7:57 PM, Robins Tharakan <tharakan@gmail.com> wrote:
> > Hi,
> >
> > I have been reading this conversation for a few days now and I just
> > wanted to ask this. From the release notes, one of the new additions in
> > 8.3 is (Allow col IS NULL to use an index (Teodor)).
> >
> > Sorry, if I am missing something here, but shouldn't something like this
> > allow us to get a (fast) accurate count ?
> >
> > SELECT COUNT(*) from table WHERE indexed_field IS NULL
> > +
> > SELECT COUNT(*) from table WHERE indexed_field IS NOT NULL
>
> It really depends on the distribution of the null / not nulls in the
> table. If it's 50/50 there's no advantage to using the index, as you
> still have to check visibility info in the table itself.
>
> OTOH, if NULL (or converserly not null) are rare, then yes, the index
> can help. I.e. if 1% of the tuples are null, the select count(*) from
> table where field is null can use the index efficiently.
But you'll get a sequential scan with the NOT NULL case which will end up
taking more time. (Seq Scan + Index Scan > Seq Scan)
--
Albert Cervera Areny
Dept. Informàtica Sedifa, S.L.
Av. Can Bordoll, 149
08202 - Sabadell (Barcelona)
Tel. 93 715 51 11
Fax. 93 715 51 12
====================================================================
........................ AVISO LEGAL ............................
La presente comunicación y sus anexos tiene como destinatario la
persona a la que va dirigida, por lo que si usted lo recibe
por error debe notificarlo al remitente y eliminarlo de su
sistema, no pudiendo utilizarlo, total o parcialmente, para
ningún fin. Su contenido puede tener información confidencial o
protegida legalmente y únicamente expresa la opinión del
remitente. El uso del correo electrónico vía Internet no
permite asegurar ni la confidencialidad de los mensajes
ni su correcta recepción. En el caso de que el
destinatario no consintiera la utilización del correo electrónico,
deberá ponerlo en nuestro conocimiento inmediatamente.
====================================================================
........................... DISCLAIMER .............................
This message and its attachments are intended exclusively for the
named addressee. If you receive this message in error, please
immediately delete it from your system and notify the sender. You
may not use this message or any part of it for any purpose.
The message may contain information that is confidential or
protected by law, and any opinions expressed are those of the
individual sender. Internet e-mail guarantees neither the
confidentiality nor the proper receipt of the message sent.
If the addressee of this message does not consent to the use
of internet e-mail, please inform us inmmediately.
====================================================================
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-11 13:35 Bill Moran <wmoran@collaborativefusion.com>
parent: Robins Tharakan <tharakan@gmail.com>
3 siblings, 1 reply; 62+ messages in thread
From: Bill Moran @ 2008-03-11 13:35 UTC (permalink / raw)
To: Robins Tharakan <tharakan@gmail.com>; +Cc: pgsql-performance
In response to "Robins Tharakan" <tharakan@gmail.com>:
> Hi,
>
> I have been reading this conversation for a few days now and I just wanted
> to ask this. From the release notes, one of the new additions in 8.3 is
> (Allow col IS NULL to use an index (Teodor)).
>
> Sorry, if I am missing something here, but shouldn't something like this
> allow us to get a (fast) accurate count ?
>
> SELECT COUNT(*) from table WHERE indexed_field IS NULL
> +
> SELECT COUNT(*) from table WHERE indexed_field IS NOT NULL
For certain, qualified definitions of "fast", sure.
--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/
wmoran@collaborativefusion.com
Phone: 412-422-3463x4023
****************************************************************
IMPORTANT: This message contains confidential information and is
intended only for the individual named. If the reader of this
message is not an intended recipient (or the individual
responsible for the delivery of this message to an intended
recipient), please be advised that any re-use, dissemination,
distribution or copying of this message is prohibited. Please
notify the sender immediately by e-mail if you have received
this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The
sender therefore does not accept liability for any errors or
omissions in the contents of this message, which arise as a
result of e-mail transmission.
****************************************************************
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-11 13:57 Matthew <matthew@flymine.org>
parent: Bill Moran <wmoran@collaborativefusion.com>
0 siblings, 1 reply; 62+ messages in thread
From: Matthew @ 2008-03-11 13:57 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
On Tue, 11 Mar 2008, Bill Moran wrote:
> In response to "Robins Tharakan" <tharakan@gmail.com>:
>> Sorry, if I am missing something here, but shouldn't something like this
>> allow us to get a (fast) accurate count ?
>>
>> SELECT COUNT(*) from table WHERE indexed_field IS NULL
>> +
>> SELECT COUNT(*) from table WHERE indexed_field IS NOT NULL
>
> For certain, qualified definitions of "fast", sure.
And certain, qualified definitions of "accurate" as well. Race condition?
Matthew
--
"Television is a medium because it is neither rare nor well done."
-- Fred Friendly
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-11 14:01 Tino Wildenhain <tino@wildenhain.de>
parent: Matthew <matthew@flymine.org>
0 siblings, 1 reply; 62+ messages in thread
From: Tino Wildenhain @ 2008-03-11 14:01 UTC (permalink / raw)
To: Matthew <matthew@flymine.org>; +Cc: pgsql-performance
Hi,
Matthew wrote:
> On Tue, 11 Mar 2008, Bill Moran wrote:
>
>> In response to "Robins Tharakan" <tharakan@gmail.com>:
>>> Sorry, if I am missing something here, but shouldn't something like this
>>> allow us to get a (fast) accurate count ?
>>>
>>> SELECT COUNT(*) from table WHERE indexed_field IS NULL
>>> +
>>> SELECT COUNT(*) from table WHERE indexed_field IS NOT NULL
>>
>> For certain, qualified definitions of "fast", sure.
>
> And certain, qualified definitions of "accurate" as well. Race condition?
You mean in a three-state-logic? null, not null and something different?
;-)
Tino
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-11 14:19 Matthew <matthew@flymine.org>
parent: Tino Wildenhain <tino@wildenhain.de>
0 siblings, 2 replies; 62+ messages in thread
From: Matthew @ 2008-03-11 14:19 UTC (permalink / raw)
To: pgsql-performance
On Tue, 11 Mar 2008, Tino Wildenhain wrote:
>> And certain, qualified definitions of "accurate" as well. Race condition?
>
> You mean in a three-state-logic? null, not null and something different?
True, False, and FILE_NOT_FOUND.
No, actually I was referring to a race condition. So, you find the count
of rows with IS NULL, then someone changes a row, then you find the count
of rows with IS NOT NULL. Add the two together, and there may be rows that
were counted twice, or not at all.
Matthew
--
It's one of those irregular verbs - "I have an independent mind," "You are
an eccentric," "He is round the twist."
-- Bernard Woolly, Yes Prime Minister
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-11 14:31 Heikki Linnakangas <heikki@enterprisedb.com>
parent: Matthew <matthew@flymine.org>
1 sibling, 0 replies; 62+ messages in thread
From: Heikki Linnakangas @ 2008-03-11 14:31 UTC (permalink / raw)
To: Matthew <matthew@flymine.org>; +Cc: pgsql-performance
Matthew wrote:
> No, actually I was referring to a race condition. So, you find the count
> of rows with IS NULL, then someone changes a row, then you find the
> count of rows with IS NOT NULL. Add the two together, and there may be
> rows that were counted twice, or not at all.
Not a problem if you use a serializable transaction, or if you do
SELECT COUNT(*) from table WHERE indexed_field IS NULL
UNION ALL
SELECT COUNT(*) from table WHERE indexed_field IS NOT NULL
as one statement.
However, this makes no sense whatsoever. As both index scans (assuming
the planner even chooses an index scan for them, which seems highly
unlikely) still have to visit each tuple in the heap. It's always going
to be slower than a single "SELECT COUNT(*) FROM table" with a seq scan.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 62+ messages in thread
* Re: count * performance issue
@ 2008-03-11 14:34 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Matthew <matthew@flymine.org>
1 sibling, 0 replies; 62+ messages in thread
From: Andrew Sullivan @ 2008-03-11 14:34 UTC (permalink / raw)
To: pgsql-performance
On Tue, Mar 11, 2008 at 02:19:09PM +0000, Matthew wrote:
> of rows with IS NULL, then someone changes a row, then you find the count
> of rows with IS NOT NULL. Add the two together, and there may be rows that
> were counted twice, or not at all.
Only if you count in READ COMMITTED.
A
^ permalink raw reply [nested|flat] 62+ messages in thread
end of thread, other threads:[~2008-03-11 14:34 UTC | newest]
Thread overview: 62+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-03-06 05:43 count * performance issue sathiya psql <sathiya.psql@gmail.com>
2008-03-06 05:48 ` Chris <dmagick@gmail.com>
2008-03-06 06:08 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2008-03-06 06:15 ` Shoaib Mir <shoaibmir@gmail.com>
2008-03-06 06:19 ` sathiya psql <sathiya.psql@gmail.com>
2008-03-06 06:26 ` Shoaib Mir <shoaibmir@gmail.com>
2008-03-06 06:31 ` sathiya psql <sathiya.psql@gmail.com>
2008-03-06 06:32 ` Shoaib Mir <shoaibmir@gmail.com>
2008-03-06 06:26 ` Mark Mielke <mark@mark.mielke.cc>
2008-03-06 06:36 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2008-03-06 06:43 ` sathiya psql <sathiya.psql@gmail.com>
2008-03-06 06:54 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2008-03-06 11:50 ` Dave Cramer <pg@fastcrypt.com>
2008-03-06 12:13 ` Dave Cramer <pg@fastcrypt.com>
2008-03-06 12:43 ` sathiya psql <sathiya.psql@gmail.com>
2008-03-06 12:48 ` Harald Armin Massa <haraldarminmassa@gmail.com>
2008-03-06 12:49 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2008-03-06 12:49 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-03-06 13:18 ` Greg Smith <gsmith@gregsmith.com>
2008-03-06 15:28 ` Craig James <craig_james@emolecules.com>
2008-03-06 15:33 ` Bruce Momjian <bruce@momjian.us>
2008-03-06 15:36 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2008-03-06 15:49 ` Greg Smith <gsmith@gregsmith.com>
2008-03-06 16:05 ` Dave Page <dpage@pgadmin.org>
2008-03-06 15:41 ` Bill Moran <wmoran@collaborativefusion.com>
2008-03-06 16:16 ` Mark Lewis <mark.lewis@mir3.com>
2008-03-06 16:31 ` D'Arcy J.M. Cain <darcy@druid.net>
2008-03-06 17:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-07 03:00 ` Craig James <craig_james@emolecules.com>
2008-03-07 05:38 ` paul rivers <rivers.paul@gmail.com>
2008-03-07 23:51 ` Mark Kirkwood <markir@paradise.net.nz>
2008-03-08 01:27 ` Josh Berkus <josh@agliodbs.com>
2008-03-08 01:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-08 05:08 ` Greg Smith <gsmith@gregsmith.com>
2008-03-08 06:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-10 15:16 ` Gregory Stark <stark@enterprisedb.com>
2008-03-10 20:54 ` Joe Mirabal <jmmirabal@gmail.com>
2008-03-10 21:28 ` Bill Moran <wmoran@collaborativefusion.com>
2008-03-10 21:52 ` Greg Smith <gsmith@gregsmith.com>
2008-03-10 22:21 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-03-10 23:01 ` Greg Smith <gsmith@gregsmith.com>
2008-03-11 02:57 ` Robins Tharakan <tharakan@gmail.com>
2008-03-11 03:01 ` Mark Mielke <mark@mark.mielke.cc>
2008-03-11 03:08 ` Joshua D. Drake <jd@commandprompt.com>
2008-03-11 03:11 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-03-11 08:34 ` Albert Cervera Areny <albert@sedifa.com>
2008-03-11 13:35 ` Bill Moran <wmoran@collaborativefusion.com>
2008-03-11 13:57 ` Matthew <matthew@flymine.org>
2008-03-11 14:01 ` Tino Wildenhain <tino@wildenhain.de>
2008-03-11 14:19 ` Matthew <matthew@flymine.org>
2008-03-11 14:31 ` Heikki Linnakangas <heikki@enterprisedb.com>
2008-03-11 14:34 ` Andrew Sullivan <ajs@crankycanuck.ca>
2008-03-08 05:31 ` Mark Mielke <mark@mark.mielke.cc>
2008-03-08 07:11 ` paul rivers <rivers.paul@gmail.com>
2008-03-07 02:40 ` Mark Kirkwood <markir@paradise.net.nz>
2008-03-08 08:04 ` Arjen van der Meijden <acmmailing@tweakers.net>
2008-03-06 06:48 ` sathiya psql <sathiya.psql@gmail.com>
2008-03-06 06:57 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2008-03-06 07:06 ` sathiya psql <sathiya.psql@gmail.com>
2008-03-06 07:18 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2008-03-06 13:27 ` Greg Smith <gsmith@gregsmith.com>
2008-03-06 13:10 ` Mark Mielke <mark@mark.mielke.cc>
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