108 reputation
6
bio website alirazabhayani.com
location Karachi, Pakistan
age 31
visits member for 2 years, 1 month
seen 17 hours ago

Pakistani Developer: A self-proclaimed Pythonista, an Open Source Programmer and a Problem Solver.


Feb
18
awarded  Popular Question
Aug
1
awarded  Analytical
Jul
28
awarded  Scholar
Jul
28
accepted PostgreSQL 9.1 - Query on VIEWS taking a lot of time
Jul
28
comment PostgreSQL 9.1 - Query on VIEWS taking a lot of time
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.
Jul
27
awarded  Student
Jul
26
comment PostgreSQL 9.1 - Query on VIEWS taking a lot of time
Here is the link for EXPLAIN ANALYZE output on explain.depesz.com. Here it seems, that the culprit is a different TABLE (companyalias) and not the Partitioned Table (buz_scdr) which had a lot of Data!! Can someone please review the output to confirm the outcomes.
Jul
26
comment PostgreSQL 9.1 - Query on VIEWS taking a lot of time
@ CraigRinger All the referenced Tables are indexed. I have also edited the post above and included the output of 'EXPLAIN ANALYZE' command.
Jul
26
revised PostgreSQL 9.1 - Query on VIEWS taking a lot of time
Added output of EXPLAIN ANALYZE command
Jul
25
comment PostgreSQL 9.1 - Query on VIEWS taking a lot of time
@ CraigRinger Thanks for the help. Except "RateManagement_prefix_and_client_wise_destinationgroup" all the referenced objects are Tables. I am showing the \d tablename output in the pastebin LINK. The output of the SHOW work_mem is : postgres=# SHOW work_mem; work_mem ---------- 128MB (1 row)
Jul
25
comment PostgreSQL 9.1 - Query on VIEWS taking a lot of time
I have Edited the question and have provided the DDL of the concerned VIEW. I have executed the EXPLAIN ANALYZE command but its taking a lot of time ( it has already taken an hour and a half and still no output). @craig-ringer , sorry for the cloned post. At first I posted at stackoverflow but found this place much more suitable for my problem as its specifically for databases.
Jul
25
awarded  Editor
Jul
25
revised PostgreSQL 9.1 - Query on VIEWS taking a lot of time
added 1105 characters in body
Jul
24
asked PostgreSQL 9.1 - Query on VIEWS taking a lot of time
Jul
24
awarded  Autobiographer