MagicHome=# EXPLAIN ANALYZE SELECT gid, s.domain, s.name,
sp.intro, sp.regist_time, r.public, r.publish, p.pid, c.fax_id,
c.category, c.address, c.edited from view_site s join
external.tb_tax_category c using(gid) join view_site_profile sp
using(gid) join view_work_release r using(gid) join view_page p
using(gid) where p.parent is null and gid not in (9198) order by
r.public desc limit 10 offset 0;
QUERY PLAN
-------------------------
Limit (cost=0.00..287.76 rows=10 width=251) (actual time=0.206..1.082 rows=10 loops=1)
-> Nested Loop (cost=0.00..277576.88 rows=9646 width=251) (actual time=0.203..1.068 rows=10 loops=1)
-> Nested Loop (cost=0.00..95023.85 rows=8358 width=259) (actual time=0.154..0.632 rows=10 loops=1)
-> Nested Loop (cost=0.00..58909.89 rows=8678 width=155) (actual time=0.082..0.442 rows=12 loops=1)
-> Nested Loop (cost=0.00..30432.77 rows=8678 width=98) (actual time=0.055..0.257 rows=12 loops=1)
-> Index Scan Backward using idx_tb_work_release_public on tb_work_release (cost=0.00..515.32 rows=8718 width=9) (actual time=0.026..0.056 rows=12 loops=1)
-> Index Scan using tb_site_pkey on tb_site (cost=0.00..3.42 rows=1 width=89) (actual time=0.010..0.011 rows=1 loops=12)
Index Cond: (tb_site.gid = "outer".gid)
Filter: ((able = true) AND (gid <> 9198))
-> Index Scan using tb_site_profile_pkey on tb_site_profile (cost=0.00..3.27 rows=1 width=57) (actual time=0.008..0.009 rows=1 loops=12)
Index Cond: ("outer".gid = tb_site_profile.gid)
-> Index Scan using tb_tax_category_pkey on tb_tax_category c (cost=0.00..4.15 rows=1 width=104) (actual time=0.009..0.010 rows=1 loops=12)
Index Cond: ("outer".gid = c.gid)
-> Index Scan using index_tb_page_gid_pageid on tb_page (cost=0.00..21.78 rows=5 width=8) (actual time=0.016..0.035 rows=1 loops=10)
Index Cond: (tb_page.gid = "outer".gid)
Filter: (parent IS NULL)
Total runtime: 1.305 ms
(17건 있음)
작업시간: 8.438 ms
이런 쿼리가 있습니다..
MagicHome=# CREATE VIEW external.view_site_list as
MagicHome-# SELECT gid, s.domain, s.name, sp.intro,
sp.regist_time, r.public, r.publish, p.pid, c.fax_id, c.category,
c.address, c.edited from view_site s join external.tb_tax_category
c using(gid) join view_site_profile sp using(gid) join
view_work_release r using(gid) join view_page p using(gid) where
p.parent is null order by r.public desc;
저 쿼리를 이용하여 뷰를 만들었습니다. 검색조건인 gid not in
(9198) 부분과 limit 부분만 빼고 뷰를 만들었습니다.
만들어진 뷰에 같은 조건이 되도록 아래와 같이 쿼리하면 플랜도
많이 다르고 시간도 많이 차이납니다..
MagicHome=# EXPLAIN ANALYZE select gid, domain, name,
regist_time, pid, category from external.view_site_list where gid
not in (9198) limit 10 offset 0;
QUERY PLAN
-----------------
Limit (cost=16201.40..16201.53 rows=10 width=258) (actual time=691.545..691.583 rows=10 loops=1)
-> Subquery Scan view_site_list (cost=16201.40..16321.95 rows=9644 width=258) (actual time=691.543..691.575 rows=10 loops=1)
-> Sort (cost=16201.40..16225.51 rows=9644 width=251) (actual time=691.532..691.535 rows=10 loops=1)
Sort Key: tb_work_release.public
-> Merge Join (cost=0.00..15563.19 rows=9644 width=251) (actual time=32.076..678.323 rows=6159 loops=1)
Merge Cond: ("outer".gid = "inner".gid)
-> Merge Join (cost=0.00..27770.58 rows=9287 width=210) (actual time=29.572..618.421 rows=6159 loops=1)
Merge Cond: ("outer".gid = "inner".gid)
-> Merge Join (cost=0.00..27060.75 rows=10612 width=201) (actual time=26.835..560.550 rows=6159 loops=1)
Merge Cond: ("outer".gid = "inner".gid)
-> Merge Join (cost=0.00..32366.95 rows=10611 width=97) (actual time=0.329..502.846 rows=8679 loops=1)
Merge Cond: ("outer".gid = "inner".gid)
-> Index Scan using tb_site_pkey on tb_site (cost=0.00..988.93 rows=12522 width=89) (actual time=0.065..28.151 rows=8676 loops=1)
Filter: ((able = true) AND (gid <> 9198))
-> Index Scan using index_tb_page_gid_pageid on tb_page (cost=0.00..31371.60 rows=10660 width=8) (actual time=0.086..421.481 rows=8721 loops=1)
Filter: (parent IS NULL)
-> Index Scan using tb_tax_category_pkey on tb_tax_category c (cost=0.00..209.11 rows=9962 width=104) (actual time=0.014..21.975 rows=6161 loops=1)
-> Index Scan using tb_work_release_pkey on tb_work_release (cost=0.00..568.05 rows=8718 width=9) (actual time=0.039..19.559 rows=8718 loops=1)
-> Index Scan using tb_site_profile_pkey on tb_site_profile (cost=0.00..327.33 rows=9593 width=57) (actual time=0.012..18.192 rows=8718 loops=1)
Total runtime: 692.991 ms
(20건 있음)
작업시간: 698.630 ms
|
