table size

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

I think I've found an acceptable compromise. The optimizer will use the index
if I limit each of the subqueries to no more than 6200 rows. I think a
composite total ~12000 is enough data, understanding that the users of this
query would be silly to use it for paging through more than a week's data. I
think it'd be silly to go beyond 24 hours... Anyway, if they're looking for
something more specific, I take out the limits to allow it search through the
entire database, and unless they're not using specific enough search criteria
the query will still move rather quickly. A decent compromise.

Thanks for helping me nail this one down!

CG

--- Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:

On Wed, 6 Mar 2002, Chris Gamache wrote:

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.

Yeah, although since you're doing different kinds of scans on the two
columns (desc and asc), I'm not sure what'd be necessary to get the index
used. Normally you'd just make an index on trans_date,trans_data, but
that won't work here. I think Tom Lane may have discussed a way to get
the index scan for that case sometime in the past couple of months on the
mailing list.

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

That makes sense. You could cheat a little if you knew how large the
results sets for the parts were if you're using union all since I believe
it will do them in order and just append the sets, but that doesn't seem
like a good idea in general.

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.

I don't think so.

One question is whether some of this could be done by keeping another
table around that you do your limits through. This would at least cut
down some of the cost (or for that matter if you could make a table with
this using triggers and such).

__________________________________________________
Do You Yahoo!?
Try FREE Yahoo! Mail - the world's greatest free email!
http://mail.yahoo.com/

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.