Queries very slow after data size increases

Following are the tables

---------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE  equipment (

  contract_nr varchar(32) COLLATE "C" NULL DEFAULT NULL,

  name varchar(64) COLLATE "C" DEFAULT '',

  latitude numeric(10,7) NOT NULL,

  longitude numeric(10,7) NOT NULL,

  mac_addr_w varchar(17) COLLATE "C" NOT NULL,

  mac_addr_wl varchar(17) COLLATE "C" NOT NULL,

  identifier varchar(32) COLLATE "C" NOT NULL,

  he_identifier varchar(17) COLLATE "C" DEFAULT '',

  number_of_wlans integer NOT NULL DEFAULT '1' ,

  regions varchar(64) COLLATE "C" DEFAULT '',

  external_id varchar(64) COLLATE "C",   

  PRIMARY KEY (external_id)

) ;

CREATE INDEX equipment_mac_addr_w_idx ON equipment (mac_addr_w);

CREATE INDEX equipment_latitude_idx ON equipment (latitude);

CREATE INDEX equipment_longitude_idx ON equipment (longitude);

no of rows - 15000

---------------------------------------------------------------------------------------------------------------------------------------------------------------

create table accounting (

  equip_wan varchar(17) COLLATE "C" NOT NULL,

  equip_wlan varchar(17) COLLATE "C" NOT NULL,

  identifier varchar(32) COLLATE "C" NOT NULL,

  he_identifier varchar(17) COLLATE "C" NULL DEFAULT NULL,

  time_stamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,

  in_oc bigint NOT NULL DEFAULT 0,

  out_oc bigint NOT NULL DEFAULT 0

  );

CREATE INDEX accounting_time_stamp_idx ON accounting (time_stamp);

CREATE INDEX accounting_equip_wan_idx ON accounting (equip_wan);

no of rows - 36699300

---------------------------------------------------------------------------------------------------------------------------------------------------------------

create table accounting_fifteenminute_aggregate (

  equip_wan varchar(17) COLLATE "C" NOT NULL,

  identifier varchar(32) COLLATE "C" NOT NULL,

  time_stamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,  

  in_oc bigint NOT NULL DEFAULT 0,

  out_oc bigint NOT NULL DEFAULT 0

  );

CREATE INDEX accounting_15min_agg_timestamp_idx ON accounting_fifteenminute_aggregate (time_stamp);

CREATE INDEX accounting_15min_agg_equip_wan_idx ON accounting_fifteenminute_aggregate (equip_wan);

no of rows - 4800000

---------------------------------------------------------------------------------------------------------------------------------------------------------------

create table accounting_hourly_aggregate (

  equip_wan varchar(17) COLLATE "C" NOT NULL,

  identifier varchar(32) COLLATE "C" NOT NULL,

  time_stamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,  

  in_oc bigint NOT NULL DEFAULT 0,

  out_oc bigint NOT NULL DEFAULT 0

  );

CREATE INDEX accounting_hourly_agg_timestamp_idx ON accounting_hourly_aggregate (time_stamp);

CREATE INDEX accounting_hourly_agg_equip_wan_idx ON accounting_hourly_aggregate (equip_wan);

no of rows - 1400000

<TABLE DEFINITION ENDS>---------------------------------------------------------------------------------------------------------------------------------------------------------------

(1) INSERT INTO accounting_fifteenminute_aggregate 

Select equip_wan,identifier,'2014-04-16 14:00:00.0',sum(in_oc),sum(out_oc) 

from accounting where time_stamp >= '2014-04-16 13:45:00.0' and time_stamp < '2014-04-16 14:00:00.0' group by equip_wan,identifier

time taken for execution of the above query - 300 sec

EXPLAIN (ANALYZE, BUFFERS) 

                                                                                      QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 Insert on accounting_fifteenminute_aggregate  (cost=253.37..253.47 rows=4 width=89) (actual time=196833.655..196833.655 rows=0 loops=1)

   Buffers: shared hit=23941 read=4092 dirtied=2675

   ->  Subquery Scan on "*SELECT*"  (cost=253.37..253.47 rows=4 width=89) (actual time=3621.621..3763.701 rows=3072 loops=1)

         Buffers: shared hit=3494 read=93

         ->  HashAggregate  (cost=253.37..253.41 rows=4 width=41) (actual time=3621.617..3737.370 rows=3072 loops=1)

               Buffers: shared hit=3494 read=93

               ->  Index Scan using accounting_time_stamp_idx on accounting  (cost=0.00..220.56 rows=3281 width=41) (actual time=3539.890..3601.808 rows=3680 loops=1)

                     Index Cond: ((time_stamp >= '2014-04-16 13:45:00+05:30'::timestamp with time zone) AND (time_stamp < '2014-04-16 14:00:00+05:30'::timestamp with time zone))

                     Buffers: shared hit=3494 read=93

 Total runtime: 196833.781 ms

(10 rows)

(2) INSERT INTO accounting_hourly_aggregate 

Select equip_wan,identifier,'2014-04-16 14:00:00.0',sum(in_oc),sum(out_oc) 

from accounting_fifteenminute_aggregate where time_stamp > '2014-04-16 13:00:00.0' group by equip_wan,identifier

time taken for execution of the above query - 280 sec

*************************************************************************************************************************************

The below query is report query which uses the above aggregated tables

Select

queryA.wAddr,

queryA.name,

queryA.dBy,

queryA.upBy,

(queryA.upBy + queryA.dBy) as totalBy

From

(Select 

queryC.wAddr,

queryC.name,

COALESCE(queryI.dBy, 0) as dBy,

COALESCE(queryI.upBy, 0) as upBy

From

(Select 

DISTINCT ON(mac_addr_w) 

mac_addr_w as wAddr,

name

From equipment

where 

(latitude BETWEEN -90.0 AND 90.0)  AND 

(longitude BETWEEN -180.0 AND 180.0) 

) as queryC

Left Join

(Select 

equip_wan as wAddr,

SUM(in_oc) as dBy,

SUM(out_oc) as upBy

From accounting_hourly_aggregate

where time_stamp > '2014-04-13 16:00:00.0' and time_stamp <= '2014-04-14 16:00:00.0'

Group by equip_wan) as queryI

On queryC.wAddr = queryI.wAddr) as queryA

order by totalBy DESC Limit 10;

Above query execution takes - 3 min 28 sec.

So I did a manual analyze to see if any performance benefit is obtained. Analyze accounting_hourly_aggregate takes 40 sec.

After analysis same query takes 16 sec.

But 40 mins after analyzing accounting_hourly_aggregate table, the above query execution time again increases to  few minutes.

The above query is run from command line of postgres. 

Auto vacuum is by default running.

Explain of the above query

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 Limit  (cost=11664.77..11664.80 rows=10 width=92) (actual time=159613.007..159613.010 rows=10 loops=1)

   Buffers: shared hit=2282 read=3528

   ->  Sort  (cost=11664.77..11689.77 rows=10000 width=92) (actual time=159613.005..159613.007 rows=10 loops=1)

         Sort Key: ((COALESCE(queryI.upBy, 0::numeric) + COALESCE(queryI.dBy, 0::numeric)))

         Sort Method: top-N heapsort  Memory: 26kB

         Buffers: shared hit=2282 read=3528

         ->  Merge Left Join  (cost=9748.22..11448.68 rows=10000 width=92) (actual time=157526.220..159607.130 rows=10000 loops=1)

               Merge Cond: ((equipment.mac_addr_w)::text = (queryI.wAddr)::text)

               Buffers: shared hit=2282 read=3528

               ->  Unique  (cost=0.00..1538.56 rows=10000 width=28) (actual time=84.291..2151.497 rows=10000 loops=1)

                     Buffers: shared hit=591 read=840

                     ->  Index Scan using equipment_mac_addr_w_idx on equipment  (cost=0.00..1499.35 rows=15684 width=28) (actual time=84.288..2145.990 rows=15684 loops=1)

                           Filter: ((latitude >= (-90.0)) AND (latitude <= 90.0) AND (longitude >= (-180.0)) AND (longitude <= 180.0))

                           Buffers: shared hit=591 read=840

               ->  Sort  (cost=9748.22..9750.20 rows=793 width=82) (actual time=157441.910..157443.710 rows=6337 loops=1)

                     Sort Key: queryI.wAddr

                     Sort Method: quicksort  Memory: 688kB

                     Buffers: shared hit=1691 read=2688

                     ->  Subquery Scan on queryI  (cost=9694.17..9710.03 rows=793 width=82) (actual time=157377.819..157381.314 rows=6337 loops=1)

                           Buffers: shared hit=1691 read=2688

                           ->  HashAggregate  (cost=9694.17..9702.10 rows=793 width=34) (actual time=157377.819..157380.154 rows=6337 loops=1)

                                 Buffers: shared hit=1691 read=2688

                                 ->  Index Scan using accounting_hourly_agg_idx on accounting_hourly_aggregate  (cost=0.00..8292.98 rows=186826 width=34) (actual time=1328.363..154164.439 rows=193717 loops=1)

                                       Index Cond: ((time_stamp > '2014-04-14 12:00:00+05:30'::timestamp with time zone) AND (time_stamp <= '2014-04-15 18:00:00+05:30'::timestamp with time zone))

                                       Buffers: shared hit=1691 read=2688

 Total runtime: 159613.100 ms

(26 rows)

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Following values have been changed in postgresql.conf

shared_buffers = 2GB

work_mem = 32MB

maintenance_work_mem = 512MB

wal_buffers = 16MB

checkpoint_segments = 32

checkpoint_timeout = 15min

checkpoint_completion_target = 0.9

System config - 

8 gb RAM

hard disk - 300 gb

Linux 2.6.32-358.el6.x86_64 #1 SMP Tue Jan 29 11:47:41 EST 2013 x86_64 x86_64 x86_64 GNU/Linux

Postgres version

PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit

Basically all the queries are taking time, as the raw tables size increases. Will partitioning help ?