pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHELP speed up my Postgres
9+ messages / 8 participants
[nested] [flat]
* HELP speed up my Postgres
@ 2004-11-25 06:00 JM <jerome@gmanmi.tv>
2004-11-25 06:12 ` Re: [PERFORM] HELP speed up my Postgres Vishal Kashyap @ [SaiHertz] <vishalonlist@gmail.com>
2004-11-25 06:40 ` Re: [PERFORM] HELP speed up my Postgres Iain <iain@mst.co.jp>
2004-11-25 07:06 ` Re: [PERFORM] HELP speed up my Postgres Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-11-25 07:08 ` Re: [PERFORM] HELP speed up my Postgres Klint Gore <kg@kgb.une.edu.au>
2004-11-25 09:21 ` Re: HELP speed up my Postgres Anatoly Okishev <rolly74@mail.ru>
0 siblings, 5 replies; 9+ messages in thread
From: JM @ 2004-11-25 06:00 UTC (permalink / raw)
To: pgsql-performance; +Cc: pgsql-general@postgresql.org
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,
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [PERFORM] HELP speed up my Postgres
2004-11-25 06:00 HELP speed up my Postgres JM <jerome@gmanmi.tv>
@ 2004-11-25 06:12 ` Vishal Kashyap @ [SaiHertz] <vishalonlist@gmail.com>
2004-11-25 06:29 ` Re: [PERFORM] HELP speed up my Postgres JM <jerome@gmanmi.tv>
4 siblings, 1 reply; 9+ messages in thread
From: Vishal Kashyap @ [SaiHertz] @ 2004-11-25 06:12 UTC (permalink / raw)
To: jerome@gmanmi.tv; +Cc: pgsql-performance; pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [PERFORM] HELP speed up my Postgres
2004-11-25 06:00 HELP speed up my Postgres JM <jerome@gmanmi.tv>
2004-11-25 06:12 ` Re: [PERFORM] HELP speed up my Postgres Vishal Kashyap @ [SaiHertz] <vishalonlist@gmail.com>
@ 2004-11-25 06:29 ` JM <jerome@gmanmi.tv>
2004-11-25 06:55 ` Re: [PERFORM] HELP speed up my Postgres Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: JM @ 2004-11-25 06:29 UTC (permalink / raw)
To: Vishal Kashyap @ [SaiHertz] <vishalonlist@gmail.com>; +Cc: pgsql-performance; pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [PERFORM] HELP speed up my Postgres
2004-11-25 06:00 HELP speed up my Postgres JM <jerome@gmanmi.tv>
2004-11-25 06:12 ` Re: [PERFORM] HELP speed up my Postgres Vishal Kashyap @ [SaiHertz] <vishalonlist@gmail.com>
2004-11-25 06:29 ` Re: [PERFORM] HELP speed up my Postgres JM <jerome@gmanmi.tv>
@ 2004-11-25 06:55 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2004-11-25 06:55 UTC (permalink / raw)
To: jerome@gmanmi.tv; +Cc: Vishal Kashyap @ [SaiHertz] <vishalonlist@gmail.com>; pgsql-performance; pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [PERFORM] HELP speed up my Postgres
2004-11-25 06:00 HELP speed up my Postgres JM <jerome@gmanmi.tv>
@ 2004-11-25 06:40 ` Iain <iain@mst.co.jp>
4 siblings, 0 replies; 9+ messages in thread
From: Iain @ 2004-11-25 06:40 UTC (permalink / raw)
To: jerome@gmanmi.tv; +Cc: pgsql-general@postgresql.org
> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [PERFORM] HELP speed up my Postgres
2004-11-25 06:00 HELP speed up my Postgres JM <jerome@gmanmi.tv>
@ 2004-11-25 07:06 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-11-26 08:28 ` Re: [PERFORM] HELP speed up my Postgres Jerome Macaranas <jerome@gmanmi.tv>
4 siblings, 1 reply; 9+ messages in thread
From: Christopher Kings-Lynne @ 2004-11-25 07:06 UTC (permalink / raw)
To: jerome@gmanmi.tv; +Cc: pgsql-performance; pgsql-general@postgresql.org
> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [PERFORM] HELP speed up my Postgres
2004-11-25 06:00 HELP speed up my Postgres JM <jerome@gmanmi.tv>
2004-11-25 07:06 ` Re: [PERFORM] HELP speed up my Postgres Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2004-11-26 08:28 ` Jerome Macaranas <jerome@gmanmi.tv>
0 siblings, 0 replies; 9+ messages in thread
From: Jerome Macaranas @ 2004-11-26 08:28 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: pgsql-performance; pgsql-general@postgresql.org
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [PERFORM] HELP speed up my Postgres
2004-11-25 06:00 HELP speed up my Postgres JM <jerome@gmanmi.tv>
@ 2004-11-25 07:08 ` Klint Gore <kg@kgb.une.edu.au>
4 siblings, 0 replies; 9+ messages in thread
From: Klint Gore @ 2004-11-25 07:08 UTC (permalink / raw)
To: jerome@gmanmi.tv; +Cc: pgsql-general@postgresql.org; pgsql-performance
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 : :
+---------------------------------------+-----------------+
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: HELP speed up my Postgres
2004-11-25 06:00 HELP speed up my Postgres JM <jerome@gmanmi.tv>
@ 2004-11-25 09:21 ` Anatoly Okishev <rolly74@mail.ru>
4 siblings, 0 replies; 9+ messages in thread
From: Anatoly Okishev @ 2004-11-25 09:21 UTC (permalink / raw)
To: jerome@gmanmi.tv; +Cc: pgsql-general <pgsql-general@postgresql.org>
> 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.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2004-11-26 08:28 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-25 06:00 HELP speed up my Postgres JM <jerome@gmanmi.tv>
2004-11-25 06:12 ` Vishal Kashyap @ [SaiHertz] <vishalonlist@gmail.com>
2004-11-25 06:29 ` JM <jerome@gmanmi.tv>
2004-11-25 06:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-11-25 06:40 ` Iain <iain@mst.co.jp>
2004-11-25 07:06 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-11-26 08:28 ` Jerome Macaranas <jerome@gmanmi.tv>
2004-11-25 07:08 ` Klint Gore <kg@kgb.une.edu.au>
2004-11-25 09:21 ` Anatoly Okishev <rolly74@mail.ru>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox