PostgreSQL 9.1 - Query on VIEWS taking a lot of time

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: (

It helps to format a query properly to see what's going on. I studied your query and found suspicious SQL:

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';

-
Don't use the same table alias cc in the outer and inner SELECT. While that's not illegal, it helps to confuse you.

-
Without table qualification for the reference to the outer query I am not sure, where the columns client_name_id and vendor_name_id bind. Would need the table definitions to know, but I suspect it results in CROSS JOINs - which is probably not what you intended and the root of the problem.

I suspect the correlated subqueries can be rewritten as plain expressions. Maybe it needs another JOIN. Here is my ...
Educated guess what you actually want:

CREATE VIEW "Swiss_client_wise_minutes_and_profit" AS
SELECT ROW_NUMBER() OVER (ORDER BY r.country, r.destination) AS id
     , c.company_id AS client_name
     , v.company_id AS vendor_name
     , s.c_prefix_id AS c_prefix
     , s.v_prefix_id AS v_prefix
     , r.country
     , r.destination
     , s.c_total_calls
     , s.v_total_calls
     , s.successful_calls
     , s.billed_duration
     , s.v_billed_amount AS cost
     , s.c_billed_amount AS revenue
     , s.c_pdd AS pdd
     , s.profit
     , s.start_time
     , s.end_time
     , s.switch_name
FROM   "Swiss_buz_scdr" s
LEFT   JOIN "Corporate_companyalias" c ON c."Alias_name" = s.client_name_id
LEFT   JOIN "RateManagement_prefix_and_client_wise_destinationgroup" r
         ON r.client_name = c.company_id AND r.prefix = s.c_prefix_id
LEFT   JOIN "Corporate_companyalias" v ON v."Alias_name" = s.vendor_name_id
WHERE  s.customer_name = 'SSP Root';

Aside: I would aim for shorter names than "RateManagement_prefix_and_client_wise_destinationgroup". And preferably legal, lower-case names that don't need double-quotes.

StackExchange Database Administrators Q#21389, answer score: 6