Postgres Pro
Downloads
Home   >   mailing lists

Thread: Need Help on query tuning

Need Help on query tuning

From
"Somasekhar Bangalore"
Date:
Hi,
 
I am having 7.2.3 version of postgres database. I have huge data in my database. I have a couple of views and functions used in the application.if i run the views. The views take a long time to get the data. I am joining just 4 tables namely roles,link_roles ,link_rights ,rights.
 
Records in the table are as follows. roles = 49,
                                                    link_roles =21399,
                                                    link_rights =371
                                                    and rights =108.
 
 
 create view view_chkACL1(usr_guid,ref_guid,rol_guid,OBJ_TYPE,scope,rol_name,rgt_guid,rgt_name) as
select b.rol_assignee_guid,b.ref_guid,a.rol_guid,a.OBJ_TYPE,a.scope,a.name,d.rgt_guid,d.name
from roles a, link_roles b, link_rights c,rights d where a.rol_guid = b.rol_guid
and a.OBJ_TYPE = b.OBJ_TYPE and a.record_flag = '01' and b.record_flag = '01'
and c.rol_guid = b.rol_guid
and c.OBJ_TYPE =  b.OBJ_TYPE
and c.record_flag ='01'
and d.rgt_guid = c.rgt_guid
and d.record_flag = '01';
 
I appreciate if some one can direct me how to enhance the performance.

Regards,
Somasekhar Bangalore
Principal Software Engineer
ZenSutra Software Technologies Pvt. Ltd.
Suite 601, HM Geneva House
#14, Cunningham Road
Bangalore 560-052, India

Ph:+91-80-235-0481
Fax:+91-80-235-0486
Email: sbangalore@zensutra.com

Weaving the knowledge tapestry'

 

Re: Need Help on query tuning

From
"Mendola Gaetano"
Date:
""Somasekhar Bangalore"" <sbangalore@zensutra.com> wrote:
>Hi,

>I am having 7.2.3 version of postgres database. I have huge data in my
database. I have a couple of views and >functions used in the application.if
i run the views. The views take a long time to get the data. I am joining
just 4 >tables namely roles,link_roles ,link_rights ,rights.

>Records in the table are as follows. roles = 49,
>                                                    link_roles =21399,
>                                                    link_rights =371
>                                                    and rights =108.


> create view
view_chkACL1(usr_guid,ref_guid,rol_guid,OBJ_TYPE,scope,rol_name,rgt_guid,rgt
_name) as
>select
b.rol_assignee_guid,b.ref_guid,a.rol_guid,a.OBJ_TYPE,a.scope,a.name,d.rgt_gu
id,d.name
>from roles a, link_roles b, link_rights c,rights d where a.rol_guid =
b.rol_guid
>and a.OBJ_TYPE = b.OBJ_TYPE and a.record_flag = '01' and b.record_flag =
'01'
>and c.rol_guid = b.rol_guid
>and c.OBJ_TYPE =  b.OBJ_TYPE
>and c.record_flag ='01'
>and d.rgt_guid = c.rgt_guid
>and d.record_flag = '01';

May we see the explain analyze for your select and the defintion of your
tables?


Regards
Gaetano Mendola