API tools faq
paste
Guest User

Untitled

a guest
Jul 21st, 2018
89
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 15.03 KB | None | 0 0
raw download clone embed print report
  1. org 1 mid 439
  2. SELECT buildrunsql(439, 'xd_paid_auth_val,xd_paid_pass_wd');
  3.  
  4.  
  5. SELECT am.progresscounter, am.json, am.segmentid,
  6.        c.localpart || '@' || d.domainname AS emailaddress,
  7.        c.localpart, d.domainname AS DOMAIN,
  8.        c.statusid, d.domainid, d.domainname,
  9.        ct.description AS contenttype, cd.*,
  10.        cd.firstname||' '||cd.lastname AS fullname ,
  11. xda1.xd_paid_auth_val,
  12. xda2.xd_paid_pass_wd
  13.  FROM contacts c
  14.  INNER JOIN activemailings_0000439 am ON c.contactid=am.contactid
  15.  INNER JOIN domains d USING (domainid)
  16.  INNER JOIN contactdetails cd ON c.contactid=cd.contactid
  17.  INNER JOIN contenttypes ct USING (contenttypeid)
  18. LEFT JOIN (
  19.         SELECT FIRST(xd_paid_auth_val) AS xd_paid_auth_val,xd_contacts_contactid
  20.           FROM (SELECT relation_5.*,testlisttomailing.testlisttomailingid AS xd_testlisttomailing_testlisttomailingid,testlisttomailing.testlistid AS xd_testlisttomailing_testlistid,testlisttomailing.mailingid AS xd_testlisttomailing_mailingid,testlisttomailing.orgid AS xd_testlisttomailing_orgid FROM ( SELECT relation_1.*,paid.contactid AS xd_paid_contactid,paid.ctm_nbr AS xd_paid_ctm_nbr,paid.fin_itg AS xd_paid_fin_itg,paid.pass_wd AS xd_paid_pass_wd,paid.auth_val AS xd_paid_auth_val,paid.testlistid AS xd_paid_testlistid FROM ( SELECT contacts.contactid AS xd_contacts_contactid,contacts.statusid AS xd_contacts_statusid,contacts.localpart AS xd_contacts_localpart,contacts.domainid AS xd_contacts_domainid FROM org0000001.contacts) relation_1 INNER JOIN org0000001rel.paid ON  xd_contacts_contactid = contactid) relation_5 INNER JOIN relational.testlisttomailing ON  xd_paid_testlistid = testlistid) rel  WHERE xd_testlisttomailing_mailingid = 439
  21.          GROUP BY xd_contacts_contactid) xda1 ON xda1.xd_contacts_contactid = cd.contactid
  22. LEFT JOIN (
  23.         SELECT FIRST(xd_paid_pass_wd) AS xd_paid_pass_wd,xd_contacts_contactid
  24.           FROM (SELECT relation_5.*,testlisttomailing.testlisttomailingid AS xd_testlisttomailing_testlisttomailingid,testlisttomailing.testlistid AS xd_testlisttomailing_testlistid,testlisttomailing.mailingid AS xd_testlisttomailing_mailingid,testlisttomailing.orgid AS xd_testlisttomailing_orgid FROM ( SELECT relation_1.*,paid.contactid AS xd_paid_contactid,paid.ctm_nbr AS xd_paid_ctm_nbr,paid.fin_itg AS xd_paid_fin_itg,paid.pass_wd AS xd_paid_pass_wd,paid.auth_val AS xd_paid_auth_val,paid.testlistid AS xd_paid_testlistid FROM ( SELECT contacts.contactid AS xd_contacts_contactid,contacts.statusid AS xd_contacts_statusid,contacts.localpart AS xd_contacts_localpart,contacts.domainid AS xd_contacts_domainid FROM org0000001.contacts) relation_1 INNER JOIN org0000001rel.paid ON  xd_contacts_contactid = contactid) relation_5 INNER JOIN relational.testlisttomailing ON  xd_paid_testlistid = testlistid) rel  WHERE xd_testlisttomailing_mailingid = 439
  25.          GROUP BY xd_contacts_contactid) xda2 ON xda2.xd_contacts_contactid = cd.contactid
  26.  WHERE c.statusid = 1
  27.  AND am.progresscounter BETWEEN 0 AND 10000
  28.  AND NOT EXISTS (SELECT * FROM activemailingsgenerated_0000439 amg WHERE progresscounter = am.progresscounter)
  29.  ORDER BY am.progresscounter ASC
  30.  
  31. EXPLAIN analyze results:
  32.  
  33. QUERY PLAN
  34.  Sort  (cost=85294.53..85294.53 ROWS=1 width=646) (actual TIME=248249.054..248249.054 ROWS=2 loops=1)
  35.    Sort KEY: am.progresscounter
  36.    Sort Method:  quicksort  Memory: 25kB
  37.    ->  Nested Loop LEFT JOIN  (cost=82851.94..85294.52 ROWS=1 width=646) (actual TIME=248041.575..248249.031 ROWS=2 loops=1)
  38.          JOIN FILTER: (org0000001.contacts.contactid = cd.contactid)
  39.          ->  Nested Loop  (cost=42375.77..44686.60 ROWS=1 width=614) (actual TIME=244890.133..245037.209 ROWS=2 loops=1)
  40.                ->  Nested Loop  (cost=42375.77..44686.32 ROWS=1 width=600) (actual TIME=244890.122..245037.174 ROWS=2 loops=1)
  41.                      ->  Nested Loop  (cost=42375.77..44685.97 ROWS=1 width=585) (actual TIME=244884.145..245018.762 ROWS=2 loops=1)
  42.                            ->  Nested Loop LEFT JOIN  (cost=42375.77..44684.10 ROWS=1 width=577) (actual TIME=244884.121..245018.719 ROWS=2 loops=1)
  43.                                  JOIN FILTER: (org0000001.contacts.contactid = cd.contactid)
  44.                                  ->  Nested Loop  (cost=1899.61..4076.19 ROWS=1 width=545) (actual TIME=48.161..118.490 ROWS=2 loops=1)
  45.                                        ->  Hash Anti JOIN  (cost=1899.61..4073.67 ROWS=1 width=48) (actual TIME=48.139..107.180 ROWS=2 loops=1)
  46.                                              Hash Cond: (am.progresscounter = amg.progresscounter)
  47.                                              ->  Seq Scan ON activemailings_0000439 am  (cost=0.00..1394.49 ROWS=62366 width=48) (actual TIME=0.010..42.173 ROWS=62366 loops=1)
  48.                                                    FILTER: ((progresscounter >= 0) AND (progresscounter <= 100000))
  49.                                              ->  Hash  (cost=1115.38..1115.38 ROWS=62738 width=4) (actual TIME=39.385..39.385 ROWS=62364 loops=1)
  50.                                                    Buckets: 8192  Batches: 1  Memory Usage: 2193kB
  51.                                                    ->  Seq Scan ON activemailingsgenerated_0000439 amg  (cost=0.00..1115.38 ROWS=62738 width=4) (actual TIME=0.007..18.415 ROWS=62364 loops=1)
  52.                                        ->  INDEX Scan USING contactdetails_pkey ON contactdetails cd  (cost=0.00..2.51 ROWS=1 width=497) (actual TIME=5.643..5.648 ROWS=1 loops=2)
  53.                                              INDEX Cond: (cd.contactid = am.contactid)
  54.                                  ->  HashAggregate  (cost=40476.17..40523.22 ROWS=3764 width=21) (actual TIME=122417.069..122442.246 ROWS=81881 loops=2)
  55.                                        ->  Nested Loop LEFT JOIN  (cost=0.00..40457.35 ROWS=3764 width=21) (actual TIME=0.151..243576.328 ROWS=120946 loops=1)
  56.                                              JOIN FILTER: ((cvi.auth_grp)::text = al.portal)
  57.                                              ->  Nested Loop  (cost=0.00..34622.53 ROWS=3764 width=23) (actual TIME=0.106..30784.896 ROWS=84642 loops=1)
  58.                                                    ->  Nested Loop  (cost=0.00..32565.93 ROWS=3764 width=23) (actual TIME=0.095..3742.272 ROWS=84642 loops=1)
  59.                                                          ->  Nested Loop  (cost=0.00..4639.05 ROWS=5837 width=15) (actual TIME=0.058..455.763 ROWS=62459 loops=1)
  60.                                                                ->  Nested Loop  (cost=0.00..12.55 ROWS=1 width=8) (actual TIME=0.017..0.020 ROWS=1 loops=1)
  61.                                                                      ->  INDEX Scan USING testlisttomailing_mailingid_orgid_unidx ON testlisttomailing  (cost=0.00..6.27 ROWS=1 width=4) (actual TIME=0.007..0.009 ROWS=1 loops=1)
  62.                                                                            INDEX Cond: (mailingid = 439)
  63.                                                                      ->  INDEX Scan USING testlists_pk ON testlists al  (cost=0.00..6.27 ROWS=1 width=12) (actual TIME=0.008..0.009 ROWS=1 loops=1)
  64.                                                                            INDEX Cond: (al.testlistid = relational.testlisttomailing.testlistid)
  65.                                                                            FILTER: (al.TYPE = 'PAID'::text)
  66.                                                                ->  INDEX Scan USING cirsub_m_pkey ON cirsub_m sub  (cost=0.00..4548.95 ROWS=6204 width=17) (actual TIME=0.034..427.325 ROWS=62459 loops=1)
  67.                                                                      INDEX Cond: ((sub.pub_cde)::text = al.advantagecode)
  68.                                                                      FILTER: (sub.crc_sts = ANY ('{R,P,Q,W}'::bpchar[]))
  69.                                                           ->  INDEX Scan USING pe2_fast_lookup_ctm_nbr_idx_new ON pe2_fast_lookup fl  (cost=0.00..4.77 ROWS=1 width=21) (actual TIME=0.037..0.051 ROWS=1 loops=62459)
  70.                                                                INDEX Cond: ((fl.ctm_nbr)::text = (sub.ctm_nbr)::text)
  71.                                                                FILTER: (fl.orgid = 1)
  72.                                                    ->  INDEX Scan USING idx_contacts1 ON contacts  (cost=0.00..0.53 ROWS=1 width=8) (actual TIME=0.316..0.318 ROWS=1 loops=84642)
  73.                                                          INDEX Cond: (org0000001.contacts.contactid = fl.contactid)
  74.                                              ->  INDEX Scan USING cdscvi_m_ctm_nbr_idx ON cdscvi_m cvi  (cost=0.00..1.51 ROWS=3 width=28) (actual TIME=1.229..2.511 ROWS=4 loops=84642)
  75.                                                    INDEX Cond: ((cvi.ctm_nbr)::text = (fl.ctm_nbr)::text)
  76.                                                    FILTER: ((cvi.auth_sta)::text = 'A'::text)
  77.                            ->  INDEX Scan USING idx_contacts1 ON contacts c  (cost=0.00..1.85 ROWS=1 width=24) (actual TIME=0.017..0.018 ROWS=1 loops=2)
  78.                                  INDEX Cond: (c.contactid = am.contactid)
  79.                                  FILTER: (c.statusid = 1)
  80.                      ->  INDEX Scan USING domains_pkey ON domains d  (cost=0.00..0.34 ROWS=1 width=19) (actual TIME=9.202..9.203 ROWS=1 loops=2)
  81.                            INDEX Cond: (d.domainid = c.domainid)
  82.                ->  INDEX Scan USING contenttypes_pkey ON contenttypes ct  (cost=0.00..0.27 ROWS=1 width=16) (actual TIME=0.007..0.012 ROWS=1 loops=2)
  83.                      INDEX Cond: (ct.contenttypeid = cd.contenttypeid)
  84.          ->  HashAggregate  (cost=40476.17..40523.22 ROWS=3764 width=15) (actual TIME=1575.181..1598.102 ROWS=81881 loops=2)
  85.                ->  Nested Loop LEFT JOIN  (cost=0.00..40457.35 ROWS=3764 width=15) (actual TIME=0.130..2721.998 ROWS=120946 loops=1)
  86.                      JOIN FILTER: ((cvi.auth_grp)::text = al.portal)
  87.                      ->  Nested Loop  (cost=0.00..34622.53 ROWS=3764 width=23) (actual TIME=0.088..1517.996 ROWS=84642 loops=1)
  88.                            ->  Nested Loop  (cost=0.00..32565.93 ROWS=3764 width=23) (actual TIME=0.080..1223.953 ROWS=84642 loops=1)
  89.                                  ->  Nested Loop  (cost=0.00..4639.05 ROWS=5837 width=15) (actual TIME=0.049..138.212 ROWS=62459 loops=1)
  90.                                        ->  Nested Loop  (cost=0.00..12.55 ROWS=1 width=8) (actual TIME=0.015..0.017 ROWS=1 loops=1)
  91.                                              ->  INDEX Scan USING testlisttomailing_mailingid_orgid_unidx ON testlisttomailing  (cost=0.00..6.27 ROWS=1 width=4) (actual TIME=0.006..0.007 ROWS=1 loops=1)
  92.                                                    INDEX Cond: (mailingid = 439)
  93.                                              ->  INDEX Scan USING testlists_pk ON testlists al  (cost=0.00..6.27 ROWS=1 width=12) (actual TIME=0.007..0.008 ROWS=1 loops=1)
  94.                                                    INDEX Cond: (al.testlistid = relational.testlisttomailing.testlistid)
  95.                                                    FILTER: (al.TYPE = 'PAID'::text)
  96.                                        ->  INDEX Scan USING cirsub_m_pkey ON cirsub_m sub  (cost=0.00..4548.95 ROWS=6204 width=17) (actual TIME=0.028..126.288 ROWS=62459 loops=1)
  97.                                              INDEX Cond: ((sub.pub_cde)::text = al.advantagecode)
  98.                                              FILTER: (sub.crc_sts = ANY ('{R,P,Q,W}'::bpchar[]))
  99.                                  ->  INDEX Scan USING pe2_fast_lookup_ctm_nbr_idx_new ON pe2_fast_lookup fl  (cost=0.00..4.77 ROWS=1 width=21) (actual TIME=0.014..0.017 ROWS=1 loops=62459)
  100.                                        INDEX Cond: ((fl.ctm_nbr)::text = (sub.ctm_nbr)::text)
  101.                                        FILTER: (fl.orgid = 1)
  102.                            ->  INDEX Scan USING idx_contacts1 ON contacts  (cost=0.00..0.53 ROWS=1 width=8) (actual TIME=0.003..0.003 ROWS=1 loops=84642)
  103.                                  INDEX Cond: (org0000001.contacts.contactid = fl.contactid)
  104.                      ->  INDEX Scan USING cdscvi_m_ctm_nbr_idx ON cdscvi_m cvi  (cost=0.00..1.51 ROWS=3 width=22) (actual TIME=0.011..0.013 ROWS=4 loops=84642)
  105.                            INDEX Cond: ((cvi.ctm_nbr)::text = (fl.ctm_nbr)::text)
  106.                            FILTER: ((cvi.auth_sta)::text = 'A'::text)
  107. Total runtime: 248249.672 ms
  108. (74 ROWS)
  109. TIME: 248280.659 ms
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!