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: pgsql-general-owner@xxxxxxxxxxxxxx on behalf of roopa perumalraja
Sent: Wed 10/18/2006 12:41 AM
To: pgsql-general@xxxxxxxxxxxxxx
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 <gonzales@xxxxxxxxxxxxxx> 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 gsw@xxxxxxxxxxxxxxxx
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=4535d8c8103071076418835&user=gsw@xxxxxxxxxxxxxxxx&retrain=spam&template=history&history_page=1";
!DSPAM:4535d8c8103071076418835!
-------------------------------------------------------
