PostgreSQL 9.1 - Query on VIEWS taking a lot of time

Question

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:

EXPLAIN ANALYZE output on pastebin

Answer 1

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.