Skip site navigation (1) Skip section navigation (2)

Problem with inserts from subselects

From: Tyler Ward <tjw19(at)columbia(dot)edu>
To: pgsql-admin(at)postgresql(dot)org
Subject: Problem with inserts from subselects
Date: 2004-04-21 15:44:23
Message-ID: 408696D7.9090103@columbia.edu (view raw or flat)
Thread:
Lists: pgsql-admin

I'm using postgres 7.3.x (the default installed on Fedora Core 1) and 
I'm running into a serious
bug in the insert performance.

When I try a query like this...

<snip>

INSERT INTO table_a (session, sent, data_row, direction_id, instrument, 
price, size)
(
    select tmp.session, tmp.sent, tmp.data_row, tmp.direction_id, i.id, 
tmp.price, tmp.size
    FROM table_b tmp
    INNER JOIN table_c i ON i.symbol_id = tmp.symbol_id AND i.route_id = 
tmp.route_id
    LIMIT 10
);

</snip>

the performance is really slow, that query takes about 10 seconds to 
run. If I raise the limit to 100, it takes
10 times longer, etc.... It is basically so slow that we can't use our 
database at all. However, if I just run the
inner select, like this....

<snip>
    select tmp.session, tmp.sent, tmp.data_row, tmp.direction_id, i.id, 
tmp.price, tmp.size
    FROM table_b tmp
    INNER JOIN table_c i ON i.symbol_id = tmp.symbol_id AND i.route_id = 
tmp.route_id
    LIMIT 10
</snip>

The result returns basically instantly, in less than half a second.

If I just insert the data by hand....

<snip>
INSERT INTO table_a (session, sent, data_row, direction_id, instrument, 
price, size) VALUES (1, NOW(), 820183, 1, 852, 1.0, 10);
INSERT INTO table_a (session, sent, data_row, direction_id, instrument, 
price, size) VALUES (1, NOW(), 820184, 1, 852, 1.0, 10);
INSERT INTO table_a (session, sent, data_row, direction_id, instrument, 
price, size) VALUES (1, NOW(), 820185, 1, 852, 1.0, 10);
INSERT INTO table_a (session, sent, data_row, direction_id, instrument, 
price, size) VALUES (1, NOW(), 820186, 1, 852, 1.0, 10);
INSERT INTO table_a (session, sent, data_row, direction_id, instrument, 
price, size) VALUES (1, NOW(), 820187, 1, 852, 1.0, 10);
INSERT INTO table_a (session, sent, data_row, direction_id, instrument, 
price, size) VALUES (1, NOW(), 820188, 1, 852, 1.0, 10);
INSERT INTO table_a (session, sent, data_row, direction_id, instrument, 
price, size) VALUES (1, NOW(), 820189, 1, 852, 1.0, 10);
INSERT INTO table_a (session, sent, data_row, direction_id, instrument, 
price, size) VALUES (1, NOW(), 820190, 1, 852, 1.0, 10);
INSERT INTO table_a (session, sent, data_row, direction_id, instrument, 
price, size) VALUES (1, NOW(), 820191, 1, 852, 1.0, 10);
INSERT INTO table_a (session, sent, data_row, direction_id, instrument, 
price, size) VALUES (1, NOW(), 820192, 1, 852, 1.0, 10);
</snip>

then it's fast again. The above query finishes all ten inserts in less 
than half a second.

So it seems that the problem only occurs when inserting data from a 
select clause, what's going on? And more importantly, how can I
fix it?


-Tyler
tjw19(at)columbia(dot)edu




pgsql-admin by date

Next:From: Corey.BruneDate: 2004-04-21 16:05:42
Subject: Re: problem with pg_dump and pg_restore
Previous:From: Fischer UlrichDate: 2004-04-21 13:44:08
Subject: Re: problem with pg_dump and pg_restore

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group