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

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