Postgres Pro
Downloads
Home   >   mailing lists

Thread: HELP speed up my Postgres

HELP speed up my Postgres

From
JM
Date:
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,

Re: HELP speed up my Postgres

From
"Vishal Kashyap @ [SaiHertz]"
Date:
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

Re: HELP speed up my Postgres

From
JM
Date:
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

Re: HELP speed up my Postgres

From
Tom Lane
Date:
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

Re: HELP speed up my Postgres

From
Klint Gore
Date:
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             :                 :
+---------------------------------------+-----------------+

Re: HELP speed up my Postgres

From
Christopher Kings-Lynne
Date:
>         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

Re: [GENERAL] HELP speed up my Postgres

From
Jerome Macaranas
Date:
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.