Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-admin by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group