Using PostgreSQL 9.1, we are having problems while executing queries on a VIEW of PostgreSQL. Following is the situation:
We have a partitioned Table "buz_scdr" over which we have built a VIEW "Swiss_client_wise_minutes_and_profit". The purpose of this VIEW is to join data from different tables including the "buz_scdr" table to make efficient queries. This strategy was working fine until the Table "buz_scdr" became gigantic(overall records in all the partitions became huge. The table is partitioned on the basis of Date).
The query being executed on this VIEW started taking very long time (around 5 to 10 mins). For figuring out why this query is taking so long to execute, we used EXPLAIN command to show its Execution PLAN. The Query which we used is as follows:
EXPLAIN SELECT * from "Swiss_client_wise_minutes_and_profit" where start_time = '2012-7-22 08:00';
Its results are on explain.depesz.com here or as follows:
Subquery Scan on "Swiss_client_wise_minutes_and_profit" (cost=2127919.71..94874537.55 rows=40474 width=677) Filter: ("Swiss_client_wise_minutes_and_profit".start_time = '2012-07-22 08:00:00+00'::timestamp with time zone) -> WindowAgg (cost=2127919.71..94773352.06 rows=8094839 width=148) -> Sort (cost=2127919.71..2148156.81 rows=8094839 width=148) Sort Key: cc.name, rdga.group_id -> Hash Left Join (cost=1661.50..604234.77 rows=8094839 width=148) Hash Cond: (((cc.company_id)::text = (rdga.company_id)::text) AND ((cs.c_prefix_id)::text = (rdga.dest_id)::text)) -> Hash Left Join (cost=7.88..460615.39 rows=8094839 width=123) Hash Cond: ((cs.client_name_id)::text = (cc."Alias_name")::text) -> Append (cost=0.00..349303.48 rows=8094839 width=111) -> Seq Scan on "Swiss_buz_scdr" cs (cost=0.00..1.06 rows=1 width=610) Filter: ((customer_name)::text = 'SSP Root'::text) -> Seq Scan on scdr_buz__2012_07_11 cs (cost=0.00..349302.41 rows=8094838 width=111) Filter: ((customer_name)::text = 'SSP Root'::text) -> Hash (cost=5.17..5.17 rows=217 width=24) -> Seq Scan on "Corporate_companyalias" cc (cost=0.00..5.17 rows=217 width=24) -> Hash (cost=1334.42..1334.42 rows=21280 width=50) -> Hash Join (cost=169.56..1334.42 rows=21280 width=50) Hash Cond: ((rdga.company_id)::text = (c.name)::text) -> Hash Join (cost=162.68..1034.93 rows=21280 width=50) Hash Cond: ((rdga.group_id)::text = (rdg.name)::text) -> Seq Scan on "RateManagement_destgroupassign" rdga (cost=0.00..497.35 rows=25935 width=40) -> Hash (cost=123.64..123.64 rows=3123 width=32) -> Hash Join (cost=13.08..123.64 rows=3123 width=32) Hash Cond: (rdg.country_id = cc.id) -> Seq Scan on "RateManagement_destinationgroup" rdg (cost=0.00..65.06 rows=3806 width=26) -> Hash (cost=7.48..7.48 rows=448 width=14) -> Seq Scan on "Corporate_country" cc (cost=0.00..7.48 rows=448 width=14) -> Hash (cost=4.17..4.17 rows=217 width=16) -> Seq Scan on "Corporate_company" c (cost=0.00..4.17 rows=217 width=16) SubPlan 1 -> Seq Scan on "Corporate_companyalias" cc (cost=0.00..5.71 rows=1 width=12) Filter: (("Alias_name")::text = (cs.client_name_id)::text) SubPlan 2 -> Seq Scan on "Corporate_companyalias" cc (cost=0.00..5.71 rows=1 width=12) Filter: (("Alias_name")::text = (cs.vendor_name_id)::text) (36 rows)
The result above of the EXPLAIN command shows that our query is sequentially scanning the "buz_scdr" Table (as highlighted above) which contains total 8094838 records. The query on the VIEW is not following the partition constraints (date) of "buz_scdr", which is causing it to scan the whole Table.
For experimental purpose, we directly executed a query on the "buz_scdr" TABLE with a WHERE statement along with date and time , and it duly respected the partition constraints and didn't scan the whole table. This indicates that the query executed directly on the partitioned Table is working as expected but the VIEW built upon it is having problems.
Is this a global problem with PostgreSQL Views or have I missed something?
EDIT: Following is the DDL of the VIEW "Swiss_client_wise_minutes_and_profit"
CREATE VIEW "Swiss_client_wise_minutes_and_profit" AS SELECT ROW_NUMBER() OVER (ORDER BY rp.country, rp.destination) As id, (SELECT company_id FROM "Corporate_companyalias" AS cc WHERE cc."Alias_name" = client_name_id) AS client_name, (SELECT company_id FROM "Corporate_companyalias" AS cc WHERE cc."Alias_name" = vendor_name_id) AS vendor_name, cs.c_prefix_id AS c_prefix, cs.v_prefix_id AS v_prefix, rp.country, rp.destination, cs.c_total_calls, cs.v_total_calls, cs.successful_calls, cs.billed_duration, cs.v_billed_amount AS cost, cs.c_billed_amount AS revenue, cs.c_pdd AS pdd, cs.profit, cs.start_time, cs.end_time, cs.switch_name FROM "Swiss_buz_scdr" AS cs LEFT JOIN "Corporate_companyalias" AS cc ON cs.client_name_id = cc."Alias_name" LEFT JOIN "RateManagement_prefix_and_client_wise_destinationgroup" AS rp ON rp.client_name = cc.company_id AND rp.prefix = cs.c_prefix_id WHERE cs.customer_name = 'SSP Root';
EDIT 2 : Here is the link for the output of "EXPLAIN ANALYZE" command: