Roopa,
I think that by defintion a "SELECT * FROM <sometable>;" will always take
longer as the table increases in size (but if anyone who is more versed in
theory of searches, sorts, etc. contradicts me I'll happily listen!). Note that
the database could increase enormously with no direct effect on speed; but if
one necessary table keeps growing in size that will effect speed.
Possible solutions are limited, since such a query *has* to check every row to
see if it is live and retrieve the data.
Might be helped by:
(a) reducing the core table to a minimum of columns, with esp. large ones
placed in an another table where you will only get them when you need them --
reduces total amount of data being moved, but may be minimal if you still have
lots of data [but if can reduce each row from say 1k bytes to 200 bytes, as
long as you don't always need the data in the new table, you gain some in
efficiency]. Heavily dependant on use of the data and the time you have to
de-normalize the table.
(b) more RAM -- if everything is in RAM and the server does not have to hit
disk it's faster, but is obviously limited by $ and capacity. Our runtime
servers have 4 gigs each and I'm worried about needing 8 sooner rather than
later (we have lots of connections as well as lots of data).
(c) faster/different disks -- RAIDED with battery backed cache as others have
suggested. We have had poor experience with some Dell disks (I don't recall
which off hand) as database servers -- I've seen similar posts by other users,
probably in the performance list archives.
(d) migrate to *nix (yeah, I know, probably not possible, but Windows has
issues, and the postgres port is relatively newer on that platform) -- untested
assumption which may warrant a flame, but I think in general the *Nix OS
versions may have better disk I/O.
Greg W.
-----Original Message-----
From: [EMAIL PROTECTED] on behalf of roopa perumalraja
Sent: Wed 10/18/2006 12:41 AM
To: pgsql-general@postgresql.org
Cc: Michael Fuhr; louis gonzales
Subject: Re: [GENERAL] Maximum size of database
Hi
Thanks for your reply.
explain select * from tk_20060403;
QUERY PLAN
--------------------------------------------------------------------------
Seq Scan on tk_20060403 (cost=0.00..95561.30 rows=3609530 width=407)
(1 row)
will this help?
louis gonzales <[EMAIL PROTECTED]> wrote:
also, run
EXPLAIN
on any command, show the results of this. In particular, if you have
some commands that are taking 'even longer?'
roopa perumalraja wrote:
> Thanks for your reply.
>
> I have answered your questions below.
>
> 1 & 2) System: Microsoft Windows XP Professional
> Version 2002
> Computer: Intel Pentium CPU 3.40GHz, 960MB of RAM
>
> 3) shared_buffers = 20000
> autovaccum = on
>
> 4) Yes, I am vacuuming & analyzing the database once every day.
>
> 5) No concurrent activities, means I run one command at a time.
>
> 6) Nothing else running on the box other than Postgres.
>
> I hope these answers will try to solve my problem. Thanks again.
>
> Roopa
> */Michael Fuhr /* wrote:
>
> On Tue, Oct 17, 2006 at 07:26:25PM -0700, roopa perumalraja wrote:
> > I would like to know that what can be the maximum size of
> database in
> > postgres 8.1.4.
>
> http://www.postgresql.org/docs/faqs.FAQ.html#item4.4
>
> > Currently my database size is 37GB & its pretty slow. I wonder if
> > its b'cos of huge amount of data in it.
>
> 37GB isn't all that huge; as the FAQ mentions, much larger databases
> exist. Without more information we'll have to ask some of the
> standard questions:
>
> What's your hardware configuration?
> What operating system and version are you using?
> What are your non-default postgresql.conf settings?
> Are you vacuuming and analyzing the database regularly?
> How much concurrent activity do you have?
> Does anything other than PostgreSQL run on the box?
>
> If you have a specific query that's slow then please post the EXPLAIN
> ANALYZE output. Also, you might get more help on the pgsql-performance
> list.
>
> --
> Michael Fuhr
>
>
>
>
> signature
>
> ------------------------------------------------------------------------
> Get your own web address for just $1.99/1st yr
>
---------------------------------
Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail.
-------------------------------------------------------
Click link below if it is SPAM [EMAIL PROTECTED]
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=4535d8c8103071076418835&[EMAIL
PROTECTED]&retrain=spam&template=history&history_page=1"
!DSPAM:4535d8c8103071076418835!
-------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
