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

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

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
>

> Yes it is the latest stable version.
>

is there any article saying the difference between this 7.3 and 8.4

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!

--
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 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/pgdo...s/release.html


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

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/s...lease-8-3.html
http://www.postgresql.org/docs/8.3/s...lease-8-2.html
http://www.postgresql.org/docs/8.3/s...lease-8-1.html
http://www.postgresql.org/docs/8.3/s...lease-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.

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

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

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

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



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

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