pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feeda very slow SQL
3+ messages / 3 participants
[nested] [flat]
* a very slow SQL
@ 2012-06-25 06:45 wangqi <wangqi@edgesoft.cn>
2012-06-26 02:23 ` Re: a very slow SQL ±èÁØö <dboxlist@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: wangqi @ 2012-06-25 06:45 UTC (permalink / raw)
To: 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)
---------------------------------------------------------------------------------------------------------------------------------------------------
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: a very slow SQL
2012-06-25 06:45 a very slow SQL wangqi <wangqi@edgesoft.cn>
@ 2012-06-26 02:23 ` ±èÁØö <dboxlist@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: ±èÁØö @ 2012-06-26 02:23 UTC (permalink / raw)
To: wangqi <wangqi@edgesoft.cn>; +Cc: pgsql-admin
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<javascript:;>
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: a very slow SQL
@ 2012-06-25 10:56 Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 3+ messages in thread
From: Kevin Grittner @ 2012-06-25 10:56 UTC (permalink / raw)
To: wangqi@edgesoft.cn; pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-06-26 02:23 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-06-25 06:45 a very slow SQL wangqi <wangqi@edgesoft.cn>
2012-06-26 02:23 ` ±èÁØö <dboxlist@gmail.com>
2012-06-25 10:56 Re: a very slow SQL Kevin Grittner <Kevin.Grittner@wicourts.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox