Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
Could you run EXPLAIN ANALYZE? That gives you information about where the time is spend –  Frank Heikens Jul 24 '12 at 12:38
    
In general, when asking for help tuning queries, we will need information on the structure of all tables involved, including all constraints, indexes, etc. A DDL script which makes all the tables and the view which has the problem will be fine. And we will need some representative data -- or a way to generate representative data. –  Colin 't Hart Jul 24 '12 at 12:50
    
This post is cloned from stackoverflow.com/questions/11626687/… –  Craig Ringer Jul 24 '12 at 13:28
1  
Yep, need explain analyze and please post the result to explain.depesz.com for readability. See also wiki.postgresql.org/wiki/Slow_Query_Questions –  Craig Ringer Jul 24 '12 at 13:33
2  
Looking at the posted plan some more, I think you also need to show your work_mem - and you should try running the query with temp table logging enabled. With all these hashes you'll need a fair bit of work mem for performance - but if you have too much you might start the machine swapping, causing miserable performance. Post the output of SHOW work_mem; in psql, and try running the query with SET client_min_messages = DEBUG ; SET log_temp_files = 0; –  Craig Ringer Jul 25 '12 at 11:52

1 Answer 1

up vote 1 down vote accepted

Sometimes 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 alias cc in the outer and inner SELECT.

  • 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 pointless CROSS JOINS - which is probably not what you intended and also probably the root of the problem.

I suspect that the correlated subqueries can be rewritten as plain expressions. Maybe it needs another JOIN. Here is my ...

Educated guess what you actually may 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';

As an aside: I would aim for shorter names than "RateManagement_prefix_and_client_wise_destinationgroup".

share|improve this answer
1  
Thanks!!@Erwin Brandstetter.You were right.The correlated subqueries were the problem and after using another JOIN as you adviced,the problem is resolved. According to the EXPLAIN ANALYZE, time taken to seq_scan "Corporate_companyalias" was 1321510.106 ms and now after making the suggested changes, the whole query is taking around 12 secs!! THANKS. But still, the query on the VIEW is not following the partition constraints(date) of "buz_scdr",which is causing it to scan the whole Table rather than a single partition.Currently there are two partitions in this table and they both are being hit. –  Ali Raza Bhayani Jul 28 '12 at 17:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.