Re: a very slow SQL

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(at)postgresql(dot)org<javascript:;>
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>