Thread: table size

table size

From
"David Blood"
Date:

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

Re: improving performance of UNION and ORDER BY

From
Chris Gamache
Date:
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/

Re: table size

From
Doug McNaught
Date:
"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...

Re: table size

From
"David Blood"
Date:
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


Re: table size

From
"ccruise"
Date:
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



Re: table size

From
Doug McNaught
Date:
"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...

Digest Mode

From
Joseph Maxwell
Date:
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  --


>


Re: Digest Mode

From
Alexander Klayman
Date:

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