pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with inserts from subselects
4+ messages / 3 participants
[nested] [flat]
* Problem with inserts from subselects
@ 2004-04-21 15:44 Tyler Ward <tjw19@columbia.edu>
0 siblings, 0 replies; 4+ messages in thread
From: Tyler Ward @ 2004-04-21 15:44 UTC (permalink / raw)
To: 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@columbia.edu
^ permalink raw reply [nested|flat] 4+ messages in thread
* Problem with inserts from subselects
@ 2004-04-23 14:30 Tyler Ward <tjw19@columbia.edu>
2004-04-23 14:54 ` Re: Problem with inserts from subselects banghe <banghe@baileylink.net>
2004-04-24 03:02 ` Re: Problem with inserts from subselects Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: Tyler Ward @ 2004-04-23 14:30 UTC (permalink / raw)
To: pgsql-admin@postgresql.org, Tyler Ward <tward@janestcapital.com>
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@columbia.edu
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Problem with inserts from subselects
2004-04-23 14:30 Problem with inserts from subselects Tyler Ward <tjw19@columbia.edu>
@ 2004-04-23 14:54 ` banghe <banghe@baileylink.net>
1 sibling, 0 replies; 4+ messages in thread
From: banghe @ 2004-04-23 14:54 UTC (permalink / raw)
To: Tyler Ward <tjw19@columbia.edu>; +Cc: pgsql-admin@postgresql.org, Tyler Ward <tward@janestcapital.com>
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@columbia.edu
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Problem with inserts from subselects
2004-04-23 14:30 Problem with inserts from subselects Tyler Ward <tjw19@columbia.edu>
@ 2004-04-24 03:02 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2004-04-24 03:02 UTC (permalink / raw)
To: Tyler Ward <tjw19@columbia.edu>; +Cc: pgsql-admin@postgresql.org, Tyler Ward <tward@janestcapital.com>
Tyler Ward <tjw19@columbia.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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-04-24 03:02 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-21 15:44 Problem with inserts from subselects Tyler Ward <tjw19@columbia.edu>
2004-04-23 14:30 Problem with inserts from subselects Tyler Ward <tjw19@columbia.edu>
2004-04-23 14:54 ` banghe <banghe@baileylink.net>
2004-04-24 03:02 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox