Re: Please comment on pgsql speed at handling 550,000 records

Sorry Tom, for the confusion.

seq_id is not equal to go_id

seq_id = NM_000033
go_is = GO:049934

It is bad on my part I wrote
seq_id = 'xxxx'
go_id = 'xxxx' it should be 'yyyy'

srini

--- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Srinivas Iyyer <srini_iyyer_bio(at)yahoo(dot)com> writes:
> > [ It's not fast to do 500K commands like ]
> > insert into tablec (seq_id,gos_id) values (
> > (select seq_id from table a where seq_name
> ='xxxx'),
> > (select gos_id from table b where go_id =
> 'xxxx'));
>
> Don't you want something like
>
> insert into tablec (seq_id,gos_id)
> select seq_id, gos_id from a, b where seq_name =
> go_id;
>
> SQL is not a low-level language, and breaking a
> table-wise operation
> down into bite-size parts is not the way to make it
> go fast. The
> startup overhead for a command is almost always
> going to dwarf the time
> spent processing any one row, so you want to make
> sure you process as
> many rows per command as feasible.
>
> Also, make sure you've ANALYZEd both input tables
> beforehand,
> else the planner may choose a poor plan for this
> command.
> It'd be worth looking at the EXPLAIN output for the
> command
> just to make sure nothing silly is happening.
>
> regards, tom lane
>

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com