Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id D2AF22B2862 for ; Mon, 25 Jun 2012 03:42:57 -0300 (ADT) Received: from [210.83.216.121] (helo=edgesoft.cn) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Sj30g-0002MF-Ek for pgsql-admin@postgresql.org; Mon, 25 Jun 2012 06:42:56 +0000 Received: from [172.18.63.139] (unknown [15.210.133.3]) (Authenticated sender: wangqi@edgesoft.cn) by mail.edgesoft.cn (Postfix) with ESMTPA id 0B4B98111 for ; Mon, 25 Jun 2012 14:42:33 +0800 (CST) Message-ID: <4FE80909.5070803@edgesoft.cn> Date: Mon, 25 Jun 2012 14:45:29 +0800 From: wangqi User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:13.0) Gecko/20120614 Thunderbird/13.0.1 MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: a very slow SQL Content-Type: text/plain; charset=GB2312 Content-Transfer-Encoding: quoted-printable X-Host-Lookup-Failed: Reverse DNS lookup failed for 210.83.216.121 (failed) X-Pg-Spam-Score: 0.8 (/) X-Archive-Number: 201206/111 X-Sequence-Number: 39702 Hi everybody, An SQL execution is very slow. What can I do to makes it faster=A1=A3 count_MCL -------- 3476534 count_MLC -------- 34442313 count_HLCC --------- 245119339 -------------------------------------------------------------------------= -------------------------------------------------------------------------= - eki0601_pg=3D# explain analyze select * FROM MCL,MLC,HCC WHERE MCL.clid =3D MLC.clid AND MLC.lctid =3D HCC.lctid AND MCL.ctid =3D '01010002759' order= by HCC.ccdate desc fetch first 10 row only ; QUERY PLAN -------------------------------------------------------------------------= ----------------------------------------------------------- ------------------------------------------------------------------------- Limit (cost=3D0.00..2119.09 rows=3D10 width=3D6294) (actual time=3D431697.682..433829.812 rows=3D10 loops=3D1) -> Nested Loop (cost=3D0.00..1764617853.95 rows=3D8327231 width=3D6294) (actual time=3D431697.679..433829.803 rows=3D10 loops=3D1) -> Nested Loop (cost=3D0.00..1453759062.80 rows=3D245081856 width=3D4710) (actual time=3D0.097..302838.488 rows=3D13615078 loops=3D1) -> Index Scan Backward using hlcc_20110322_idx on hcc (cost=3D0.00..27082303.79 rows=3D2 45081856 width=3D193) (actual time=3D0.020..18232.493 rows=3D13615078 loo= ps=3D1) -> Index Scan using idx910300732140000 on MLC (cost=3D0.00..5.81 rows=3D1 width=3D4517) (actual time=3D0 .017..0.018 rows=3D1 loops=3D13615078) Index Cond: ((mlc.lctid)::text =3D (hcc.lctid)::text) -> Index Scan using mcl_tk14289_idx1 on MCL (cost=3D0.00..1.26 rows=3D1 width=3D1584) (actual time=3D0.009..0. 009 rows=3D0 loops=3D13615078) Index Cond: ((mcl.clid)::text =3D (mlc.clid)::text) Filter: ((mcl.ctid)::text =3D '01010002759'::text) Total runtime: 433830.691 ms (10 rows) -------------------------------------------------------------------------= -------------------------------------------------------------------------= -