Обсуждение: a very slow SQL

Поиск
Список
Период
Сортировка

a very slow SQL

От
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)

---------------------------------------------------------------------------------------------------------------------------------------------------



Re: a very slow SQL

От
"Kevin Grittner"
Дата:
wangqi  wrote:

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

Without knowing more about the version of PostgreSQL, your PostgreSQL
configuration, your schema (including indexes), and your hardware,
it's hard to give advice.

http://wiki.postgresql.org/wiki/SlowQueryQuestions

A wild guess on the evidence we have is that you might benefit from
an index on MCL.ctid if you don't already have one.

-Kevin

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