Lists: | pgsql-generalpgsql-performance |
---|
From: | JM <jerome(at)gmanmi(dot)tv> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | HELP speed up my Postgres |
Date: | 2004-11-25 06:00:32 |
Message-ID: | 200411251400.32752.jerome@gmanmi.tv |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-performance |
Hi ALL,
Ive been using postgres for 3 years and now we are having problems with its
performance.
Here are some givens..
We have 260 subscription tables per Database.
We have 2 databases.
Our main client has given us 250,000 mobile numbers to deactivate.
--
We we are experiencing
91,000 mobile numbers to deactive it took a week to finish for 1 DB only
the second DB is still in the process of deactivating
Algorithm to deactivate:
we loaded all subscription tables names into a table
we loaded all mobile numbers to deactivate into a table
SQL:
update SUBSCRIPTIONTABLE set ACTIVEFLAG='Y' where mobile_num in (select
mobile_num from LOADED_MOBILE_NUMBERS)
the script was made is "C"
COFIG FILE:
# This is ARA nmimain
tcpip_socket = true
max_connections = 150
superuser_reserved_connections = 2
port = 5433
shared_buffers = 45600
sort_mem = 40000
max_locks_per_transaction=128
#fsync = true
#wal_sync_method = fsync
#
# Locale settings
#
# (initialized by initdb -- may be changed)
LC_MESSAGES = 'en_US.UTF-8'
LC_MONETARY = 'en_US.UTF-8'
LC_NUMERIC = 'en_US.UTF-8'
LC_TIME = 'en_US.UTF-8'
.. DB is being vaccumed every week
my box is running on a DUAL Xeon, 15K RPM with 2 G Mem.
that box is running 2 instances of PG DB.
TIA,
From: | "Vishal Kashyap (at) [SaiHertz]" <vishalonlist(at)gmail(dot)com> |
---|---|
To: | jerome(at)gmanmi(dot)tv |
Cc: | pgsql-performance(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org |
Subject: | Re: [PERFORM] HELP speed up my Postgres |
Date: | 2004-11-25 06:12:18 |
Message-ID: | 77b69d2104112422126218b407@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-performance |
Dear JM ,
> Ive been using postgres for 3 years and now we are having problems with its
PostgrSQL version please
--
With Best Regards,
Vishal Kashyap.
Lead Software Developer,
http://saihertz.com,
http://vishalkashyap.tk
From: | JM <jerome(at)gmanmi(dot)tv> |
---|---|
To: | "Vishal Kashyap (at) [SaiHertz]" <vishalonlist(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org |
Subject: | Re: [PERFORM] HELP speed up my Postgres |
Date: | 2004-11-25 06:29:48 |
Message-ID: | 200411251429.48440.jerome@gmanmi.tv |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-performance |
PG Version 7.3.4
On Thursday 25 November 2004 14:12, Vishal Kashyap @ [SaiHertz] wrote:
> Dear JM ,
>
> > Ive been using postgres for 3 years and now we are having
> > problems with its
>
> PostgrSQL version please
From: | "Iain" <iain(at)mst(dot)co(dot)jp> |
---|---|
To: | <jerome(at)gmanmi(dot)tv> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: [PERFORM] HELP speed up my Postgres |
Date: | 2004-11-25 06:40:57 |
Message-ID: | 002001c4d2b9$bba0b230$7201a8c0@mst1x5r347kymb |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-performance |
> SQL:
> update SUBSCRIPTIONTABLE set ACTIVEFLAG='Y' where mobile_num in (select
> mobile_num from LOADED_MOBILE_NUMBERS)
Could you try using UPDATE ... FROM (SELECT ....) AS .. style syntax?
About 20 minutes ago, I changed a 8 minute update to an most instant by
doing that.
regards
Iain
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | jerome(at)gmanmi(dot)tv |
Cc: | "Vishal Kashyap (at) [SaiHertz]" <vishalonlist(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org |
Subject: | Re: [PERFORM] HELP speed up my Postgres |
Date: | 2004-11-25 06:55:25 |
Message-ID: | 25493.1101365725@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-performance |
JM <jerome(at)gmanmi(dot)tv> writes:
> PG Version 7.3.4
Avoid the "IN (subselect)" construct then. 7.4 is the first release
that can optimize that in any real sense.
regards, tom lane
From: | Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> |
---|---|
To: | jerome(at)gmanmi(dot)tv |
Cc: | pgsql-performance(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org |
Subject: | Re: [PERFORM] HELP speed up my Postgres |
Date: | 2004-11-25 07:06:27 |
Message-ID: | 41A58473.5020106@familyhealth.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-performance |
> update SUBSCRIPTIONTABLE set ACTIVEFLAG='Y' where mobile_num in (select
> mobile_num from LOADED_MOBILE_NUMBERS)
Change to:
update SUBSCRIPTIONTABLE set ACTIVEFLAG='Y' where exists (select 1 from
LOADED_MOBILE_NUMBERS lmn where
lmn.mobile_num=SUBSCRIPTIONTABLE.mobile_num);
That should run a lot faster.
Make sure you have indexes on both mobile_num columns.
Chris
From: | Klint Gore <kg(at)kgb(dot)une(dot)edu(dot)au> |
---|---|
To: | jerome(at)gmanmi(dot)tv |
Cc: | pgsql-general(at)postgresql(dot)org, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: [PERFORM] HELP speed up my Postgres |
Date: | 2004-11-25 07:08:30 |
Message-ID: | 41A584EE111.0263KG@129.180.47.120 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-performance |
On Thu, 25 Nov 2004 14:00:32 +0800, JM <jerome(at)gmanmi(dot)tv> wrote:
> update SUBSCRIPTIONTABLE set ACTIVEFLAG='Y' where mobile_num in (select
> mobile_num from LOADED_MOBILE_NUMBERS)
does loaded_mobile_numbers have a primary key or index on mobile_num?
same for subscriptiontable?
have you analyzed both tables?
is mobile_num the same type in both tables?
how does this query compare?
update SUBSCRIPTIONTABLE set ACTIVEFLAG='Y'
from loaded_mobile_numbers
where subscriptiontable.mobile_num = LOADED_MOBILE_NUMBERS.mobile_num
klint.
+---------------------------------------+-----------------+
: Klint Gore : "Non rhyming :
: EMail : kg(at)kgb(dot)une(dot)edu(dot)au : slang - the :
: Snail : A.B.R.I. : possibilities :
: Mail University of New England : are useless" :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : +61 2 6772 5376 : :
+---------------------------------------+-----------------+
From: | "Anatoly Okishev" <rolly74(at)mail(dot)ru> |
---|---|
To: | <jerome(at)gmanmi(dot)tv> |
Cc: | "pgsql-general" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: HELP speed up my Postgres |
Date: | 2004-11-25 09:21:00 |
Message-ID: | 077301c4d2d0$1608ea10$191716ac@ws5 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-performance |
> SQL:
> update SUBSCRIPTIONTABLE set ACTIVEFLAG='Y' where mobile_num in (select
> mobile_num from LOADED_MOBILE_NUMBERS)
You can try this:
update SUBSCRIPTIONTABLE, LOADED_MOBILE_NUMBERS set
SUBSCRIPTIONTABLE.ACTIVEFLAG='Y'
where LOADED_MOBILE_NUMBERS.mobile_num=SUBSCRIPTIONTABLE.mobile_num
Anatoly.
From: | "ON(dot)KG" <skyer(at)on(dot)kg> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Trigger before insert |
Date: | 2004-11-25 11:37:40 |
Message-ID: | 9414883125.20041125143740@on.kg |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-performance |
Hi all,
===================================
CREATE FUNCTION trigger_test_func()
RETURNS trigger
AS '
DECLARE
cnt int4;
BEGIN
SELECT INTO cnt COUNT(*)
FROM table_test
WHERE ip = new.ip;
IF cnt > 50 THEN
-- THERE THE "INSERT" HAS TO BE STOPED
END IF;
RETURN new;
END;'
LANGUAGE 'plpgsql';
CREATE TRIGGER trigger_test
BEFORE INSERT
ON table_test
FOR EACH ROW
EXECUTE PROCEDURE trigger_test_func();
===================================
How could i stop Inserting record into table by some condition?
Thanx!
From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | "ON(dot)KG" <skyer(at)on(dot)kg> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Trigger before insert |
Date: | 2004-11-25 12:54:46 |
Message-ID: | 41A5D616.4090308@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-performance |
ON.KG wrote:
>
> How could i stop Inserting record into table by some condition?
RETURN null when using a before trigger. Or raise an exception to abort
the whole transaction.
--
Richard Huxton
Archonet Ltd
From: | "ON(dot)KG" <skyer(at)on(dot)kg> |
---|---|
To: | Richard Huxton <dev(at)archonet(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Trigger before insert |
Date: | 2004-11-25 15:02:40 |
Message-ID: | 11127183609.20041125180240@on.kg |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-performance |
Hi!
>> How could i stop Inserting record into table by some condition?
RH> RETURN null when using a before trigger. Or raise an exception to abort
RH> the whole transaction.
Thanx ;)
RETURN NULL works so as i need
From: | Jerome Macaranas <jerome(at)gmanmi(dot)tv> |
---|---|
To: | Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> |
Cc: | pgsql-performance(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org |
Subject: | Re: [PERFORM] HELP speed up my Postgres |
Date: | 2004-11-26 08:28:31 |
Message-ID: | 200411261628.31249.jerome@gmanmi.tv |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general pgsql-performance |
it did.. thanks.. generally a weeks process turned out to be less than a
day..
On Thursday 25 November 2004 15:06, Christopher Kings-Lynne wrote:
> > update SUBSCRIPTIONTABLE set ACTIVEFLAG='Y' where mobile_num in (select
> > mobile_num from LOADED_MOBILE_NUMBERS)
>
> Change to:
>
> update SUBSCRIPTIONTABLE set ACTIVEFLAG='Y' where exists (select 1 from
> LOADED_MOBILE_NUMBERS lmn where
> lmn.mobile_num=SUBSCRIPTIONTABLE.mobile_num);
>
> That should run a lot faster.
>
> Make sure you have indexes on both mobile_num columns.
>
> Chris
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
--
Jerome Macaranas
Systems/Network Administrator
GMA New Media, Inc.
Phone: (632) 9254627 loc 202
Fax: (632) 9284553
Mobile: (632) 918-9336819
jerome(at)gmanmi(dot)tv
Sanity is the playground for the unimaginative.
DISCLAIMER: This Message may contain confidential information intended only
for the use of the addressee named above. If you are not the intended
recipient of this message you are hereby notified that any use,
dissemination, distribution or reproduction of this message is prohibited. If
you received this message in error please notify your Mail Administrator and
delete this message immediately. Any views expressed in this message are
those of the individual sender and may not necessarily reflect the views of
GMA New Media, Inc.