Re: Problem with inserts from subselects

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

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

Tyler Ward wrote:

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

Tyler Ward <tjw19(at)columbia(dot)edu> writes:
> 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