Problem with inserts from subselects

[Tyler Ward]

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
***@columbia.edu



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[banghe]

Can you try in this way:

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 ;

This is without ( ).

Banghe

Post by Tyler Ward
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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Tom Lane]

Post by Tyler Ward
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.

Could we see EXPLAIN ANALYZE output from all three cases? I'd have
expected "INSERT ... SELECT foo" to generate the same plan as
"SELECT foo", but it sounds like you're getting something different.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html