pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWorking with very large datasets
4+ messages / 3 participants
[nested] [flat]
* Working with very large datasets
@ 2003-02-11 23:42 Wilkinson Charlie E <Charlie.E.Wilkinson@irs.gov>
0 siblings, 2 replies; 4+ messages in thread
From: Wilkinson Charlie E @ 2003-02-11 23:42 UTC (permalink / raw)
To: pgsql-sql
Greetings,
Can anyone enlighten me or point me at resources concerning use of pgsql
with
very large datasets?
My specific problem is this:
I have two tables, one with about 100 million rows and one with about 22,000
rows. My plan was to inner join the two tables on an integer key and output
the 4 significant columns, excluding the keys. (Those with a better
understanding
of pgsql internals, feel free to laugh.) The result was a big angry psql
that
grew to 800+MB before I had to kill it.
Example:
CREATE TABLE users (
userid INTEGER NOT NULL,
fileid INTEGER NOT NULL
);
CREATE INDEX "users_userid_key" on "users" using btree ( "userid" "int4_ops"
);
CREATE TABLE files
(
fileid SERIAL NOT NULL,
name VARCHAR(120),
size INTEGER,
mtime INTEGER
);
-- files.fileid will automagically get indexed
[100 million inserts to 'users', 22,000 inserts to 'files'...]
SELECT userid,name,size,mtime FROM users INNER JOIN files ON users.fileid =
files.fileid;
[attack of the 50 foot psql...]
Did I miss sonething important? Is there a better way? Any clues
appreciated.
-cw-
(Apologies for any HTML that gets tacked onto this message. I spec'd
plaintext!)
--
Charlie Wilkinson
TRIS Development Systems Administrator
IS:SD:CT:CC:TD
Phone: 202-283-3241
MSMail: Charlie.E.Wilkinson@irs.gov
SMTP: cwilkins@tris.irs.gov
Home: cwilkins@boinklabs.com
This message constructed from 90% post-consumer electrons.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Working with very large datasets
@ 2003-02-12 00:02 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Wilkinson Charlie E <Charlie.E.Wilkinson@irs.gov>
1 sibling, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2003-02-12 00:02 UTC (permalink / raw)
To: Wilkinson Charlie E <Charlie.E.Wilkinson@irs.gov>; +Cc: pgsql-sql
On Tue, 11 Feb 2003, Wilkinson Charlie E wrote:
> Greetings,
> Can anyone enlighten me or point me at resources concerning use of pgsql
> with
> very large datasets?
>
> My specific problem is this:
>
> I have two tables, one with about 100 million rows and one with about 22,000
> rows. My plan was to inner join the two tables on an integer key and output
> the 4 significant columns, excluding the keys. (Those with a better
> understanding
> of pgsql internals, feel free to laugh.) The result was a big angry psql
> that
> grew to 800+MB before I had to kill it.
Was it psql that grew to 800Mb or a backend? If the former, how many rows
do you expect that to return? You probably want to look into using
cursors rather than returning the entire result set at once.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Working with very large datasets
@ 2003-02-12 00:33 Lex Berezhny <LBerezhny@DevIS.com>
parent: Wilkinson Charlie E <Charlie.E.Wilkinson@irs.gov>
1 sibling, 0 replies; 4+ messages in thread
From: Lex Berezhny @ 2003-02-12 00:33 UTC (permalink / raw)
To: Wilkinson Charlie E <Charlie.E.Wilkinson@irs.gov>; +Cc: pgsql-sql
On Tue, 2003-02-11 at 18:42, Wilkinson Charlie E wrote:
> Greetings,
> Can anyone enlighten me or point me at resources concerning use of
> pgsql with
> very large datasets?
[snip]
> SELECT userid,name,size,mtime FROM users INNER JOIN files ON
> users.fileid = files.fileid;
If you just want to see a sample result from that query in psql, then
you can use LIMIT:
SELECT userid,name,size,mtime FROM users INNER JOIN files ON
users.fileid = files.fileid LIMIT 100;
This should only return 100 rows.
I hope that helps.
I think there is also a way to specify offsets so that you can return
the rows in batches.
--
Lex Berezhny <LBerezhny@DevIS.com>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Working with very large datasets
@ 2003-02-12 21:14 Wilkinson Charlie E <Charlie.E.Wilkinson@irs.gov>
0 siblings, 0 replies; 4+ messages in thread
From: Wilkinson Charlie E @ 2003-02-12 21:14 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; Lex Berezhny <LBerezhny@DevIS.com>; +Cc: pgsql-sql
> -----Original Message-----
> From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
[...]
> Was it psql that grew to 800Mb or a backend? If the former,
> how many rows
> do you expect that to return? You probably want to look into using
> cursors rather than returning the entire result set at once.
It was the front end, psql that got big. postmaster has been pretty
well behaved through all this. I'm looking to get the full 100M+
records back. I have to print it all out and fax them to someone. ;)
Seriously, I want to dump it all to a (big!) file and sift through
it with some familiar unix tools to check the data and generally
look it over.
Thanks to Stephan and Lex for the tips! I took your CURSOR and LIMIT
clues and ran with them. I looked at straight SQL>psql and Perl DBD:Pg
for solutions, but the end result was some "ecpg" C code:
----------------------------------
#include <stdio.h>
#define NO_MORE_TUPLES (sqlca.sqlcode == ECPG_NOT_FOUND)
int main(int argc, char* agv[]){
EXEC SQL BEGIN DECLARE SECTION;
int myuser;
char myname[121];
int mysize;
int mymtime;
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE sqlca;
EXEC SQL CONNECT TO userdb USER xxxxxxx;
EXEC SQL WHENEVER sqlerror sqlprint;
EXEC SQL DECLARE mycursor CURSOR FOR SELECT user,name,size,mtime
FROM users JOIN files USING (fileid) ORDER BY user;
EXEC SQL OPEN mycursor;
while(1){
EXEC SQL FETCH FROM mycursor
INTO :myuser,:myname,:mysize,:mymtime;
if(NO_MORE_TUPLES)
break;
myname[120] = '\0';
printf("%d\t%s\t%d\t%d\n", myuser, myname, mysize, mymtime);
}
EXEC SQL CLOSE mycursor;
exit(0);
}
-----------------------------------
No idea how long it's going to take to complete its run, but it
faithfully starts spitting out the records shortly after being
invoked. Thanks again guys!
For those interested, more helpful hints & examples on epgc,
libpq and related goodies here:
http://www.cse.ucsc.edu/classes/cmps180/Winter02/proj5.html
- thanks to UCSC and Google. :-)
-cw-
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-02-12 21:14 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-11 23:42 Working with very large datasets Wilkinson Charlie E <Charlie.E.Wilkinson@irs.gov>
2003-02-12 00:02 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-02-12 00:33 ` Lex Berezhny <LBerezhny@DevIS.com>
2003-02-12 21:14 Re: Working with very large datasets Wilkinson Charlie E <Charlie.E.Wilkinson@irs.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox