Обсуждение: Help trying to tune query that executes 40x slower than in SqlServer
Hi there :-)
I'm really, really having trouble with this query... It is a part of,
hmmm... 200 similar querys that I dinyamically build and run in a
stored procedure. This one, for example, takes 27seconds to run. The
whole stored procedure executes in about 15minutes. This is too much
when compared to the exact same database, with the same indexes and
same data running under SqlServer 2000, which takes 21seconds to run
the whole batch.
Any help would be extremely appreciated. I've also tried to tune up
the configuration
insert into MRS_REPLICATION_OUT select 514, 10000168, C.contxt_id,
C.contxt_elmt_ix, CAST(null as NUMERIC(18)), CAST(null as
NUMERIC(18)), CAST(null as NUMERIC(18)), CAST(null as NUMERIC(18)),
CAST(null as NUMERIC(18)), null, 1 from c2iedm.CONTXT as P inner join
c2iedm.CONTXT_ELMT as C on (P.contxt_id=C.contxt_id) inner join
MRS_REPLICATION_OUT as S on S.ent_id=10000029 and (CAST(P.contxt_id AS
numeric(18)) = S.pk1) inner join MRS_TRANSACTION TRANS on
TRANS.trans_id=514 left join NON_REPL_DATA_OWNER NRDO on
NRDO.non_repl_data_owner_id=C.owner_id left join REPL_DATA_OWNER_RSDNC
RDOR on RDOR.owner_id=C.owner_id and
RDOR.rsdnc_node_id=TRANS.recv_node_id left join MRS_REPLICATION_OUT
OUT on OUT.trans_id=514 and OUT.ent_id=10000168 and ((CAST(C.contxt_id
AS numeric(18)) = OUT.pk1 AND CAST(C.contxt_elmt_ix AS numeric(18)) =
OUT.pk2)) inner join MRS_TRANSACTION RED_TRANS on
TRANS.prov_node_id=RED_TRANS.prov_node_id and
TRANS.recv_node_id=RED_TRANS.recv_node_id left join
MRS_REPLICATION_OUT RED_OUT on RED_TRANS.cat_code = 'OUT' and
RED_TRANS.trans_type in ('X01', 'X02') and
RED_TRANS.trans_id=RED_OUT.trans_id where S.age=0 and S.trans_id=514
and (NRDO.non_repl_data_owner_id is null) AND (RDOR.repl_data_owner_id
is null) AND (OUT.trans_id is null) AND (RED_OUT.trans_id is null);
This kind of inserts generate few rows. Between 8k and 15k for this particular
insert, and about 20k for the whole batch. If I try to run a batch
to generate about 50k rows, then I'll be stuck here for more that 45h.
Compare this to 12minutes when running SqlServer 2000.
Here is the result of explain analyze:
"Merge Left Join (cost=1338.32..1377.99 rows=45 width=32) (actual
time=719.000..26437.000 rows=14862 loops=1)"
" Merge Cond: ("outer".trans_id = "inner".trans_id)"
" Join Filter: (("outer".cat_code = 'OUT'::bpchar) AND
(("outer".trans_type = 'X01'::bpchar) OR ("outer".trans_type =
'X02'::bpchar)))"
" Filter: ("inner".trans_id IS NULL)"
" -> Sort (cost=1067.36..1067.47 rows=45 width=56) (actual
time=719.000..735.000 rows=14862 loops=1)"
" Sort Key: red_trans.trans_id"
" -> Merge Join (cost=851.66..1066.12 rows=45 width=56)
(actual time=407.000..673.000 rows=14862 loops=1)"
" Merge Cond: ("outer".recv_node_id = "inner".recv_node_id)"
" Join Filter: ("outer".prov_node_id = "inner".prov_node_id)"
" -> Nested Loop Left Join (cost=847.14..987.28
rows=3716 width=60) (actual time=407.000..610.000 rows=14862 loops=1)"
" Join Filter: ((("outer".contxt_id)::numeric(18,0)
= "inner".pk1) AND (("outer".contxt_elmt_ix)::numeric(18,0) =
"inner".pk2))"
" Filter: ("inner".trans_id IS NULL)"
" -> Merge Left Join (cost=718.22..746.87
rows=3716 width=60) (actual time=407.000..563.000 rows=14862 loops=1)"
" Merge Cond: (("outer".recv_node_id =
"inner".rsdnc_node_id) AND ("outer".owner_id = "inner".owner_id))"
" Filter: ("inner".repl_data_owner_id IS NULL)"
" -> Sort (cost=717.19..726.48 rows=3716
width=74) (actual time=407.000..423.000 rows=14862 loops=1)"
" Sort Key: trans.recv_node_id, c.owner_id"
" -> Nested Loop Left Join
(cost=1.01..496.84 rows=3716 width=74) (actual time=0.000..312.000
rows=14862 loops=1)"
" Join Filter:
("inner".non_repl_data_owner_id = "outer".owner_id)"
" Filter:
("inner".non_repl_data_owner_id IS NULL)"
" -> Nested Loop
(cost=0.00..412.22 rows=3716 width=74) (actual time=0.000..186.000
rows=14862 loops=1)"
" -> Seq Scan on
mrs_transaction trans (cost=0.00..2.05 rows=1 width=28) (actual
time=0.000..0.000 rows=1 loops=1)"
" Filter: (trans_id =
514::numeric)"
" -> Nested Loop
(cost=0.00..373.01 rows=3716 width=46) (actual time=0.000..139.000
rows=14862 loops=1)"
" Join Filter:
("outer".contxt_id = "inner".contxt_id)"
" -> Nested Loop
(cost=0.00..4.81 rows=1 width=16) (actual time=0.000..0.000 rows=4
loops=1)"
" Join Filter:
(("inner".contxt_id)::numeric(18,0) = "outer".pk1)"
" -> Index
Scan using ix_mrs_replication_out_all on mrs_replication_out s
(cost=0.00..3.76 rows=1 width=16) (actual time=0.000..0.000 rows=4
loops=1)"
" Index
Cond: ((ent_id = 10000029::numeric) AND (age = 0::numeric) AND
(trans_id = 514::numeric))"
" -> Seq Scan
on contxt p (cost=0.00..1.02 rows=2 width=16) (actual
time=0.000..0.000 rows=2 loops=4)"
" -> Seq Scan on
contxt_elmt c (cost=0.00..275.31 rows=7431 width=46) (actual
time=0.000..7.500 rows=7431 loops=4)"
" -> Materialize
(cost=1.01..1.02 rows=1 width=12) (actual time=0.000..0.001 rows=1
loops=14862)"
" -> Seq Scan on
non_repl_data_owner nrdo (cost=0.00..1.01 rows=1 width=12) (actual
time=0.000..0.000 rows=1 loops=1)"
" -> Sort (cost=1.03..1.03 rows=2 width=42)
(actual time=0.000..0.000 rows=2 loops=1)"
" Sort Key: rdor.rsdnc_node_id, rdor.owner_id"
" -> Seq Scan on repl_data_owner_rsdnc
rdor (cost=0.00..1.02 rows=2 width=42) (actual time=0.000..0.000
rows=2 loops=1)"
" -> Materialize (cost=128.92..128.93 rows=1
width=42) (actual time=0.000..0.000 rows=0 loops=14862)"
" -> Seq Scan on mrs_replication_out "out"
(cost=0.00..128.92 rows=1 width=42) (actual time=0.000..0.000 rows=0
loops=1)"
" Filter: ((trans_id = 514::numeric)
AND (ent_id = 10000168::numeric))"
" -> Sort (cost=4.52..4.73 rows=84 width=52) (actual
time=0.000..15.000 rows=1 loops=1)"
" Sort Key: red_trans.recv_node_id"
" -> Seq Scan on mrs_transaction red_trans
(cost=0.00..1.84 rows=84 width=52) (actual time=0.000..0.000 rows=1
loops=1)"
" -> Sort (cost=270.96..277.78 rows=2728 width=10) (actual
time=0.000..5255.000 rows=8932063 loops=1)"
" Sort Key: red_out.trans_id"
" -> Seq Scan on mrs_replication_out red_out
(cost=0.00..115.28 rows=2728 width=10) (actual time=0.000..0.000
rows=602 loops=1)"
"Total runtime: 27094.000 ms"
Once again, thanks in advance.
Hugo Ferreira
--
GPG Fingerprint: B0D7 1249 447D F5BB 22C5 5B9B 078C 2615 504B 7B85
Hugo,
> insert into MRS_REPLICATION_OUT select 514, 10000168, C.contxt_id,
> C.contxt_elmt_ix, CAST(null as NUMERIC(18)), CAST(null as
> NUMERIC(18)), CAST(null as NUMERIC(18)), CAST(null as NUMERIC(18)),
> CAST(null as NUMERIC(18)), null, 1 from c2iedm.CONTXT as P inner join
> c2iedm.CONTXT_ELMT as C on (P.contxt_id=C.contxt_id) inner join
> MRS_REPLICATION_OUT as S on S.ent_id=10000029 and (CAST(P.contxt_id AS
> numeric(18)) = S.pk1) inner join MRS_TRANSACTION TRANS on
Can you *format* this query please, and re-submit it? Proper query format
looks like:
SELECT a.1, b.2
FROM a JOIN b ON a.1 = b.3
JOIN c ON b.4 = c.1
WHERE a.5 < 6
AND c.7 = '2005-01-01';
... for maximum readability.
Also, when asking others to help debug your queries, it helps them (and,
frankly, you) if you can NOT use single-letter table aliases. Single-letter
table aliases are evil for the same reason that single-letter variables in
code are.
Thanks!
--
Josh Berkus
Aglio Database Solutions
San Francisco
I'm sorry for my unpolite query alignment. Here is the query in a more
human-readable format:
SELECT 514, 10000168, C.contxt_id, C.contxt_elmt_ix, null, null,
null, null, null, null, 1
FROM CONTXT as P INNER JOIN CONTXT_ELMT as C on P.contxt_id = C.contxt_id
INNER JOIN MRS_REPLICATION_OUT as S on S.ent_id=10000029
AND P.contxt_id = S.pk1
INNER JOIN MRS_TRANSACTION TRANS on TRANS.trans_id=514
LEFT JOIN ON_REPL_DATA_OWNER NRDO on
NRDO.non_repl_data_owner_id = C.owner_id
LEFT JOIN REPL_DATA_OWNER_RSDNC RDOR on RDOR.owner_id = C.owner_id
AND RDOR.rsdnc_node_id=TRANS.recv_node_id
LEFT JOIN MRS_REPLICATION_OUT OUT on OUT.trans_id = 514
AND OUT.ent_id=10000168 and C.contxt_id = OUT.pk1
AND C.contxt_elmt_ix = OUT.pk2
INNER JOIN MRS_TRANSACTION RED_TRANS on
TRANS.prov_node_id=RED_TRANS.prov_node_id
AND TRANS.recv_node_id=RED_TRANS.recv_node_id
LEFT JOIN MRS_REPLICATION_OUT RED_OUT on RED_TRANS.cat_code = 'OUT'
AND RED_TRANS.trans_type in ('X01', 'X02')
AND RED_TRANS.trans_id = RED_OUT.trans_id
WHERE S.age=0 and S.trans_id=514
AND (NRDO.non_repl_data_owner_id is null)
AND (RDOR.repl_data_owner_id is null)
AND (OUT.trans_id is null)
AND (RED_OUT.trans_id is null);
Because GMAIL also cuts out text at 80 characters, I also send the
query in attachment.
Once again thanks for your help,
Hugo Ferreira
> Can you *format* this query please, and re-submit it? Proper query format
> looks like:
>
> SELECT a.1, b.2
> FROM a JOIN b ON a.1 = b.3
> JOIN c ON b.4 = c.1
> WHERE a.5 < 6
> AND c.7 = '2005-01-01';
>
> ... for maximum readability.
--
GPG Fingerprint: B0D7 1249 447D F5BB 22C5 5B9B 078C 2615 504B 7B85
Вложения
Hugo Ferreira <bytter@gmail.com> writes:
> SELECT 514, 10000168, C.contxt_id, C.contxt_elmt_ix, null, null,
> null, null, null, null, 1
> FROM CONTXT as P INNER JOIN CONTXT_ELMT as C on P.contxt_id = C.contxt_id
> INNER JOIN MRS_REPLICATION_OUT as S on S.ent_id=10000029
> AND P.contxt_id = S.pk1
> INNER JOIN MRS_TRANSACTION TRANS on TRANS.trans_id=514
> LEFT JOIN ON_REPL_DATA_OWNER NRDO on
> NRDO.non_repl_data_owner_id = C.owner_id
> LEFT JOIN REPL_DATA_OWNER_RSDNC RDOR on RDOR.owner_id = C.owner_id
> AND RDOR.rsdnc_node_id=TRANS.recv_node_id
> LEFT JOIN MRS_REPLICATION_OUT OUT on OUT.trans_id = 514
> AND OUT.ent_id=10000168 and C.contxt_id = OUT.pk1
> AND C.contxt_elmt_ix = OUT.pk2
> INNER JOIN MRS_TRANSACTION RED_TRANS on
> TRANS.prov_node_id=RED_TRANS.prov_node_id
> AND TRANS.recv_node_id=RED_TRANS.recv_node_id
> LEFT JOIN MRS_REPLICATION_OUT RED_OUT on RED_TRANS.cat_code = 'OUT'
> AND RED_TRANS.trans_type in ('X01', 'X02')
> AND RED_TRANS.trans_id = RED_OUT.trans_id
I think the problem is that the intermix of inner and left joins forces
Postgres to do the joins in a particular order, per
http://www.postgresql.org/docs/8.0/static/explicit-joins.html
and this order is quite non optimal for your data. In particular it
looks like joining red_trans to red_out first, instead of last,
would be a good idea (I think but am not 100% certain that this
doesn't change the results).
It is possible but complicated to determine that reordering outer joins
is safe in some cases. We don't currently have such logic in PG. It
may be that SQL Server does have that capability and that's why it's
finding a much better plan ... but for now you have to do that by hand
in PG.
regards, tom lane
Hi,
Well, I think the problem is far more complex than just joins
reordering... I've restrucutred the query so that it won't use any
explicit joins.Instead it now has a series of 'in (select ...)' and
'not exists (select ...)'. This actually got faster... sometimes!!!
select 1, 10000168, C.contxt_id, C.contxt_elmt_ix, null, null, null,
null, null, null, 1
from CONTXT as P, CONTXT_ELMT as C, MRS_REPLICATION_OUT as S,
MRS_TRANSACTION as TRANS
where S.age=0
and S.trans_id=1
and S.trans_id = TRANS.trans_id
and S.ent_id = 10000029
and (P.contxt_id=C.contxt_id) and (P.contxt_id = S.pk1)
and (C.owner_id not in (select non_repl_data_owner_id from
NON_REPL_DATA_OWNER))
AND (C.owner_id not in (select repl_data_owner_id from REPL_DATA_OWNER_RSDNC
where rsdnc_node_id = TRANS.recv_node_id))
AND (not exists (select pk1 from MRS_REPLICATION_OUT
where trans_id=1
and ent_id=10000168
and C.contxt_id = pk1
AND C.contxt_elmt_ix = pk2))
AND (not exists (select pk1 from MRS_TRANSACTION RED_TRANS,
MRS_REPLICATION_OUT RED_OUT
where RED_TRANS.cat_code = 'OUT'
and RED_TRANS.trans_type in ('X01', 'X02')
and RED_TRANS.trans_id=RED_OUT.trans_id
and RED_TRANS.prov_node_id=TRANS.prov_node_id
and RED_TRANS.recv_node_id=TRANS.recv_node_id
and RED_OUT.ent_id=10000168
and C.contxt_id = pk1
AND C.contxt_elmt_ix = pk2))
For example... I run the query, it takes 122seconds. Then I delete the
target tables, vacuum the database, re-run it again: 9s. But if I run
vacuum several times, and then run, it takes again 122seconds. If I
stop this 122seconds query, say, at second 3 and then run it again, it
will only take 9s. It simply doesn't make sense. Also, explain analyse
will give me diferent plans each time I run it... Unfortunately, this
is rendering PostgreSQL unusable for our goals. Any ideas?
By the way, I got the following indexes over MRS_REPLICATION_OUT which
seems to speed up things:
CREATE INDEX ix_mrs_replication_out_all ON mrs_replication_out
USING btree (ent_id, age, trans_id);
CREATE INDEX ix_mrs_replication_pks ON mrs_replication_out
USING btree (trans_id, ent_id, pk1, pk2, pk3, pk4, pk5, pk6, pk7);
Note: pk2... pk7 are nullable columns. trans_id is the least variant
column. pk1 is the most variant column. Most of the times, the
execution plan includes an 'index scan' over the first index
(ix_mrs_replication_out_all), followed by a filter with columns from
the second index (trans_id, ent_id, pk1, pk2, pk3, pk4, pk5, pk6,
pk7), though the 'age' column is not used... Any guess why??
Thanks in advance,
Hugo Ferreira
> It is possible but complicated to determine that reordering outer joins
> is safe in some cases. We don't currently have such logic in PG. It
> may be that SQL Server does have that capability and that's why it's
> finding a much better plan ... but for now you have to do that by hand
> in PG.
--
GPG Fingerprint: B0D7 1249 447D F5BB 22C5 5B9B 078C 2615 504B 7B85
On Wed, 9 Mar 2005 11:08 pm, Hugo Ferreira wrote: > For example... I run the query, it takes 122seconds. Then I delete the > target tables, vacuum the database, re-run it again: 9s. But if I run > vacuum several times, and then run, it takes again 122seconds. If I > stop this 122seconds query, say, at second 3 and then run it again, it > will only take 9s. It simply doesn't make sense. Also, explain analyse > will give me diferent plans each time I run it... Unfortunately, this > is rendering PostgreSQL unusable for our goals. Any ideas? > The explain analyze is still be best information if you want assistance with what postgresql is doing, and how to stop it. If you could attach explain analyzes for both the fast (9s), and slow (122s) runs, that would help people get an idea of how the query is running. At the moment we don't know how postgresql is actually executing the query. Regards Russell Smith.
Hugo,
I think your problem is with the MRS_TRANSACTION TRANS table. It is
not joining anything when declared, but later it is joining thru a LEFT
JOIN of the REPL_DATA_OWNER_RSDNC table. In fact I'm not sure that this
table is really needed. I would suggest rewriting your FROM clause. It
appears a little busy and includes additional filters that are taken
care of in the WHERE clause.
What are the table layouts and what fields are indexed?
Hugo Ferreira wrote:
>Hi there :-)
>
>I'm really, really having trouble with this query... It is a part of,
>hmmm... 200 similar querys that I dinyamically build and run in a
>stored procedure. This one, for example, takes 27seconds to run. The
>whole stored procedure executes in about 15minutes. This is too much
>when compared to the exact same database, with the same indexes and
>same data running under SqlServer 2000, which takes 21seconds to run
>the whole batch.
>
>Any help would be extremely appreciated. I've also tried to tune up
>the configuration
>
>insert into MRS_REPLICATION_OUT select 514, 10000168, C.contxt_id,
>C.contxt_elmt_ix, CAST(null as NUMERIC(18)), CAST(null as
>NUMERIC(18)), CAST(null as NUMERIC(18)), CAST(null as NUMERIC(18)),
>CAST(null as NUMERIC(18)), null, 1 from c2iedm.CONTXT as P inner join
>c2iedm.CONTXT_ELMT as C on (P.contxt_id=C.contxt_id) inner join
>MRS_REPLICATION_OUT as S on S.ent_id=10000029 and (CAST(P.contxt_id AS
>numeric(18)) = S.pk1) inner join MRS_TRANSACTION TRANS on
>TRANS.trans_id=514 left join NON_REPL_DATA_OWNER NRDO on
>NRDO.non_repl_data_owner_id=C.owner_id left join REPL_DATA_OWNER_RSDNC
>RDOR on RDOR.owner_id=C.owner_id and
>RDOR.rsdnc_node_id=TRANS.recv_node_id left join MRS_REPLICATION_OUT
>OUT on OUT.trans_id=514 and OUT.ent_id=10000168 and ((CAST(C.contxt_id
>AS numeric(18)) = OUT.pk1 AND CAST(C.contxt_elmt_ix AS numeric(18)) =
>OUT.pk2)) inner join MRS_TRANSACTION RED_TRANS on
>TRANS.prov_node_id=RED_TRANS.prov_node_id and
>TRANS.recv_node_id=RED_TRANS.recv_node_id left join
>MRS_REPLICATION_OUT RED_OUT on RED_TRANS.cat_code = 'OUT' and
>RED_TRANS.trans_type in ('X01', 'X02') and
>RED_TRANS.trans_id=RED_OUT.trans_id where S.age=0 and S.trans_id=514
>and (NRDO.non_repl_data_owner_id is null) AND (RDOR.repl_data_owner_id
>is null) AND (OUT.trans_id is null) AND (RED_OUT.trans_id is null);
>
>This kind of inserts generate few rows. Between 8k and 15k for this particular
>insert, and about 20k for the whole batch. If I try to run a batch
>to generate about 50k rows, then I'll be stuck here for more that 45h.
>Compare this to 12minutes when running SqlServer 2000.
>
>Here is the result of explain analyze:
>
>"Merge Left Join (cost=1338.32..1377.99 rows=45 width=32) (actual
>time=719.000..26437.000 rows=14862 loops=1)"
>" Merge Cond: ("outer".trans_id = "inner".trans_id)"
>" Join Filter: (("outer".cat_code = 'OUT'::bpchar) AND
>(("outer".trans_type = 'X01'::bpchar) OR ("outer".trans_type =
>'X02'::bpchar)))"
>" Filter: ("inner".trans_id IS NULL)"
>" -> Sort (cost=1067.36..1067.47 rows=45 width=56) (actual
>time=719.000..735.000 rows=14862 loops=1)"
>" Sort Key: red_trans.trans_id"
>" -> Merge Join (cost=851.66..1066.12 rows=45 width=56)
>(actual time=407.000..673.000 rows=14862 loops=1)"
>" Merge Cond: ("outer".recv_node_id = "inner".recv_node_id)"
>" Join Filter: ("outer".prov_node_id = "inner".prov_node_id)"
>" -> Nested Loop Left Join (cost=847.14..987.28
>rows=3716 width=60) (actual time=407.000..610.000 rows=14862 loops=1)"
>" Join Filter: ((("outer".contxt_id)::numeric(18,0)
>= "inner".pk1) AND (("outer".contxt_elmt_ix)::numeric(18,0) =
>"inner".pk2))"
>" Filter: ("inner".trans_id IS NULL)"
>" -> Merge Left Join (cost=718.22..746.87
>rows=3716 width=60) (actual time=407.000..563.000 rows=14862 loops=1)"
>" Merge Cond: (("outer".recv_node_id =
>"inner".rsdnc_node_id) AND ("outer".owner_id = "inner".owner_id))"
>" Filter: ("inner".repl_data_owner_id IS NULL)"
>" -> Sort (cost=717.19..726.48 rows=3716
>width=74) (actual time=407.000..423.000 rows=14862 loops=1)"
>" Sort Key: trans.recv_node_id, c.owner_id"
>" -> Nested Loop Left Join
>(cost=1.01..496.84 rows=3716 width=74) (actual time=0.000..312.000
>rows=14862 loops=1)"
>" Join Filter:
>("inner".non_repl_data_owner_id = "outer".owner_id)"
>" Filter:
>("inner".non_repl_data_owner_id IS NULL)"
>" -> Nested Loop
>(cost=0.00..412.22 rows=3716 width=74) (actual time=0.000..186.000
>rows=14862 loops=1)"
>" -> Seq Scan on
>mrs_transaction trans (cost=0.00..2.05 rows=1 width=28) (actual
>time=0.000..0.000 rows=1 loops=1)"
>" Filter: (trans_id =
>514::numeric)"
>" -> Nested Loop
>(cost=0.00..373.01 rows=3716 width=46) (actual time=0.000..139.000
>rows=14862 loops=1)"
>" Join Filter:
>("outer".contxt_id = "inner".contxt_id)"
>" -> Nested Loop
>(cost=0.00..4.81 rows=1 width=16) (actual time=0.000..0.000 rows=4
>loops=1)"
>" Join Filter:
>(("inner".contxt_id)::numeric(18,0) = "outer".pk1)"
>" -> Index
>Scan using ix_mrs_replication_out_all on mrs_replication_out s
>(cost=0.00..3.76 rows=1 width=16) (actual time=0.000..0.000 rows=4
>loops=1)"
>" Index
>Cond: ((ent_id = 10000029::numeric) AND (age = 0::numeric) AND
>(trans_id = 514::numeric))"
>" -> Seq Scan
>on contxt p (cost=0.00..1.02 rows=2 width=16) (actual
>time=0.000..0.000 rows=2 loops=4)"
>" -> Seq Scan on
>contxt_elmt c (cost=0.00..275.31 rows=7431 width=46) (actual
>time=0.000..7.500 rows=7431 loops=4)"
>" -> Materialize
>(cost=1.01..1.02 rows=1 width=12) (actual time=0.000..0.001 rows=1
>loops=14862)"
>" -> Seq Scan on
>non_repl_data_owner nrdo (cost=0.00..1.01 rows=1 width=12) (actual
>time=0.000..0.000 rows=1 loops=1)"
>" -> Sort (cost=1.03..1.03 rows=2 width=42)
>(actual time=0.000..0.000 rows=2 loops=1)"
>" Sort Key: rdor.rsdnc_node_id, rdor.owner_id"
>" -> Seq Scan on repl_data_owner_rsdnc
>rdor (cost=0.00..1.02 rows=2 width=42) (actual time=0.000..0.000
>rows=2 loops=1)"
>" -> Materialize (cost=128.92..128.93 rows=1
>width=42) (actual time=0.000..0.000 rows=0 loops=14862)"
>" -> Seq Scan on mrs_replication_out "out"
>(cost=0.00..128.92 rows=1 width=42) (actual time=0.000..0.000 rows=0
>loops=1)"
>" Filter: ((trans_id = 514::numeric)
>AND (ent_id = 10000168::numeric))"
>" -> Sort (cost=4.52..4.73 rows=84 width=52) (actual
>time=0.000..15.000 rows=1 loops=1)"
>" Sort Key: red_trans.recv_node_id"
>" -> Seq Scan on mrs_transaction red_trans
>(cost=0.00..1.84 rows=84 width=52) (actual time=0.000..0.000 rows=1
>loops=1)"
>" -> Sort (cost=270.96..277.78 rows=2728 width=10) (actual
>time=0.000..5255.000 rows=8932063 loops=1)"
>" Sort Key: red_out.trans_id"
>" -> Seq Scan on mrs_replication_out red_out
>(cost=0.00..115.28 rows=2728 width=10) (actual time=0.000..0.000
>rows=602 loops=1)"
>"Total runtime: 27094.000 ms"
>
>Once again, thanks in advance.
>
>Hugo Ferreira
>--
>GPG Fingerprint: B0D7 1249 447D F5BB 22C5 5B9B 078C 2615 504B 7B85
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
>
>