a very slow SQL

From: wangqi <wangqi(at)edgesoft(dot)cn>
To: pgsql-admin(at)postgresql(dot)org
Subject: a very slow SQL
Date: 2012-06-25 06:45:29
Message-ID: 4FE80909.5070803@edgesoft.cn
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi everybody,
An SQL execution is very slow.
What can I do to makes it faster。

count_MCL
--------
3476534

count_MLC
--------
34442313

count_HLCC
---------
245119339

---------------------------------------------------------------------------------------------------------------------------------------------------
eki0601_pg=# explain analyze select * FROM MCL,MLC,HCC WHERE MCL.clid =
MLC.clid AND MLC.lctid = HCC.lctid AND MCL.ctid = '01010002759' order by
HCC.ccdate desc fetch first 10 row only ;

QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------
Limit (cost=0.00..2119.09 rows=10 width=6294) (actual
time=431697.682..433829.812 rows=10 loops=1)
-> Nested Loop (cost=0.00..1764617853.95 rows=8327231 width=6294)
(actual time=431697.679..433829.803 rows=10 loops=1)
-> Nested Loop (cost=0.00..1453759062.80 rows=245081856 width=4710)
(actual time=0.097..302838.488 rows=13615078 loops=1)
-> Index Scan Backward using hlcc_20110322_idx on hcc
(cost=0.00..27082303.79 rows=2
45081856 width=193) (actual time=0.020..18232.493 rows=13615078 loops=1)
-> Index Scan using idx910300732140000 on MLC (cost=0.00..5.81 rows=1
width=4517) (actual time=0
.017..0.018 rows=1 loops=13615078)
Index Cond: ((mlc.lctid)::text = (hcc.lctid)::text)
-> Index Scan using mcl_tk14289_idx1 on MCL (cost=0.00..1.26 rows=1
width=1584) (actual time=0.009..0.
009 rows=0 loops=13615078)
Index Cond: ((mcl.clid)::text = (mlc.clid)::text)
Filter: ((mcl.ctid)::text = '01010002759'::text)
Total runtime: 433830.691 ms
(10 rows)
---------------------------------------------------------------------------------------------------------------------------------------------------

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Albe Laurenz 2012-06-25 08:03:34 Re: terminating autovacuum process due to administrator command
Previous Message Stuart Bishop 2012-06-24 08:15:14 Re: starting postgres with an empty px_xlog folder