whirlwind
TDD infected, paranoid
Оптимизировать сортировку
Есть запрос
[sql]
SELECT tab_file.id, tab_file.name, tab_file.path, tab_file.timepos, tab_file.size, tab_file.host, tab_host.id, tab_host.name, tab_host.port, tab_host.proto, tab_host.ip FROM tab_file,tab_host WHERE (((tab_file.timepos>='2007-05-02 00:00:00')) AND NOT EXISTS(SELECT ref FROM tab_lexindex AS subq WHERE subq.lex IN ('topplan2', 'для') AND subq.ref=tab_file.id) AND tab_host.id=tab_file.host) ORDER BY tab_file.path,tab_file.name LIMIT 20;
[/sql]
такого плана
Можно ли как-то ускорить сортировку?
Есть запрос
[sql]
SELECT tab_file.id, tab_file.name, tab_file.path, tab_file.timepos, tab_file.size, tab_file.host, tab_host.id, tab_host.name, tab_host.port, tab_host.proto, tab_host.ip FROM tab_file,tab_host WHERE (((tab_file.timepos>='2007-05-02 00:00:00')) AND NOT EXISTS(SELECT ref FROM tab_lexindex AS subq WHERE subq.lex IN ('topplan2', 'для') AND subq.ref=tab_file.id) AND tab_host.id=tab_file.host) ORDER BY tab_file.path,tab_file.name LIMIT 20;
[/sql]
такого плана
Код:
Limit (cost=732.25..732.30 rows=20 width=142) (actual time=453.000..453.000 rows=20 loops=1)
-> Sort (cost=732.25..732.31 rows=25 width=142) (actual time=453.000..453.000 rows=20 loops=1)
Sort Key: tab_file.path, tab_file.name
-> Nested Loop (cost=0.00..731.67 rows=25 width=142) (actual time=0.000..390.000 rows=219 8 loops=1)
Join Filter: ("outer".id = "inner".host)
-> Seq Scan on tab_host (cost=0.00..1.01 rows=1 width=29) (actual time=0.000..0.000 rows=1 loops=1)
-> Index Scan using tab_file_timeposidx on tab_file (cost=0.00..730.34 rows=25 width=113) (actual time=0.000..375.000 rows=2198 loops=1)
Index Cond: (timepos >= '2007-05-02 00:00:00'::timestamp without time zone)
Filter: (NOT (subplan))
SubPlan
-> Index Scan using tab_lexindex_refidx on tab_lexindex subq (cost=0.00..12.06 rows=1 width=4) (actual time=0.037..0.037 rows=1 loops=8949)
Index Cond: (ref = $0)
Filter: (((lex)::text = 'topplan2'::text) OR ((lex)::text = 'для'::text))
Total runtime: 453.000 ms
(14 rows)