Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pQ9Ed-0006xv-DZ for pgsql-hackers@arkaria.postgresql.org; Thu, 09 Feb 2023 15:53:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pQ9Ec-00014s-94 for pgsql-hackers@arkaria.postgresql.org; Thu, 09 Feb 2023 15:53:18 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pQ9Eb-00013q-N6 for pgsql-hackers@lists.postgresql.org; Thu, 09 Feb 2023 15:53:17 +0000 Received: from mail-ej1-x62d.google.com ([2a00:1450:4864:20::62d]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pQ9EY-0003fR-SF for pgsql-hackers@lists.postgresql.org; Thu, 09 Feb 2023 15:53:16 +0000 Received: by mail-ej1-x62d.google.com with SMTP id m2so7650938ejb.8 for ; Thu, 09 Feb 2023 07:53:14 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; h=in-reply-to:references:to:from:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=bqSOpsWrAd2DjxpTinC/023jl2qK27DUSeccYFk8boY=; b=e2Ni9ggSbOhGvHVQEwA4b9EZHOA6+n+WeRsZFc6Jcq/EUnc/dX24FBY8YC2csLL8+d 7UIpL1RDZsVoZfb2J1okWZYqTJ0Jfn9hBjmVmEHqQc/8/I3haESEa8T23ySgt3rQ5rA1 u4wXa4lRGZbNvXgLzf4Hvvm21QhAskqCDgquTJIdOHgNjGe+f36qB3NWkpQZOzchtPT4 2QlfhmgPHJES6AbmaSxZxqtTH5KkOGPqDsc1p7pdqr/Oba/XJgsaWdoufLaT8FXm2S6M YUz3YWUFAyjBedDX0h0nBLKpe/fSd3vrP9GnXze/W1z/nR/PCA4h7mZWCeh/UNcMRkUd hhYQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:references:to:from:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=bqSOpsWrAd2DjxpTinC/023jl2qK27DUSeccYFk8boY=; b=SAL1t3TR+0MxcdVIV6E7Woh57tMFL1xKni41LDiwLpA9zMSsCaDg7OA6nB3pO8Ndzh Con0SBQbcNXcV9GuyK7exgkx9qn08rjiORSsxIpQszY5xBQ3yJEMZYOaNP0OLcIwyFYL Tll2w4qCwj1LWr7LPppK7J27kO8tHyhHdSL+T7//1Fmkk7O/CENpo2zoGG7Uk/jCcbnv zGI1nL4ag3h/NgbUH8/mSAfKI9x3Fw9MKn07EklsjBYQmG7suiaCA4RD2ZB/yOB4KnI0 7Gi3ZI9Yn3JlD5pUgchxpUt90VCMTIuUdr7SkmN1hOMAZrSdceX7qmcOz/TWvfV5ribx xl0g== X-Gm-Message-State: AO0yUKU9m14m+gLkbzyQMppRs1kmykMghlc9Wfa3uwH85mSW20Stz//7 MZyvTOzr3l5IQYN8Ul4h6Ky0D6Aw1cHuGptO4R3NMLVwaW8I3NAEA4uVb7brv3AC8wB0M7V+WN6 n3ayzTYvuRVOZgYr7N6HMhjCExbf1XLFJIB90pwmSmcHPyJRpTtuWSmH+a6+Ty2Tzs7j5m5eJSy yLtONjIUYKnNu12rabnxNvSxvYWEEhH2d/JLgRi5R/MGy+IV0ZgehlLKo/stVggTcPsi6+Ic70Z yRYNLiDzg1AlwP2FywatBkWA8I/7CjjJaTLd+OrVBMQDswUMK8Pz0e9W39JugJPIHo3YTk= X-Google-Smtp-Source: AK7set/z9Hl18YL79eXTBbzLw3vI5Ox+4bIIGKeWu+tahubslFTp7XzlIwokhApRl995TYMOfFihAg== X-Received: by 2002:a17:906:3e95:b0:895:58be:968 with SMTP id a21-20020a1709063e9500b0089558be0968mr12867961ejj.31.1675957993094; Thu, 09 Feb 2023 07:53:13 -0800 (PST) Received: from [10.137.0.17] (nat-106.starnet.cz. [178.255.168.106]) by smtp.gmail.com with ESMTPSA id kt13-20020a170906aacd00b0087943d525e1sm1010281ejb.215.2023.02.09.07.53.12 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 09 Feb 2023 07:53:12 -0800 (PST) Content-Type: multipart/mixed; boundary="------------eBhWuHJXSSWhaPd4aABP0037" Message-ID: Date: Thu, 9 Feb 2023 16:53:08 +0100 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.7.1 Subject: Re: AW: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 Content-Language: en-US From: Tomas Vondra To: Hans Buschmann , "pgsql-hackers@lists.postgresql.org" References: <4504e67078d648cdac3651b2960da6e7@nidsa.net> <0c8094a63b9942878b29d252a82aeb7e@nidsa.net> <6fbeee5a-7c31-3478-a530-63df3840f553@enterprisedb.com> In-Reply-To: <6fbeee5a-7c31-3478-a530-63df3840f553@enterprisedb.com> X-CLOUD-SEC-AV-Info: enterprisedb,google_mail,monitor X-CLOUD-SEC-AV-Sent: true X-Gm-Spam: 0 X-Gm-Phishy: 0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------eBhWuHJXSSWhaPd4aABP0037 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit > > FWIW I suggest you provide the data in a form that's easier to use (like > a working SQL script). More people are likely to look and help than when > they have to extract stuff from an e-mail, fill in missing pieces etc. > BTW if anyone wants to play with this, here are the SQL scripts I used to create the tables and the queries. There's no data, but it's enough to see how the plans change. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company --------------eBhWuHJXSSWhaPd4aABP0037 Content-Type: application/sql; name="query-complete.sql" Content-Disposition: attachment; filename="query-complete.sql" Content-Transfer-Encoding: base64 ZXhwbGFpbgp3aXRoCnF1cCBhcyAoCiAgICBzZWxlY3QKICAgICAgICAgY3Vycl9zZWFzb24g LS0gYWxsIHh4eF9zZWFzb3NuIGFyZSBhbHdheXMgc21hbGxpbnQKICAgICAgICAsY3Vycl9j b2RlLS0gYWxsIHh4X2NvZGUgYXJlIGFsd2F5cyB2YXJjaGFyKDEwKQogICAgICAgICxhcnJh eV9hZ2coaWRfdXAgb3JkZXIgYnkgaWRfdXApfHxhcnJheV9maWxsKDA6OnNtYWxsaW50LGFy cmF5WzEwXSkgYXMgbWF0X2FycgogICAgICAgICxhcnJheV9hZ2coY3Vycl9tYXRfY29kZSBv cmRlciBieSBpZF91cCkgYXMgbWF0Y29kZV9hcnIKICAgICAgICAsYml0X29yKGltYXNrKSBh cyBpYml0bWFzawogICAgZnJvbSgKICAgICAgICBzZWxlY3QKICAgICAgICAgICAgIHN1cF9z ZWFzb24gYXMgY3Vycl9zZWFzb24KICAgICAgICAgICAgLHN1cF9zYV9jb2RlIGFzIGN1cnJf Y29kZQogICAgICAgICAgICAsc3VwX21hdF9jb2RlIGFzIGN1cnJfbWF0X2NvZGUKICAgICAg ICAgICAgLHN1cF9jbHJfY29kZSBhcyBjdXJyX2Nscl9jb2RlCiAgICAgICAgICAgICxpZF91 cAogICAgICAgICAgICAsY29hbGVzY2UoaWRfdXAsLTEpIGFzIGltYXNrCiAgICAgICAgZnJv bSBvbmx5IHNhX3VwcGVyCiAgICAgICAgbGVmdCBqb2luIHVwcGVyX3RhcmdldCBvbiB1cF9t YXRfY29kZT1zdXBfbWF0X2NvZGUgYW5kIGlkX3VwIDw9ICg1MTItMS0xNikKICAgICkgcXIK ICAgIGdyb3VwIGJ5IDEsMgopCixxbGkgYXMgKAogICAgc2VsZWN0CiAgICAgICAgIGN1cnJf c2Vhc29uCiAgICAgICAgLGN1cnJfY29kZQogICAgICAgICxhcnJheV9hZ2coaWRfbGkgb3Jk ZXIgYnkgaWRfbGkpfHxhcnJheV9maWxsKDA6OnNtYWxsaW50LGFycmF5WzRdKSBhcyBtYXRf YXJyCiAgICAgICAgLGFycmF5X2FnZyhjdXJyX21hdF9jb2RlIG9yZGVyIGJ5IGlkX2xpKSBh cyBtYXRjb2RlX2FycgogICAgICAgICxiaXRfb3IoaW1hc2spIGFzIGliaXRtYXNrCiAgICBm cm9tKAogICAgICAgIHNlbGVjdAogICAgICAgICAgICAgc2xpX3NlYXNvbiBhcyBjdXJyX3Nl YXNvbgogICAgICAgICAgICAsc2xpX3NhX2NvZGUgYXMgY3Vycl9jb2RlCiAgICAgICAgICAg ICxzbGlfbWF0X2NvZGUgYXMgY3Vycl9tYXRfY29kZQogICAgICAgICAgICAsc2xpX2Nscl9j b2RlIGFzIGN1cnJfY2xyX2NvZGUKICAgICAgICAgICAgLGlkX2xpCiAgICAgICAgICAgICxj b2FsZXNjZShpZF9saSwtMSkgYXMgaW1hc2sKICAgICAgICBmcm9tIG9ubHkgc2FfbGluaW5n CiAgICAgICAgbGVmdCBqb2luIGxpbmluZ190YXJnZXQgb24gbGlfbWF0X2NvZGU9c2xpX21h dF9jb2RlIGFuZCBpZF9saSA8PSAoMTI4LTEtOCkKICAgICkgcXIKICAgIGdyb3VwIGJ5IDEs MgopCixxaW4gYXMgKApzZWxlY3QKICAgICBjdXJyX3NlYXNvbgogICAgLGN1cnJfY29kZQog ICAgLGFycmF5X2FnZyhpZF9pbiBvcmRlciBieSBpZF9pbil8fGFycmF5X2ZpbGwoMDo6c21h bGxpbnQsYXJyYXlbNF0pIGFzIG1hdF9hcnIKICAgICxhcnJheV9hZ2coY3Vycl9tYXRfY29k ZSBvcmRlciBieSBpZF9pbikgYXMgbWF0Y29kZV9hcnIKICAgICxiaXRfb3IoaW1hc2spIGFz IGliaXRtYXNrCiAgICBmcm9tKAogICAgICAgIHNlbGVjdAogICAgICAgICAgICAgc2luX3Nl YXNvbiBhcyBjdXJyX3NlYXNvbgogICAgICAgICAgICAsc2luX3NhX2NvZGUgYXMgY3Vycl9j b2RlCiAgICAgICAgICAgICxzaW5fbWF0X2NvZGUgYXMgY3Vycl9tYXRfY29kZQogICAgICAg ICAgICAsc2luX2Nscl9jb2RlIGFzIGN1cnJfY2xyX2NvZGUKICAgICAgICAgICAgLGlkX2lu CiAgICAgICAgICAgICxjb2FsZXNjZShpZF9pbiwtMSkgYXMgaW1hc2sKICAgICAgICBmcm9t IG9ubHkgc2FfaW5zb2xlCiAgICAgICAgbGVmdCBqb2luIGluc29sZV90YXJnZXQgb24gaW5f bWF0X2NvZGU9c2luX21hdF9jb2RlIGFuZCBpZF9pbiA8PSAoMTI4LTEtOCkKICAgICkgcXIK ICAgIGdyb3VwIGJ5IDEsMgopCixxb3UgYXMgKAogICAgc2VsZWN0CiAgICAgICAgIGN1cnJf c2Vhc29uCiAgICAgICAgLGN1cnJfY29kZQogICAgICAgICxhcnJheV9hZ2coaWRfb3Ugb3Jk ZXIgYnkgaWRfb3UpfHxhcnJheV9maWxsKDA6OnNtYWxsaW50LGFycmF5WzZdKSBhcyBtYXRf YXJyCiAgICAgICAgLGFycmF5X2FnZyhjdXJyX21hdF9jb2RlIG9yZGVyIGJ5IGlkX291KSBh cyBtYXRjb2RlX2FycgogICAgICAgICxiaXRfb3IoaW1hc2spIGFzIGliaXRtYXNrCiAgICBm cm9tKAogICAgICAgIHNlbGVjdAogICAgICAgICAgICAgc291X3NlYXNvbiBhcyBjdXJyX3Nl YXNvbgogICAgICAgICAgICAsc291X3NhX2NvZGUgYXMgY3Vycl9jb2RlCiAgICAgICAgICAg ICxzb3VfbWF0X2NvZGUgYXMgY3Vycl9tYXRfY29kZQogICAgICAgICAgICAsc291X2Nscl9j b2RlIGFzIGN1cnJfY2xyX2NvZGUKICAgICAgICAgICAgLGlkX291CiAgICAgICAgICAgICxj b2FsZXNjZShpZF9vdSwtMSkgYXMgaW1hc2sKICAgICAgICBmcm9tIG9ubHkgc2Ffb3V0c29s ZQogICAgICAgIGxlZnQgam9pbiBvdXRzb2xlX3RhcmdldCBvbiBvdV9tYXRfY29kZT1zb3Vf bWF0X2NvZGUgYW5kIGlkX291IDw9ICgzMi0xLTIpCiAgICApIHFyCiAgICBncm91cCBieSAx LDIKKQoscXVwZCBhcyAoCiAgICBzZWxlY3QgKiBmcm9tICgKICAgICAgICBzZWxlY3QKICAg ICAgICAgICAgIHF1cC5jdXJyX3NlYXNvbgogICAgICAgICAgICAscXVwLmN1cnJfY29kZQog ICAgICAgICAgICAscXVwLmliaXRtYXNrfHFpbi5pYml0bWFza3xxbGkuaWJpdG1hc2t8cW91 LmliaXRtYXNrIGFzIGliaXRtYXNrCiAgICAgICAgICAgIC0tIHRoZSBjYWxjdWxhdGlvbnMg b2YgbmV3X21hdF94IGFyZSBzaW1wbGlmaWVkIGhlcmUKICAgICAgICAgICAgLS0gaW4gdGhl IHByb2R1Y3Rpb24gdmVyc2lvbiB0aGV5IGFyZSBhIG1vcmUgY29tcGxleCBjb21iaW5hdGlv biBvZiBiaXQgbWFza3MsIGJpdCBzaGlmdHMgYW5kIGJpdCBvciBvZiBkaWZmZXJlbnQgZWxl bWVudHMgb2YgdGhlIGFycmF5cwogICAgICAgICAgICAsKHF1cC5tYXRfYXJyWzFdfHFsaS5t YXRfYXJyWzFdfHFpbi5tYXRfYXJyWzFdfHFvdS5tYXRfYXJyWzFdKTo6YmlnaW50IGFzIG5l d19tYXRfMQogICAgICAgICAgICAKICAgICAgICAgICAgLChxdXAubWF0X2FyclsyXXxxbGku bWF0X2FyclsyXXxxaW4ubWF0X2FyclsyXXxxb3UubWF0X2FyclsyXSk6OmJpZ2ludCBhcyBu ZXdfbWF0XzIKICAgICAgICAgICAgCiAgICAgICAgICAgICwocXVwLm1hdF9hcnJbM118cWxp Lm1hdF9hcnJbM118cWluLm1hdF9hcnJbM118cW91Lm1hdF9hcnJbM10pOjpiaWdpbnQgYXMg bmV3X21hdF8zCiAgICAgICAgCiAgICAgICAgZnJvbSBxdXAKICAgICAgICBsZWZ0IGpvaW4g cWxpIG9uIChxbGkuY3Vycl9zZWFzb249cXVwLmN1cnJfc2Vhc29uIGFuZCBxbGkuY3Vycl9j b2RlPXF1cC5jdXJyX2NvZGUgYW5kIHFsaS5pYml0bWFzaz4wIGFuZCBjYXJkaW5hbGl0eShx bGkubWF0X2FycikgPD04KQogICAgICAgIGxlZnQgam9pbiBxaW4gb24gKHFpbi5jdXJyX3Nl YXNvbj1xdXAuY3Vycl9zZWFzb24gYW5kIHFpbi5jdXJyX2NvZGU9cXVwLmN1cnJfY29kZSBh bmQgcWluLmliaXRtYXNrPjAgYW5kIGNhcmRpbmFsaXR5KHFpbi5tYXRfYXJyKSA8PTgpCiAg ICAgICAgbGVmdCBqb2luIHFvdSBvbiAocW91LmN1cnJfc2Vhc29uPXF1cC5jdXJyX3NlYXNv biBhbmQgcW91LmN1cnJfY29kZT1xdXAuY3Vycl9jb2RlIGFuZCBxb3UuaWJpdG1hc2s+MCBh bmQgY2FyZGluYWxpdHkocW91Lm1hdF9hcnIpIDw9MTEpCiAgICAgICAgd2hlcmUgcXVwLmli aXRtYXNrPjAgYW5kIGNhcmRpbmFsaXR5KHF1cC5tYXRfYXJyKSA8PTIxCiAgICApcWoKICAg IHdoZXJlIGliaXRtYXNrIGlzIG5vdCBudWxsCikKLHF1cGRhIGFzICgKc2VsZWN0CiBxdXAu Y3Vycl9zZWFzb24KLHF1cC5jdXJyX2NvZGUKLHJlcGVhdCgnMCcsNjQpfHwKcmVwZWF0KCcx MScsY29hbGVzY2UoY2FyZGluYWxpdHkocW91Lm1hdGNvZGVfYXJyKSwwKSl8fHJlcGVhdCgn MTAnLGNvYWxlc2NlKGNhcmRpbmFsaXR5KHFpbi5tYXRjb2RlX2FyciksMCkpfHwKcmVwZWF0 KCcwMScsY29hbGVzY2UoY2FyZGluYWxpdHkocW91Lm1hdGNvZGVfYXJyKSwwKSl8fHJlcGVh dCgnMDAnLGNvYWxlc2NlKGNhcmRpbmFsaXR5KHF1cC5tYXRjb2RlX2FyciksMCkpfHwKJzAw JyBhcyBjdXJyX21hdHR5cGVfYml0bWFzawoscXVwLm1hdGNvZGVfYXJyfHxxbGkubWF0Y29k ZV9hcnJ8fHFpbi5tYXRjb2RlX2Fycnx8cW91Lm1hdGNvZGVfYXJyIGFzIGN1cnJfbWF0Y29k ZV9hcnIKZnJvbSBxdXAKbGVmdCBqb2luIHFsaSBvbiBxbGkuY3Vycl9zZWFzb249cXVwLmN1 cnJfc2Vhc29uIGFuZCBxbGkuY3Vycl9jb2RlPXF1cC5jdXJyX2NvZGUgYW5kIChxbGkuaWJp dG1hc2s8MCBvciBjYXJkaW5hbGl0eShxbGkubWF0X2FycikgPjgpCmxlZnQgam9pbiBxaW4g b24gcWluLmN1cnJfc2Vhc29uPXF1cC5jdXJyX3NlYXNvbiBhbmQgcWluLmN1cnJfY29kZT1x dXAuY3Vycl9jb2RlIGFuZCAocWluLmliaXRtYXNrPDAgb3IgY2FyZGluYWxpdHkocWluLm1h dF9hcnIpID44KQpsZWZ0IGpvaW4gcW91IG9uIHFvdS5jdXJyX3NlYXNvbj1xdXAuY3Vycl9z ZWFzb24gYW5kIHFvdS5jdXJyX2NvZGU9cXVwLmN1cnJfY29kZSBhbmQgKHFvdS5pYml0bWFz azwwIG9yIGNhcmRpbmFsaXR5KHFvdS5tYXRfYXJyKSA+MTEpCndoZXJlIHF1cC5pYml0bWFz azwwIG9yIGNhcmRpbmFsaXR5KHF1cC5tYXRfYXJyKSA+MjEKKQpzZWxlY3QKIGN1cnJfc2Vh c29uCixjdXJyX2NvZGUKLG5ld19tYXRfMQosbmV3X21hdF8yCixuZXdfbWF0XzMKLE5VTEw6 OmJpZ2ludCBhcyBuZXdfbWF0dHlwZV9iaXRtYXNrCixOVUxMIGFzIG5ld19tYXRfY29kZXMK ZnJvbSBxdXBkCnVuaW9uIGFsbApzZWxlY3QKIGN1cnJfc2Vhc29uCixjdXJyX2NvZGUKLE5V TEw6OmJpZ2ludCBhcyBuZXdfbWF0XzEKLE5VTEw6OmJpZ2ludCBhcyBuZXdfbWF0XzIKLE5V TEw6OmJpZ2ludCBhcyBuZXdfbWF0XzMKLHN1YnN0cihjdXJyX21hdHR5cGVfYml0bWFzayxs ZW5ndGgoY3Vycl9tYXR0eXBlX2JpdG1hc2spLTYzKTo6Yml0KDY0KTo6YmlnaW50IGFzIG5l d19tYXR0eXBlX2JpdG1hc2sKLGN1cnJfbWF0Y29kZV9hcnIgYXMgbmV3X21hdF9jb2Rlcwpm cm9tIHF1cGRhCjsK --------------eBhWuHJXSSWhaPd4aABP0037 Content-Type: application/sql; name="query-smaller.sql" Content-Disposition: attachment; filename="query-smaller.sql" Content-Transfer-Encoding: base64 ZXhwbGFpbgp3aXRoCnF1cCBhcyAoCiAgICBzZWxlY3QKICAgICAgICAgY3Vycl9zZWFzb24g LS0gYWxsIHh4eF9zZWFzb3NuIGFyZSBhbHdheXMgc21hbGxpbnQKICAgICAgICAsY3Vycl9j b2RlLS0gYWxsIHh4X2NvZGUgYXJlIGFsd2F5cyB2YXJjaGFyKDEwKQogICAgICAgICxhcnJh eV9hZ2coaWRfdXAgb3JkZXIgYnkgaWRfdXApfHxhcnJheV9maWxsKDA6OnNtYWxsaW50LGFy cmF5WzEwXSkgYXMgbWF0X2FycgogICAgICAgICxhcnJheV9hZ2coY3Vycl9tYXRfY29kZSBv cmRlciBieSBpZF91cCkgYXMgbWF0Y29kZV9hcnIKICAgICAgICAsYml0X29yKGltYXNrKSBh cyBpYml0bWFzawogICAgZnJvbSgKICAgICAgICBzZWxlY3QKICAgICAgICAgICAgIHN1cF9z ZWFzb24gYXMgY3Vycl9zZWFzb24KICAgICAgICAgICAgLHN1cF9zYV9jb2RlIGFzIGN1cnJf Y29kZQogICAgICAgICAgICAsc3VwX21hdF9jb2RlIGFzIGN1cnJfbWF0X2NvZGUKICAgICAg ICAgICAgLHN1cF9jbHJfY29kZSBhcyBjdXJyX2Nscl9jb2RlCiAgICAgICAgICAgICxpZF91 cAogICAgICAgICAgICAsY29hbGVzY2UoaWRfdXAsLTEpIGFzIGltYXNrCiAgICAgICAgZnJv bSBvbmx5IHNhX3VwcGVyCiAgICAgICAgbGVmdCBqb2luIHVwcGVyX3RhcmdldCBvbiB1cF9t YXRfY29kZT1zdXBfbWF0X2NvZGUgYW5kIGlkX3VwIDw9ICg1MTItMS0xNikKICAgICkgcXIK ICAgIGdyb3VwIGJ5IDEsMgopCixxbGkgYXMgKAogICAgc2VsZWN0CiAgICAgICAgIGN1cnJf c2Vhc29uCiAgICAgICAgLGN1cnJfY29kZQogICAgICAgICxhcnJheV9hZ2coaWRfbGkgb3Jk ZXIgYnkgaWRfbGkpfHxhcnJheV9maWxsKDA6OnNtYWxsaW50LGFycmF5WzRdKSBhcyBtYXRf YXJyCiAgICAgICAgLGFycmF5X2FnZyhjdXJyX21hdF9jb2RlIG9yZGVyIGJ5IGlkX2xpKSBh cyBtYXRjb2RlX2FycgogICAgICAgICxiaXRfb3IoaW1hc2spIGFzIGliaXRtYXNrCiAgICBm cm9tKAogICAgICAgIHNlbGVjdAogICAgICAgICAgICAgc2xpX3NlYXNvbiBhcyBjdXJyX3Nl YXNvbgogICAgICAgICAgICAsc2xpX3NhX2NvZGUgYXMgY3Vycl9jb2RlCiAgICAgICAgICAg ICxzbGlfbWF0X2NvZGUgYXMgY3Vycl9tYXRfY29kZQogICAgICAgICAgICAsc2xpX2Nscl9j b2RlIGFzIGN1cnJfY2xyX2NvZGUKICAgICAgICAgICAgLGlkX2xpCiAgICAgICAgICAgICxj b2FsZXNjZShpZF9saSwtMSkgYXMgaW1hc2sKICAgICAgICBmcm9tIG9ubHkgc2FfbGluaW5n CiAgICAgICAgbGVmdCBqb2luIGxpbmluZ190YXJnZXQgb24gbGlfbWF0X2NvZGU9c2xpX21h dF9jb2RlIGFuZCBpZF9saSA8PSAoMTI4LTEtOCkKICAgICkgcXIKICAgIGdyb3VwIGJ5IDEs MgopCixxaW4gYXMgKApzZWxlY3QKICAgICBjdXJyX3NlYXNvbgogICAgLGN1cnJfY29kZQog ICAgLGFycmF5X2FnZyhpZF9pbiBvcmRlciBieSBpZF9pbil8fGFycmF5X2ZpbGwoMDo6c21h bGxpbnQsYXJyYXlbNF0pIGFzIG1hdF9hcnIKICAgICxhcnJheV9hZ2coY3Vycl9tYXRfY29k ZSBvcmRlciBieSBpZF9pbikgYXMgbWF0Y29kZV9hcnIKICAgICxiaXRfb3IoaW1hc2spIGFz IGliaXRtYXNrCiAgICBmcm9tKAogICAgICAgIHNlbGVjdAogICAgICAgICAgICAgc2luX3Nl YXNvbiBhcyBjdXJyX3NlYXNvbgogICAgICAgICAgICAsc2luX3NhX2NvZGUgYXMgY3Vycl9j b2RlCiAgICAgICAgICAgICxzaW5fbWF0X2NvZGUgYXMgY3Vycl9tYXRfY29kZQogICAgICAg ICAgICAsc2luX2Nscl9jb2RlIGFzIGN1cnJfY2xyX2NvZGUKICAgICAgICAgICAgLGlkX2lu CiAgICAgICAgICAgICxjb2FsZXNjZShpZF9pbiwtMSkgYXMgaW1hc2sKICAgICAgICBmcm9t IG9ubHkgc2FfaW5zb2xlCiAgICAgICAgbGVmdCBqb2luIGluc29sZV90YXJnZXQgb24gaW5f bWF0X2NvZGU9c2luX21hdF9jb2RlIGFuZCBpZF9pbiA8PSAoMTI4LTEtOCkKICAgICkgcXIK ICAgIGdyb3VwIGJ5IDEsMgopCixxb3UgYXMgKAogICAgc2VsZWN0CiAgICAgICAgIGN1cnJf c2Vhc29uCiAgICAgICAgLGN1cnJfY29kZQogICAgICAgICxhcnJheV9hZ2coaWRfb3Ugb3Jk ZXIgYnkgaWRfb3UpfHxhcnJheV9maWxsKDA6OnNtYWxsaW50LGFycmF5WzZdKSBhcyBtYXRf YXJyCiAgICAgICAgLGFycmF5X2FnZyhjdXJyX21hdF9jb2RlIG9yZGVyIGJ5IGlkX291KSBh cyBtYXRjb2RlX2FycgogICAgICAgICxiaXRfb3IoaW1hc2spIGFzIGliaXRtYXNrCiAgICBm cm9tKAogICAgICAgIHNlbGVjdAogICAgICAgICAgICAgc291X3NlYXNvbiBhcyBjdXJyX3Nl YXNvbgogICAgICAgICAgICAsc291X3NhX2NvZGUgYXMgY3Vycl9jb2RlCiAgICAgICAgICAg ICxzb3VfbWF0X2NvZGUgYXMgY3Vycl9tYXRfY29kZQogICAgICAgICAgICAsc291X2Nscl9j b2RlIGFzIGN1cnJfY2xyX2NvZGUKICAgICAgICAgICAgLGlkX291CiAgICAgICAgICAgICxj b2FsZXNjZShpZF9vdSwtMSkgYXMgaW1hc2sKICAgICAgICBmcm9tIG9ubHkgc2Ffb3V0c29s ZQogICAgICAgIGxlZnQgam9pbiBvdXRzb2xlX3RhcmdldCBvbiBvdV9tYXRfY29kZT1zb3Vf bWF0X2NvZGUgYW5kIGlkX291IDw9ICgzMi0xLTIpCiAgICApIHFyCiAgICBncm91cCBieSAx LDIKKQoscXVwZCBhcyAoCiAgICBzZWxlY3QgKiBmcm9tICgKICAgICAgICBzZWxlY3QKICAg ICAgICAgICAgIHF1cC5jdXJyX3NlYXNvbgogICAgICAgICAgICAscXVwLmN1cnJfY29kZQog ICAgICAgICAgICAscXVwLmliaXRtYXNrfHFpbi5pYml0bWFza3xxbGkuaWJpdG1hc2t8cW91 LmliaXRtYXNrIGFzIGliaXRtYXNrCiAgICAgICAgICAgIC0tIHRoZSBjYWxjdWxhdGlvbnMg b2YgbmV3X21hdF94IGFyZSBzaW1wbGlmaWVkIGhlcmUKICAgICAgICAgICAgLS0gaW4gdGhl IHByb2R1Y3Rpb24gdmVyc2lvbiB0aGV5IGFyZSBhIG1vcmUgY29tcGxleCBjb21iaW5hdGlv biBvZiBiaXQgbWFza3MsIGJpdCBzaGlmdHMgYW5kIGJpdCBvciBvZiBkaWZmZXJlbnQgZWxl bWVudHMgb2YgdGhlIGFycmF5cwogICAgICAgICAgICAsKHF1cC5tYXRfYXJyWzFdfHFsaS5t YXRfYXJyWzFdfHFpbi5tYXRfYXJyWzFdfHFvdS5tYXRfYXJyWzFdKTo6YmlnaW50IGFzIG5l d19tYXRfMQogICAgICAgICAgICAKICAgICAgICAgICAgLChxdXAubWF0X2FyclsyXXxxbGku bWF0X2FyclsyXXxxaW4ubWF0X2FyclsyXXxxb3UubWF0X2FyclsyXSk6OmJpZ2ludCBhcyBu ZXdfbWF0XzIKICAgICAgICAgICAgCiAgICAgICAgICAgICwocXVwLm1hdF9hcnJbM118cWxp Lm1hdF9hcnJbM118cWluLm1hdF9hcnJbM118cW91Lm1hdF9hcnJbM10pOjpiaWdpbnQgYXMg bmV3X21hdF8zCiAgICAgICAgCiAgICAgICAgZnJvbSBxdXAKICAgICAgICBsZWZ0IGpvaW4g cWxpIG9uIChxbGkuY3Vycl9zZWFzb249cXVwLmN1cnJfc2Vhc29uIGFuZCBxbGkuY3Vycl9j b2RlPXF1cC5jdXJyX2NvZGUpCiAgICAgICAgbGVmdCBqb2luIHFpbiBvbiAocWluLmN1cnJf c2Vhc29uPXF1cC5jdXJyX3NlYXNvbiBhbmQgcWluLmN1cnJfY29kZT1xdXAuY3Vycl9jb2Rl KQogICAgICAgIGxlZnQgam9pbiBxb3Ugb24gKHFvdS5jdXJyX3NlYXNvbj1xdXAuY3Vycl9z ZWFzb24gYW5kIHFvdS5jdXJyX2NvZGU9cXVwLmN1cnJfY29kZSkKICAgICAgICB3aGVyZSBx dXAuaWJpdG1hc2s+MCBhbmQgY2FyZGluYWxpdHkocXVwLm1hdF9hcnIpIDw9MjEKICAgIClx agogICAgd2hlcmUgaWJpdG1hc2sgaXMgbm90IG51bGwKKQpzZWxlY3QKIGN1cnJfc2Vhc29u CixjdXJyX2NvZGUKLG5ld19tYXRfMQosbmV3X21hdF8yCixuZXdfbWF0XzMKLE5VTEw6OmJp Z2ludCBhcyBuZXdfbWF0dHlwZV9iaXRtYXNrCixOVUxMIGFzIG5ld19tYXRfY29kZXMKZnJv bSBxdXBkOwo= --------------eBhWuHJXSSWhaPd4aABP0037 Content-Type: application/sql; name="create-join.sql" Content-Disposition: attachment; filename="create-join.sql" Content-Transfer-Encoding: base64 Y3JlYXRlIHRhYmxlIHNhX3VwcGVyICgKIGlkX3N1cCAgICAgICBpbnRlZ2VyICAgICAgICAg ICAgICAgcHJpbWFyeSBrZXksCiBzdXBfc2Vhc29uICAgc21hbGxpbnQgICAgICAgICAgICAg ICwKIHN1cF9zYV9jb2RlICBjaGFyYWN0ZXIgdmFyeWluZygxMCkgLAogc3VwX21hdF9jb2Rl IGNoYXJhY3RlciB2YXJ5aW5nKDQpICAsCiBzdXBfY2xyX2NvZGUgY2hhcmFjdGVyIHZhcnlp bmcoMykpOwoKY3JlYXRlIHRhYmxlIHNhX2xpbmluZyAoCiBpZF9zbGkgICAgICAgaW50ZWdl ciAgICAgICAgICAgICAgIHByaW1hcnkga2V5LAogc2xpX3NlYXNvbiAgIHNtYWxsaW50ICAg ICAgICAgICAgICAsCiBzbGlfc2FfY29kZSAgY2hhcmFjdGVyIHZhcnlpbmcoMTApICwKIHNs aV9tYXRfY29kZSBjaGFyYWN0ZXIgdmFyeWluZyg0KSAgLAogc2xpX2Nscl9jb2RlIGNoYXJh Y3RlciB2YXJ5aW5nKDMpKTsKCmNyZWF0ZSB0YWJsZSBzYV9pbnNvbGUgKAogaWRfc2luICAg ICAgIGludGVnZXIgICAgICAgICAgICAgICBwcmltYXJ5IGtleSwKIHNpbl9zZWFzb24gICBz bWFsbGludCAgICAgICAgICAgICAgLAogc2luX3NhX2NvZGUgIGNoYXJhY3RlciB2YXJ5aW5n KDEwKSAsCiBzaW5fbWF0X2NvZGUgY2hhcmFjdGVyIHZhcnlpbmcoNCkgICwKIHNpbl9jbHJf Y29kZSBjaGFyYWN0ZXIgdmFyeWluZygzKSk7CgpjcmVhdGUgdGFibGUgcHVibGljLnNhX291 dHNvbGUgKAogaWRfc291ICAgICAgIGludGVnZXIgICAgICAgICAgICAgICBwcmltYXJ5IGtl eSwKIHNvdV9zZWFzb24gICBzbWFsbGludCAgICAgICAgICAgICAgLAogc291X3NhX2NvZGUg IGNoYXJhY3RlciB2YXJ5aW5nKDEwKSAsCiBzb3VfbWF0X2NvZGUgY2hhcmFjdGVyIHZhcnlp bmcoNCkgICwKIHNvdV9jbHJfY29kZSBjaGFyYWN0ZXIgdmFyeWluZygzKSk7CiAKY3JlYXRl IHRhYmxlIHVwcGVyX3RhcmdldCAoCiBpZF91cCAgICAgICBzbWFsbGludCwKIG51cCAgICAg ICAgIGludGVnZXIgLAogdXBfbWF0X2NvZGUgdGV4dCk7CgpjcmVhdGUgdGFibGUgbGluaW5n X3RhcmdldCAoCiBpZF9saSAgICAgICBzbWFsbGludCwKIG51cCAgICAgICAgIGludGVnZXIg LAogbGlfbWF0X2NvZGUgdGV4dCk7CgpjcmVhdGUgdGFibGUgb3V0c29sZV90YXJnZXQgKAog aWRfb3UgICAgICAgc21hbGxpbnQsCiBudXAgICAgICAgICBpbnRlZ2VyICwKIG91X21hdF9j b2RlIHRleHQpOwoKY3JlYXRlIHRhYmxlIGluc29sZV90YXJnZXQgKAogaWRfaW4gICAgICAg c21hbGxpbnQsCiBudXAgICAgICAgICBpbnRlZ2VyICwKIGluX21hdF9jb2RlIHRleHQpOwo= --------------eBhWuHJXSSWhaPd4aABP0037--