Re: Help trying to tune query that executes 40x slower than in SqlServer

Hugo Ferreira <bytter(at)gmail(dot)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