pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Hugo Ferreira <bytter@gmail.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Help trying to tune query that executes 40x slower than in SqlServer
Date: Mon, 7 Mar 2005 17:45:32 +0000
Message-ID: <4e8efcf50503070945332778bb@mail.gmail.com> (raw)
In-Reply-To: <200503070928.47118.josh@agliodbs.com>
References: <20050304002724.650A95AF649@svr4.postgresql.org>
	<4e8efcf50503040413245d80f9@mail.gmail.com>
	<4e8efcf50503070901406808be@mail.gmail.com>
	<200503070928.47118.josh@agliodbs.com>

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

Attachments:

query.txt (text/plain, 1.1K) download 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);

view thread (7+ messages)  latest in thread

Message-ID: <4e8efcf50503070945332778bb@mail.gmail.com>
Permalink:  ../4e8efcf50503070945332778bb@mail.gmail.com/
Also on:    postgresql.org/message-id/4e8efcf50503070945332778bb@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: bytter@gmail.com, josh@agliodbs.com
  Subject: Re: Help trying to tune query that executes 40x slower than in SqlServer
  In-Reply-To: <4e8efcf50503070945332778bb@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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