Thread: table size
I have noticed that our database is growing in size about 10 MB day. I am not sure where this growth is coming from. Is there an easy way to check the size of all of the tables?
Thanks
David
UNION ALL was an excellent idea! It didn't cut much time off, but at least no resources are devoted to eliminating the nonexistant duplicate rows. I've had days to think about this. It seems as though the ORDER BY part of the first query is the culprit. When I run this query by itself, I can see that it would comprise the bulk of the UNION query time. select a.username as "User", a.trans_date as "Date", tl.longtype as "Type", a.trans_data as "Query Data", a.trans_charge as "Charged", a.user_reference_id as "Reference ID" from a_trans_log a join addtypelong tl on a.trans_type = tl.shorttype where a.trans_date >= '12/31/01'::TIMESTAMP order by a.trans_date desc, a.trans_data limit 20; By removing the ORDER BY a.trans_data, it cut the query down to the "almost instant" level... EXPLAIN shows me that it uses the indeces! I guess I need to drop that part of the ORDER BY, or make an index for it to use... Bah. Alas... Unless someone knows different, I don't believe that I can use the LIMIT statement in each of the subqueries. The app needs to page through the UNIONized table. As the app pages through LIMIT 20,20 LIMIT 20,40 LIMIT 20,60, etc. It needs to look at the whole sorted UNIONized table. (am I making ANY sense?) Even by dropping the order on column 4, it still takes 6 seconds to assemble the data, sort it and limit it... Could I create a cross-table index specifically for this query? I doubt its a) possible, b) (even if possible) a good idea. CG --- Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote: > > Are there going to possibly be equal rows in the two parts that you need > to merge into one row? If not, try union all which should get rid of a > sort and unique I think. __________________________________________________ Do You Yahoo!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.yahoo.com/
"David Blood" <davidjblood@yahoo.com> writes: > I have noticed that our database is growing in size about 10 MB day. I > am not sure where this growth is coming from. Is there an easy way to > check the size of all of the tables? Are you running VACUUM regularly? -Doug -- Doug McNaught Wireboard Industries http://www.wireboard.com/ Custom software development, systems and network consulting. Java PostgreSQL Enhydra Python Zope Perl Apache Linux BSD...
Yes -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Doug McNaught Sent: Wednesday, March 06, 2002 7:41 AM To: David Blood Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] table size "David Blood" <davidjblood@yahoo.com> writes: > I have noticed that our database is growing in size about 10 MB day. I > am not sure where this growth is coming from. Is there an easy way to > check the size of all of the tables? Are you running VACUUM regularly? -Doug -- Doug McNaught Wireboard Industries http://www.wireboard.com/ Custom software development, systems and network consulting. Java PostgreSQL Enhydra Python Zope Perl Apache Linux BSD... ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
I have postgres 7.2. I have a web page that uses coldfusion scripting to insert the contents of a form into a table. One of the items on the form is a place to put comments. To insert the data into the table, I used cold fusion's url encode which converts all not alpha text into their hex values. IE: spaces gets converted to %20 and so on. It seemed like a good idea to avoid having to attempted to catch for ' or " that may be entered into the comment until I got an error saying the query string was too long. Is there a limit in postgres on how long the query string can be? Thank You Charles Cruise System Administrator RV America OnLine www.rvamerica.com 408 E. Southern Avenue Tempe, Az 85282 480-784-4771 -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org]On Behalf Of David Blood Sent: Wednesday, March 06, 2002 4:44 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] table size Yes -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Doug McNaught Sent: Wednesday, March 06, 2002 7:41 AM To: David Blood Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] table size "David Blood" <davidjblood@yahoo.com> writes: > I have noticed that our database is growing in size about 10 MB day. I > am not sure where this growth is coming from. Is there an easy way to > check the size of all of the tables? Are you running VACUUM regularly? -Doug -- Doug McNaught Wireboard Industries http://www.wireboard.com/ Custom software development, systems and network consulting. Java PostgreSQL Enhydra Python Zope Perl Apache Linux BSD... ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
"ccruise" <ccruise@rvamerica.com> writes: > I have postgres 7.2. I have a web page that uses coldfusion scripting to > insert the contents of a form into a table. One of the items on the form is > a place to put comments. To insert the data into the table, I used cold > fusion's url encode which converts all not alpha text into their hex values. > IE: spaces gets converted to %20 and so on. It seemed like a good idea to > avoid having to attempted to catch for ' or " that may be entered into the > comment until I got an error saying the query string was too long. Is there > a limit in postgres on how long the query string can be? Not currently. Make sure you're running PG 7.1 or later and using the latest ODBC driver. -Doug -- Doug McNaught Wireboard Industries http://www.wireboard.com/ Custom software development, systems and network consulting. Java PostgreSQL Enhydra Python Zope Perl Apache Linux BSD...
Hello, Could someone say if the Digest mode of this mail server works, I have subscribed & unsubscribed 4 times so far recently in an attempt to receive digest mode info but to no avail. I Have even sent mail to the owner but have not received a reply yet Thanks -- Joe -- >
It seems there is just no traffic here. I just subscribed myself a few hours ago and it has been dead quiet. On Sunday 19 May 2002 15:46, you wrote: > Hello, > Could someone say if the Digest mode of this mail server works, I have > subscribed & unsubscribed 4 times so far recently in an attempt to receive > digest mode info but to no avail. I Have even sent mail to the owner but > have not received a reply yet > Thanks > -- Joe -- > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html