view
view を作ったので、そのメモ
swimmar=> select * from topdomain; id | domain ----+------------- 1 | asahi.com 3 | kyodo.co.jp 4 | yahoo.co.jp 5 | cnn.co.jp (4 rows) swimmar=> select * from urllist; id | topid | url ----+-------+---------------------------------- 1 | 1 | http://www.asahi.com/ 3 | 5 | http://www.cnn.co.jp/ 4 | 3 | http://www.kyodo.co.jp/ 5 | 4 | http://dailynews.yahoo.co.jp/fc/ (4 rows) swimmar=> create view domain_url_view as select d.id, d.domain, u.url from topdomain d, urllist u where d.id = u.topid; CREATE VIEW swimmar=> \d List of relations Schema | Name | Type | Owner --------+---------------------+----------+--------- public | contentslist | table | swimmar public | contentslist_id_seq | sequence | swimmar public | domain_url_view | view | swimmar public | topdomain | table | swimmar public | topdomain_id_seq | sequence | swimmar public | urllist | table | swimmar public | urllist_id_seq | sequence | swimmar (7 rows) swimmar=> select * from domain_url_view; id | domain | url ----+-------------+---------------------------------- 1 | asahi.com | http://www.asahi.com/ 3 | kyodo.co.jp | http://www.kyodo.co.jp/ 4 | yahoo.co.jp | http://dailynews.yahoo.co.jp/fc/ 5 | cnn.co.jp | http://www.cnn.co.jp/ (4 rows) swimmar=>EXPLAIN ANALYZE select * from domain_url_view ; QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Hash Join (cost=13.15..40.50 rows=140 width=552) (actual time=0.176..0.229 rows=4 loops=1) Hash Cond: (d.id = u.topid) -> Seq Scan on topdomain d (cost=0.00..21.60 rows=1160 width=36) (actual time=0.024..0.040 rows=4 loops=1) -> Hash (cost=11.40..11.40 rows=140 width=520) (actual time=0.077..0.077 rows=4 loops=1) -> Seq Scan on urllist u (cost=0.00..11.40 rows=140 width=520) (actual time=0.017..0.039 rows=4 loops=1) Total runtime: 0.444 ms (6 rows) swimmar=>