Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id A78F99F999D for ; Thu, 27 Sep 2007 08:54:21 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 11667-02 for ; Thu, 27 Sep 2007 08:54:16 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mail3.ecircle.de (mail3.ecircle.de [195.140.186.206]) by postgresql.org (Postfix) with ESMTP id 65A3D9F999E for ; Thu, 27 Sep 2007 08:54:16 -0300 (ADT) Received: from einstein.muc.ecircle.de (einstein.muc.ecircle.de [192.168.1.7]) by mail3.ecircle.de (READY) with ESMTP id E23CB11C0DA; Thu, 27 Sep 2007 13:54:14 +0200 (CEST) Received: from [192.168.1.91] ([192.168.1.91]) by einstein.muc.ecircle.de with Microsoft SMTPSVC(6.0.3790.1830); Thu, 27 Sep 2007 13:53:16 +0200 Subject: Re: Searching for the cause of a bad plan From: Csaba Nagy To: postgres performance list Cc: Simon Riggs In-Reply-To: <21582.1190820137@sss.pgh.pa.us> References: <1190369024.4661.173.camel@PCD12478> <6175.1190390913@sss.pgh.pa.us> <1190396352.4202.109.camel@ebony.site> <13644.1190397220@sss.pgh.pa.us> <1190399879.4202.127.camel@ebony.site> <27857.1190417434@sss.pgh.pa.us> <1190640429.4181.165.camel@ebony.site> <1190818357.5430.29.camel@PCD12478> <21582.1190820137@sss.pgh.pa.us> Content-Type: multipart/mixed; boundary="=-Bc17SXw3J9L72b2bJPxA" Date: Thu, 27 Sep 2007 13:53:24 +0200 Message-Id: <1190894004.5430.46.camel@PCD12478> Mime-Version: 1.0 X-Mailer: Evolution 2.10.1 X-OriginalArrivalTime: 27 Sep 2007 11:53:16.0526 (UTC) FILETIME=[FD8660E0:01C800FC] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/454 X-Sequence-Number: 26999 --=-Bc17SXw3J9L72b2bJPxA Content-Type: text/plain Content-Transfer-Encoding: 7bit On Wed, 2007-09-26 at 11:22 -0400, Tom Lane wrote: > ... how > many values of "a" are there really, and what's the true distribution of > counts? table_a has 23366 distinct values. Some statistics (using R): > summary(table_a_histogram) a count Min. : 70000857 Min. : 1 1st Qu.:700003628 1st Qu.: 9 Median :700011044 Median : 22 Mean :622429573 Mean : 17640 3rd Qu.:700018020 3rd Qu.: 391 Max. :800003349 Max. :3347707 I'm not sure what you want to see in terms of distribution of counts, so I created 2 plots: "a" against the counts for each distinct "a" value, and the histogram of the log of the counts (without the log it's not really readable). I hope they'll make it through to the list... > Do the plan estimates get closer to reality if you set a higher > statistics target? The results of setting higher statistics targets are attached too. I can't tell if the stats are closer to reality or not, but the plan changes in any case... Cheers, Csaba. --=-Bc17SXw3J9L72b2bJPxA Content-Disposition: attachment; filename=table_a_counts.png Content-Type: image/png; name=table_a_counts.png Content-Transfer-Encoding: base64 iVBORw0KGgoAAAANSUhEUgAAAeAAAAHfCAMAAAC/JvvfAAAABlBMVEUAAAD+/v7KB/Q9AAAAAnRS TlP/AOW3MEoAAAlJSURBVHic7d3teqI6FEDheP83fZ7j2GqRjwR2wmax3h89nTOYWtcQAlpbHkIr Z98B9WVgOAPDGRjOwHAGhjMwnIHhDAxnYDgDwxkYzsBwBoYzMJyB4QwMZ2A4A8MZGM7AcAaGMzCc geEMDGdgOAPDGRjOwHAGhjMwnIHhDAxnYDgDwxkYzsBwBoYzMJyB4QwMZ2A4A8MZGM7AcAaGMzCc geEMDGdgOAPDGRjOwHAGhjMwnIHhDAxnYDgDwxkYzsBwBoYzMJyB4QwMZ2A4A8MZGM7AcAaGMzCc geHCAheNNjhw1ECqZGA4A8MZGM7AcAaGMzCcgeEMnF/t1Yr5GwduNXKgGymPI4+bgbMrHx933zxm q5ED3YeB4crzGGxgLo/BbO7BcB6D4QxM5zGYzitZWmbg/NyD2epfGzl768CtRg50Hy2vfp27eeBW Iwe6DwPDPdsamMvAcE7RcAam8zSJzgsdbD7ZwObThXC+ogPOVTScFzrgnKLhXGTBOUXTeSWLzitZ WjYocNv7rimOezCcgeEMDGdgOAPDGRjOwHAGhjMwnIHhDAxnYDgDwxkYzsBwBoYLD7z+1L6BR4sO XGY+2zWQYhgYzsBwBs4v18tmXWRF8wfA2fzZJLhsgZ2igyUL7CIrXK5jsIHjpVpFGzicezBbsmOw i6xovkcHnG+jBJctsFN0sGSBXWRFS/YuOwaO5h4MZ2C4ZIFdZEXLFnjQQPfhhQ64bHuwU3S0XO9V 6SIrWrIpeiGwb2W4W7Jnk9yDoxmYLtcT/i6y4qV6yc6oge7EwGxO0WwusuAMDGdgulzHYAPHy7WK dpGVi6dJcAaG63IMdorOo0fglYW9gUczMJyB4QwMFx74tb5ykZWEp0lwBobrcaly5TYGHs1FFlyf K1mLtzHwaH2eLlx8CtPAo3V6PrgYOIkOi6zXfwycgqdJcAaGMzCcgeEMDGdgOAPDGRjOwHAGhjMw nIHhDAxnYDgD55fr54MHDXQjiy+Pqb511FZrA/helXutvISx/uYxW40c6D4MDGdgukscg8MHuhNX 0VpmYDgDwxkYzsBwBobbF7jzabfiNAYukz/3+XKK0xT4+FMGBh5t3x7c+cspjossuNbAm3O0bwie S+sUvbV9mfms+cspTvQx2MDJGBiu+Rh8bEsDj9Z8DHaRdS2eJsGF78ERX05xdu3Bazdyis4l+tkk F1nJGBhu3xRt4MvYt8iqGc/AKYSfJrnIysXzYLh9Txd2/nKKs+vpQi9VXkf0i+5cZCVjYLjoKdrA yUQvsgycjOfBcHte0eFp0oWEnyZFfDn9MfItHDZX0U7R4crjyOPmaVJ25ePj7ptXb+Vp0nBjA+89 TfKtDHcbHLh+S/fgIEOPwRWbusiK5huhaZmB4ZyiaSYPvYssmOmSzMD5tSyyvk6qDJxe02mSgS+n 7UJH98AusqI1XsnqfQweNdB9tF6q7LyKHjXQjeS6VPl6tskpOlCqS5Xvf24uslIwMJyB4QwMFx74 tb5ykRUn1SJr1EA3kus0adRA95HsNVmjBroPA8MlC1zK6rMNBm6X7Bi8vqmBd8i1ijZwLh6D4QwM Z2A4A8MZGM7AcAbOL9d58KCBbiTZlaxBA91HsmvRowa6DwPDGZjOYzCdq2gtMzCcgeEMDDcosG9l eICLLDZPk9i80AFnYDgD0434bZEGPpGB2dZ+3Lrm5oFbjRzoPsrzPNjAWO7BcAcvARo4OwPDeQyG cw+GMzCcgeEMDOd5MJx7MNyFA/sSrSrJnk2q/60rx15rdBvJ9uAy89n8QAdfqXAbyRZZBo6W7FKl gaNddor2GFwnWeCWX23nKrpGtsCtA1l5Q7JjcOtAztNbsu3BDVP0w5VWhWSBGxZZ7z8beIWB4Vof osk/hpMDewze1Hgla7q7nx3YVfSWtin665/DyYssbWs6BH9N6GefJmlbyyT3ddZs4PTa9uDp9Hn6 FO0xeEPrMbhzYFfR0RoD956iPQ+O1hr4/XH66ebN2rY0cIxrT9EHnyq5g8ZLlb0vdLQusjwGb0m2 BzcO5B68KdkxeGmAhf3aY/CmbIHbpmgDb0p2Ldrz4GjJFlk+mxStMXC254O1pTXw9OaBW/3d0sAx ku3BPtkQLdkxuHWgQ9PPLSRbRTcOdPAAcwcXPg8u69vqqTXw5NLgiYusf5OJgTckm6LrAxf34Cqt gSdbnxjYPbjKZafo56cG3pRsiq5fZLkH10k2RdcP5DG4zmUDu4quk2yKfh9ZtwdyD66RMPDX1ZSF gQxcI+EqujKwU3SVZMfg+sCeJtUxMFy2wK/hK86DZ++PppItshoG8hhcJdke3DKQq+ga1w1cDFzj soG9Fl3nuoFn74+mGgPneY8OA9dpfIje56nvP9fdKoRTdLPL7sEusupc9zzY06Qq7sFw111FG7iK geEMDGdgOAPDGRjOwHDZAq9HM3CzZIHLzGfzAxm4joHhrhF4JqWB61wj8Mz/NHCdZIEb3qPDvnXa HqLp1ic/Xfj6oDVtD9Fk6zMDawADwxkYzsBwBoYzMJyB4QwMNzqwRhsbOFDf+3Th0fcMbuALjW5g +OgGho9uYPjoBoaPbmD46AaGj25g+OiUwApkYDgDwxkYzsBwBoYzMJyB4QwMZ2A4A8MZGM7AcOcH XnwB6M9fVL9CdOkLdBu8712PGf30wGXpXvz8xeIGtV+g2+B973rQ6PjApXOCxb8w8If/78V7winv j4cDl46D/96u3+jH73uKwP++jd/ZNPhRKv0GL6XvXQ+47xkCl78fJt/HkZXKzGMRN/jr12T0Gv3n xgdHTxD4dbdfP0/1+4NVEbvBey/oMPg1Rj8/8J/jy8enIYFfD0qXwf8OknX00wOXyX8foY/S5xjh gxu4xm/g8uiwUvkZo8vg70k08ejnB/75WeavJUPklaw+gy8Okmj00wOrLwPDGRjOwHAGhjMwnIHh DAxnYDgDwxkYzsBwBoYzMJyB4QwMZ2A4A8MZGM7AcAaGMzCcgeEMDGdgOAPDGRjOwHB3D3z0J9vS Y393mz5+NhMK/c1VQj8G6G+uQv1vWr4o9De37f0+NlTob27b4feHSA/9zW1zkUX3fJMl8oNA/t70 MDCegeEMDGdgOAPDGRjOwHAGhjMwnIHhDAxnYDgDwxkYzsBwBoYzMJyB4QwMZ2A4A8P9B3PXcakC tnOTAAAAAElFTkSuQmCC --=-Bc17SXw3J9L72b2bJPxA Content-Disposition: attachment; filename=table_a_counts_histogram.png Content-Type: image/png; name=table_a_counts_histogram.png Content-Transfer-Encoding: base64 iVBORw0KGgoAAAANSUhEUgAAAeAAAAHfCAMAAAC/JvvfAAAABlBMVEUAAAD+/v7KB/Q9AAAAAnRS TlP/AOW3MEoAAAukSURBVHic7ZyLlqOqFgDx/3/6njvdRiT4QEGwumrNpI3K3kgpQl5hEjShdwWk LQqGo2A4CoajYDgKhqNgOAqGo2A4CoajYDgKhqNgOAqGo2A4CoajYDgKhqNgOAqGo2A4CoajYDgK hqNgOAqGo2A4Coaj4Od5tM2bJQth+RtaZPkE3Y6e2bKsOl2pC0X2w4XiODcSdxBcR3c4Fvyzy1by 24KvHUcIhYZ/GvBymz0iOL+lTvxdwd+bbwk+t/4wWigqmjlLi/JdLXgYOL2C5xN3Pn+X8/j/C7+7 /Z6u80GFn00hDpuWnnbCLevXQXPF4gzpObEUvn0c/wSvjyUKFbXYOs1lww0Fz0zL4c4Ln+3RnnOL rJ8mPdrnWSo4G2767JgG/S6W1DtzJHWOYwm+OpbIYlzmVYKn9UPIPyz7xBuWoN+31q1In8tjO2h8 4UTR0jVL4QrHEZ0P80W/rnEmxJiCl7/pmTutj2W99PPsuymnVakvwZvh0rMjrVR6Cc97JkdS7zjS a3I7xKsET1FXNu02THzdVxO8Cror+GtNEvH+ccQ12Q3xLsHTRsfzvRSip5UEZzrl9bUW1Ttd/31p 3jyOsM4NEZwc5vZN87NPVvC875fgrXBJmPQe/Im3qveO4LvHEcJH8O/D9z36pYKXseLv4zLaWHef 2130qtQq02a4T/uFkFZjVWydYU/wveNYlc8ufAn+OuWKPFwsdxw4ETwt7bY0wLzLqtOaz+qc4CkJ ciLc0ky/G1ZnSOo3uqjS+JWOIy6fXUgEZ/qUEpoJLuTyAeyEqxzzfOKjfdqErZasMnPPWT9cvaCF idvEvli0Zj0uVuHrPlgr3Pmo890v7KwpSFybG2EHEJy5D44U7gWJ9xivRlIVBcNRMBwFw1EwHAXD UTAcBcNRMBwFw1EwHAXDUTAcBcNRMBwFw1EwHAXDUTAcBcNRMBwFw1EwHAXDUTAcBcNRMBwFw1Ew HAXDUTAcBcMpEFz+nXfpT8EvFFwoI91RMBwFw1EwHAdZcLQFp7/gkKV3rTD076Kz4RRci/6DLAU3 RcFwFAxHwXAcZMHp35IKbsrtlrx9XSu4KdW6aAWPSbVBloLHRMFwFAxHwXAcZMGp1pIKHhMFwynp oqdpsot+G2WDrLBdRsFjomA4CoZTOA9eHq4HOlNQwbVwHgzHaRIcBcNRMBwFw1EwHAXDUTAcBcNR MBwFw1EwHAXDUTAcBcNRMBwFw1EwHD/RAafwM1nbZRQ8JgqGo2A4CobjIAuO0yQ4CoZjFw3HQRYc BcNRMBwFw3GQBcdpEpzbLenPCY+NXTQcB1lwFAxHwXAUDMdBFhznwXAUDKfwHmwX/TbKBIftMgoe EwXDUTAcBcMpEPw7vnKQ9SqcJsFRMBwFw1EwHAXDUTAcBcNRMBwFw1EwHAXDUTAcBcNRMBwFw1Ew HAXD8ZsNcAo/F71dRsFjomA4CoajYDgOsuA4TYIzquA816v3Z3lVF63gcl41yFJwOQqGo2A4Cobj IAvOqNOkujn+MLfb7MmfE1ZwOXbRcBxkwVEwHAXDUTAcB1lwnAfDKW6zrQIKHpOCe/D+++4KHpNP mx2/FBXWBbYCXa7BcTQFlxO12eHLjSEpsBXocg2Ooim4nHWbHSgOXwW2Al2uwW40BZeTtNnBuwZB wW9j1WZ7Pxc876HgdxG9PjW7u9aMCh6TglH0yUBVCiq4FutXmG9YVvCYJFdw5Q9mXC6o4FqEZEHB MBQMxy4azmqadOf7e2dKnv/KoIJr8ej7weddKrgWCoaTdtFN78EKfp5HX8lS8POk06T7gUr3UXBT FAwnZJZuBircR8FNie7BDrKIOE2CU9Bm97/ZoODnST7RsdOEB3drBY9JPA8Ou7NhBb+SeJoUpr02 VPArUTCc8120g6xX8uz7wfdWKvgCzoPhFLySZRf9RsLu0/wmB1kvIm0zR9EwFAwn7aIVDCMdZO3t 6iDrhThNgnO7zUp+TljBz/M1D975Dr9d9AtJhk7Og2mEZMFRNAwFw4nfLvw87u+p4DdR8Hahg6w3 4jwYjoLhrLro/Sb81zvbRb+MeJAVjgdZ25NlBY9JPE0K014bKviVKBhOYRedLGU3n0h2baWCL1A0 D47/bAc6k+zSSgVfwGkSnPS16PuBSvdRcFMUDOdg6HQlUOE+Cm5KNIr2NzqIOMiCE6LHCoEu7KPg pkSC7zWfgsdEwXAUDEfBcBQMZ34DoeQbKDuBLuyj4KY4D4ajYDgKhqNgOAVt5jcb3sj5Njt4Y1HB Y6JgOAqGo2A4DrLgOE2Co2A4dtFwHGTBUTAcBcNRMBwHWXCcJsG53Wb+nPDY2EXDcZAFR8FwFAxH wXAcZMFxHgxHwXDKuuidMgoek7JBVtguo+AxKRxFLw9XAin4eUqnSct1fCGQgp+neB4cFPwqigZZ v38U/CKcJsFRMBwFw1EwHAXDUTAcBcNRMBwFw2kkOOQ5XQMF16KV4PPJSgSfPmtk5l2Cz+8qvygY joLhKBiOguEoGE7RJzrOf7NBwaNwvnlCZmkzkIJHQcFwFAxHwXAcZMFxmgRHwXDsouE4yMqDeetZ wXkwnYWC8yh4PxBU8Av7bQdZee5WdRicJuVR8CdA9rpW8CjYRef5g4IdZCn4KKyCn0fBeRS8H0jB o+AgK89fFFwSSMGjoOA8Ct4PpOBRUHD+HYQ/KLjo4N8k+GYqjOCDXccSfPeyVPDB1u6C8+XPv5/7 FwWXBHpScAtrCj4INOgV3CRVwRnWAQU/mKoHClbwlUC9BSl4RsEKvhKotyAFzyhYwVcC9RbUX/Ao cycFd0/VFgV3T9UWBXdP1RYFd0/VFgV3T9UWBXdP1RYFd0/VFgV3T9UWBXdP1RYFd0/VFgV3T9UW BXdP1RYFd0/VFgV3T9UWBXdP1RYFd0/VFgV3T9UWBXdP1RYFd0/VFgU/marDJ7UUPGaqahREL/nm Xu9WU3B59JBZ2gzUu9UUXB5dwa1SNb0x3xacr9HGcELu0UOwjE21QZaMibbgKBiOguEoGI6C4SgY joLhKBiOguE0Etzx9VoI1UzUCvRIWFP1C/RIWFP1C/RIWFP1C/RIWFP1C/RIWFP1C/RIWFP1C/RI WFP1C/RIWFP1C/RIWFP1CyRjomA4CoajYDgKhqNgOAqGo2A4CoajYDgKhqNgOAqG00RwzY99Hmd6 Jlf45HsmVa0ja1Hd0CrwRqon8oTloBrnjFPVCFcpzldMluD4oJrnVHCS6rlMCl5iPiT4sdv944Ir HdnLBS/5HknyoOBkqUKweii4QqpkqUKweii4QqpkqUKwejw9yEIKrpVKwcWZFPzgK1nPpXr6laxa qXwtGo6C4SgYjoLhKBiOguEoGI6C4SgYjoLhKBiOguEoGI6C4SgYjoLhKBiOguEoGI6C4SgYjoLh KBiOguEouJxXtdk7KputZcg+PXFAIflbWJfTXzkYom2HqMQhuVqGZP0dwQWtEOZ/J3KM0Lgj1OGY NoL3428XVnB1/smcf37mvz/hs2rZEH6exrt+iq+fh7ngHGIVZ0nwu2kOPk1hZTck6X92iyOO0LoD VOEEc+PG/3/XpxuiZ5/SG8/n0yEbZwrZlT8SoyhJfVZPR2jdAapwgkho+jfd4WvTssdqMXxOmynq 7tMEcfDVRb++JazSR6UHaN0BqnCCXcG/w9pIcDrQTZ7HIaLOOpcgDr7qiBVclT3Bs6HkCo5LRz7m UrHCHcFx8GQnBVdkV/B6RU7w52H9fFG0LTjZYY6/YVTBFwlT1UHWHPIjLhNnuYLXXfQi+DffV3oF l/Mj4GuaNDdr+GnavWlSbHh1WYZIZDZBmL3NJ0O033e9VufdCK07QBUu0LzxdoOfzjxC445QhyKW KWrrBNt7nA51vza3GaEOZXz636Oqh7CeL6XPDxPcZYi2HaIS0g4Fw1EwHAXDUTAcBcNRMBwFw1Ew HAXDUTAcBcNRMBwFw1EwHAXDUTAcBcNRMJz/Aa71a3pyVUcVAAAAAElFTkSuQmCC --=-Bc17SXw3J9L72b2bJPxA Content-Disposition: attachment; filename=bumped_stats.txt Content-Type: text/plain; name=bumped_stats.txt; charset=UTF-8 Content-Transfer-Encoding: quoted-printable db=3D# alter table temp_table_a ALTER a set statistics 100; db=3D# analyze verbose temp_table_a; INFO: analyzing "public.temp_table_a" INFO: "temp_table_a": scanned 30000 of 655299 pages, containing 18870000 l= ive rows and 0 dead rows; 30000 rows in sample, 412183071 estimated total r= ows db=3D# select * from pg_stats where tablename =3D 'temp_table_a'; schemaname | tablename | attname | null_frac | avg_width | n_distinct = | = = = = = = most_common_vals = = = = = = = | = = = = = = most_common_freqs = = = = = = = | = = = = = = histogram= _bounds = = = = = = | correlation=20 ------------+--------------+---------+-----------+-----------+-------------= +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -----------------------+---------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -----------------------+---------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------------------------------------+------------- public | temp_table_a | a | 0 | 8 | 3320 = | {700009411,700002317,700004630,700002843,700006505,700023682,700022128,70= 0003583,700017957,700006025,700015988,700023482,700004117,700007205,7000121= 36,700010476,700014833,700017217,700008694,700014251,700011345,700002683,70= 0013155,800002849,700019049,700000762,700019048,700022028,700001252,7000217= 93,700019097,700013291,700013588,700009119,700018429,700000158,700016990,70= 0017956,700023992,70011667,700019493,700021411,700017871,700020154,70000546= 6,700010475,700018637,700000897,700014785,700023244,800000335,80016184,7000= 00761,700006691,700008961,700015131,700022290,700013232,700014831,700016187= ,700021053,700002541,700018414,700019738,700020192,700007416,700008945,7000= 15991,700012285,700013309,700006364,700011228,700020174,700004361,700005736= ,700014249,700017890,700020669,70011584,700002454,700010069,700013082,70001= 6098,80017430,80018012,700001286,700001795,700002325,700002721,700018164,70= 0022384,80013130,700001517,800000032,700003137,700008230,700010374,70001129= 9,700014197,700016434} | {0.00903333,0.00886667,0.00826667,0.008,0.00756667= ,0.00753333,0.00743333,0.00726667,0.00713333,0.00706667,0.007,0.00693333,0.= 00673333,0.00656667,0.00636667,0.0062,0.0062,0.00616667,0.00613333,0.006,0.= 00563333,0.0055,0.00463333,0.00423333,0.00396667,0.00373333,0.00343333,0.00= 343333,0.0031,0.00256667,0.0025,0.00236667,0.00233333,0.00213333,0.00213333= ,0.0021,0.00206667,0.00203333,0.00203333,0.002,0.002,0.00196667,0.00193333,= 0.00193333,0.00183333,0.00183333,0.00183333,0.0018,0.0018,0.0018,0.00176667= ,0.00173333,0.00173333,0.00173333,0.0017,0.0017,0.0017,0.00166667,0.0016666= 7,0.00163333,0.0016,0.00156667,0.00156667,0.00156667,0.00156667,0.00153333,= 0.00153333,0.00153333,0.0015,0.0015,0.00146667,0.00146667,0.00146667,0.0014= 3333,0.00143333,0.00143333,0.00143333,0.00143333,0.0014,0.0014,0.0014,0.001= 4,0.0014,0.00136667,0.00136667,0.00136667,0.00136667,0.00136667,0.00136667,= 0.00136667,0.00136667,0.00133333,0.00133333,0.00133333,0.0013,0.0013,0.0013= ,0.0013,0.0013,0.0013} | {70010184,80010631,80011534,80013683,80014332,8001= 5011,80016113,80017127,80017990,80018206,80018579,80018815,700000082,700000= 556,700000949,700001464,700001797,700002009,700002342,700002536,700002895,7= 00002996,700003288,700003570,700003934,700004172,700004478,700004748,700005= 072,700005370,700005493,700005791,700006004,700006263,700006598,700006822,7= 00007062,700007409,700007572,700007792,700008233,700008565,700008795,700009= 047,700009276,700009539,700009717,700009877,700010029,700010227,700010503,7= 00010931,700011122,700011406,700011652,700011851,700012122,700012287,700012= 598,700012856,700013144,700013308,700013475,700013790,700014103,700014357,7= 00014443,700014756,700015046,700015377,700015671,700016037,700016345,700016= 745,700017024,700017221,700017575,700017876,700018283,700018676,700019090,7= 00019409,700019877,700020116,700020513,700020803,700021114,700021388,700021= 797,700022133,700022374,700022577,700022921,700023447,700023681,700024112,7= 00024460,800000171,800000572,800001802,800003180} | 0.862633 public | temp_table_a | b | 0 | 8 | 1.86656e+07 = | {700773174,701001618,701208693,7004728327,7006203959,7006357115,700797498= 5,7009682696,7012068090,7013911212} = = = = = = = = = = = = | {6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6= .66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05} = = = = = = = = = = = = | {41707835,700698980,803974484,7004815752,700756618= 6,7010477490,7013904387,7016651409,7020650387,7026239954,8001229552} = = = = = = = = = = = = | 0.657061 db=3D# prepare test_003(bigint) as db-# SELECT tb.* db-# FROM temp_table_a ta=20 db-# JOIN temp_table_b2 tb ON ta.b=3Dtb.b db-# WHERE ta.a =3D $1 =20 db-# ORDER BY ta.a, ta.b db-# limit 10; db=3D# explain analyze execute test_003(31855344); QUER= Y PLAN = =20 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -- Limit (cost=3D0.00..22427.86 rows=3D10 width=3D24) (actual time=3D0.027..= 0.027 rows=3D0 loops=3D1) -> Nested Loop (cost=3D0.00..625737.32 rows=3D279 width=3D24) (actual = time=3D0.024..0.024 rows=3D0 loops=3D1) -> Index Scan using temp_pk_table_a on temp_table_a ta (cost=3D0= .00..133756.10 rows=3D124152 width=3D16) (actual time=3D0.023..0.023 rows= =3D0 loops=3D1) Index Cond: (a =3D $1) -> Index Scan using temp_pk_table_b2 on temp_table_b2 tb (cost= =3D0.00..3.95 rows=3D1 width=3D8) (never executed) Index Cond: ("outer".b =3D tb.b) Total runtime: 0.082 ms --=-Bc17SXw3J9L72b2bJPxA--