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=>
