Thread: HELP speed up my Postgres
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,
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
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
> 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
JM <jerome@gmanmi.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
On Thu, 25 Nov 2004 14:00:32 +0800, JM <jerome@gmanmi.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@kgb.une.edu.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 : : +---------------------------------------+-----------------+
> 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
> 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.
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!
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
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
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@gmanmi.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.