The plan used the way to bypass 'order by' via index scan backward. The query should scan tables through the index and do nl join the other tables while it finds 10 rows that meet the condition
MCL.ctid = '01010002759'. The problem is the rows that meets your condition is very rarely founded so there is too much scan and join needed.
One question.
How many rows are returned without limiting result?
If the number of rows are small then use the index on MCL.ctid first and join the others might helpful.
Otherwise, Unfortunately there is no way to get the result within a second within this schema.
ps. I'm not an english speaking person. If you can't understand my english, I'm really sorry. :-|
2012년 6월 25일 월요일에 wangqi님이 작성:
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)
---------------------------------------------------------------------------------------------------------------------------------------------------
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin