pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
HELP speed up my Postgres
9+ messages / 8 participants
[nested] [flat]

* HELP speed up my Postgres
@ 2004-11-25 06:00  JM <jerome@gmanmi.tv>
  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:12  Vishal Kashyap @ [SaiHertz] <vishalonlist@gmail.com>
  parent: 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:29  JM <jerome@gmanmi.tv>
  parent: Vishal Kashyap @ [SaiHertz] <vishalonlist@gmail.com>
  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:40  Iain <iain@mst.co.jp>
  parent: JM <jerome@gmanmi.tv>
  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:55  Tom Lane <tgl@sss.pgh.pa.us>
  parent: JM <jerome@gmanmi.tv>
  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 07:06  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: JM <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 07:08  Klint Gore <kg@kgb.une.edu.au>
  parent: JM <jerome@gmanmi.tv>
  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 09:21  Anatoly Okishev <rolly74@mail.ru>
  parent: JM <jerome@gmanmi.tv>
  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

* Re: [PERFORM] HELP speed up my Postgres
@ 2004-11-26 08:28  Jerome Macaranas <jerome@gmanmi.tv>
  parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  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


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