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...3/msg00068.php

--
Postgresql & php tutorials
http://www.designmagick.com/

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://mail.postgresql.org/mj/mj_www...ql-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

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://mail.postgresql.org/mj/mj_www...ql-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

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

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

>
> 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>

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>

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....on_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

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://mail.postgresql.org/mj/mj_www...ql-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

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://mail.postgresql.org/mj/mj_www...ql-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

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://mail.postgresql.org/mj/mj_www...ql-performance