CREATE TABLE calls
(
  id bigserial NOT NULL,
  callid character( 32 ),
  host inet NOT NULL,
  dst_number_in character varying( 24 ),
  dst_number_out character varying( 24 ),
  src_number_bill character varying( 24 ),
  dst_number_bill character varying( 24 ),
  src_ip inet,
  dst_ip inet,
  elapsed_time integer NOT NULL,
  setup_time timestamp with time zone NOT NULL,
  connect_time timestamp with time zone,
  disconnect_time timestamp with time zone,
  CONSTRAINT call_pk PRIMARY KEY (id)
)
WITHOUT OIDS;

CREATE TABLE areacodes
(
  id bigserial NOT NULL,
  mask character varying( 24 ),
  destinationid integer,
  todate date,
  CONSTRAINT areacodes_pk PRIMARY KEY (id),
  CONSTRAINT areacodes_fk1 FOREIGN KEY (destinationid)
      REFERENCES destinations (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITHOUT OIDS;

CREATE INDEX fki_areacodes_fk1
  ON areacodes
  USING btree
  (destinationid);

CREATE TABLE networks
(
  id bigserial NOT NULL,
  contractid integer NOT NULL,
  net inet NOT NULL,
  CONSTRAINT network_pk PRIMARY KEY (id),
  CONSTRAINT networks_fk1 FOREIGN KEY (contractid)
      REFERENCES contracts (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT networks_unq_idx1 UNIQUE (net)
)
WITHOUT OIDS;

CREATE INDEX fki_networks_fk1
  ON networks
  USING btree
  (contractid);

CREATE TABLE rates
(
  id bigserial NOT NULL,
  contractid integer NOT NULL,
  mask character varying( 20 ),
  price numeric( 7 , 5 ) NOT NULL DEFAULT  0 ,
  fromdate date NOT NULL,
  todate date,
  day_start integer NOT NULL DEFAULT  0 ,
  day_end integer NOT NULL DEFAULT  6 ,
  time_start integer NOT NULL DEFAULT  0 ,
  time_end integer NOT NULL DEFAULT  1440 ,
  CONSTRAINT rates_pk PRIMARY KEY (id),
  CONSTRAINT contracts_fk1 FOREIGN KEY (contractid)
      REFERENCES contracts (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT rates_unq_idx1 UNIQUE (mask, contractid, fromdate, todate)
)
WITHOUT OIDS;

CREATE INDEX fki_contracts_fk1
  ON rates
  USING btree
  (contractid);

SELECT to_char(d.connect_time, 'yyyy-MON'::text) AS period, c.contractid, c.mask, c.price, c.fromdate, c.todate, sum(d.elapsed_time) AS duration
   FROM rates c
   RIGHT JOIN ( SELECT a.id, a.callid, b.contractid, a.connect_time, a.dst_number_bill, a.elapsed_time
           FROM calls a
      LEFT JOIN networks b ON b.net >>= a.src_ip) d ON c.id = (( SELECT e.id
      FROM rates e
     WHERE e.contractid = d.contractid AND d.connect_time >= e.fromdate AND (d.connect_time < e.todate OR e.todate IS NULL) AND d.dst_number_bill::text ~~ (e.mask::text || '%'::text)
     ORDER BY e.mask DESC
    LIMIT  1 ))
  GROUP BY c.contractid, to_char(d.connect_time, 'yyyy-MON'::text), c.mask, c.price, c.fromdate, c.todate;

"GroupAggregate  (cost=34771406.75..34808977.99 rows=1073464 width=43) (actual time=58584.692..58682.392 rows=428 loops=1)"
"  ->  Sort  (cost=34771406.75..34774090.41 rows=1073464 width=43) (actual time=58581.694..58597.169 rows=74032 loops=1)"
"        Sort Key: c.contractid, to_char(a.connect_time, 'yyyy-MON'::text), c.mask, c.price, c.fromdate, c.todate"
"        ->  Merge Right Join  (cost=176890.10..34640315.42 rows=1073464 width=43) (actual time=55762.697..56011.846 rows=74032 loops=1)"
"              Merge Cond: ("outer".id = "inner"."?column5?")"
"              ->  Index Scan using rates_pk on rates c  (cost=0.00..54.35 rows=1733 width=39) (actual time=0.016..1.930 rows=1733 loops=1)"
"              ->  Sort  (cost=176890.10..179573.76 rows=1073464 width=31) (actual time=55762.625..55780.858 rows=74032 loops=1)"
"                    Sort Key: (subplan)"
"                    ->  Nested Loop Left Join  (cost=1.32..50919.52 rows=1073464 width=31) (actual time=0.207..55399.577 rows=74032 loops=1)"
"                          Join Filter: ("inner".net >>= "outer".src_ip)"
"                          ->  Seq Scan on calls a  (cost=0.00..2612.32 rows=74032 width=38) (actual time=0.003..81.470 rows=74032 loops=1)"
"                          ->  Materialize  (cost=1.32..1.61 rows=29 width=15) (actual time=0.000..0.008 rows=35 loops=74032)"
"                                ->  Seq Scan on networks b  (cost=0.00..1.29 rows=29 width=15) (actual time=0.003..0.038 rows=35 loops=1)"
"                          SubPlan"
"                            ->  Limit  (cost=32.03..32.04 rows=1 width=17) (actual time=0.722..0.722 rows=1 loops=74032)"
"                                  ->  Sort  (cost=32.03..32.04 rows=1 width=17) (actual time=0.721..0.721 rows=1 loops=74032)"
"                                        Sort Key: mask"
"                                        ->  Bitmap Heap Scan on rates e  (cost=4.21..32.02 rows=1 width=17) (actual time=0.413..0.716 rows=1 loops=74032)"
"                                              Recheck Cond: (contractid = $0)"
"                                              Filter: (($1 >= fromdate) AND (($1 < todate) OR (todate IS NULL)) AND (($2)::text ~~ ((mask)::text || '%'::text)))"
"                                              ->  Bitmap Index Scan on fki_contracts_fk1  (cost=0.00..4.21 rows=347 width=0) (actual time=0.078..0.078 rows=364 loops=74032)"
"                                                    Index Cond: (contractid = $0)"
"Total runtime: 58698.390 ms"