From: | Savita <savita(at)india(dot)hp(dot)com> |
---|---|
To: | shridhar_daithankar(at)persistent(dot)co(dot)in |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: ONE MORE PERFORMANCE ISSUE WITH POSTGRES |
Date: | 2002-10-30 05:19:01 |
Message-ID: | 3DBF6BC5.341B713C@india.hp.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
My machine is a windowsNT machine.
In my stored procedure I am using some statements like
1.select count(*) into cust_count from os_customer_view;
2.select to_number(substring(os_crm_exchange_unit_id, 4), 999999999999) into id
from os_unit_view offset row;
SO If I call a query which is using this stored procedure than execution is very slow.
Is there any way to fine tune the statements inside the stored procedure.I have
created index on os_crm_exchange_unit_id field.
Like in ORACLE if you use count(*),sub-string,to-number then indexing won't work is
there something like this is there in postgres also.Is yes then what is the
appropriate solution for this.
Shridhar Daithankar wrote:
> On 29 Oct 2002 at 16:42, Savita wrote:
> > I just wanted to know is it possible to improve the performance of a query if
> > the query is calling any stored procedure in it.
>
> Generally if you are making 3-4 calls for select/update/insert, a stored
> procedure works much better compared to separate calls to database because
> communication overhead is only once and database probably pre-parse-pre-plan
> the queries in it. So yes, it does improve performance..
>
>
> > Because when I run this query it takes 100% CPU usage and other applications are
> > hanging.
>
> What's the query? Could you elaborate more on that?
>
> And other applications are hanging is rediculous behaviour unless it's windows.
> On linux, any 100% CPU chewing task should not block any other task. At least
> 2.4.19 onwards, same goes for other unices.. Windows is a different story.(My
> mail client, pegasus maxes out CPU, if it can not reach SMTP host for some
> reason. This is XP/SP1.. Go figure)
>
> Bye
> Shridhar
>
> --
> Dijkstra probably hates me(Linus Torvalds, in kernel/sched.c)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
--
Best Regards
- Savita
----------------------------------------------------
Hewlett Packard (India)
+91 80 2051288 (Phone)
847 1288 (HP Telnet)
----------------------------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Bartley | 2002-10-30 06:06:40 | duplicate key in unique index pg_statistic_relid_att_index |
Previous Message | Bruce Momjian | 2002-10-30 05:04:15 | Re: move 0 behaviour |