pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rosser Schwarz <rschwarz@totalcardinc.com>
To: 'Tom Lane' <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: atrocious update performance
Date: Tue, 16 Mar 2004 13:58:47 -0600
Message-ID: <001e01c40b91$181d66c0$2500fa0a@CardServices.TCI.com> (raw)
In-Reply-To: <26148.1079460286@sss.pgh.pa.us>

while you weren't looking, Tom Lane wrote:

> ...slow FK checks could be your problem if the application is set
> up to issue multiple UPDATEs affecting the same row(s) during a
> single transaction.  I'm not clear on whether that applies to you
> or not.

It shouldn't.  It's just one large batch update that should be hitting
every row serially.

> And anyway the bottom line is: have you got indexes on the columns
> *referencing* account.cust.custid?

No.  I'd've sworn I had one on account.acct.custid, since that table
is popupated (currently ~500K rows), but it's not.

$ time psql tci -c "explain analyze select * from account.acct where
custid = 257458"
                              QUERY PLAN
-----------------------------------------------------------------------
 Seq Scan on acct  (cost=0.00..7166.68 rows=2 width=71) (actual
 time=1047.122..1047.122 rows=0 loops=1)
   Filter: (custid = 257458)
 Total runtime: 1047.362 ms
(3 rows)


real    0m1.083s
user    0m0.010s
sys     0m0.000s

If it is looking up the custid in account.acct for each row, that's,
say, 1 seconds per lookup, for 4.7 million lookups, for, if my math
is right (4,731,410 / 3600 / 24) 54 days.  I suppose that tracks, but
that doesn't make sense, given what you said about the fk checks,
above.

Of course, if I index the column and amend the query to say "where
custid = 194752::bigint" I get back much saner numbers:

                              QUERY PLAN
----------------------------------------------------------------------
 Index Scan using ix_fk_acct_custid on acct  (cost=0.00..3.34 rows=2
 width=71) (actual time=0.126..0.141 rows=2 loops=1)
   Index Cond: (custid = 194752::bigint)
 Total runtime: 0.314 ms
(3 rows)


real    0m0.036s
user    0m0.010s
sys     0m0.000s

Which would still take just under two days.

$ time psql tci -c "explain analyze update account.cust set prodid =
tempprod.prodid, subprodid = tempprod.subprodid where origid =
tempprod.debtid"

But if I'm not touching the column referenced from account.acct, why
would it be looking there at all?  I've got an explain analyze of the
update running now, but until it finishes, I can't say for certain
what it's doing.  explain, alone, says:

$ time psql tci -c "explain update account.cust set prodid =
tempprod.prodid, subprodid = tempprod.subprodid where origid =
tempprod.debtid;"
                              QUERY PLAN
---------------------------------------------------------------------
 Merge Join  (cost=0.00..232764.69 rows=4731410 width=252)
   Merge Cond: (("outer".origid)::text = ("inner".debtid)::text)
   ->  Index Scan using ix_origid on cust  (cost=0.00..94876.83 
       rows=4731410 width=236)
   ->  Index Scan using ix_debtid on tempprod  (cost=0.00..66916.71
       rows=4731410 width=26)
(4 rows)


real    0m26.965s
user    0m0.010s
sys     0m0.000s

which shows it not hitting account.acct at all.  (And why did it take
the planner 20-some seconds to come up with that query plan?)

tempprod doesn't have an index either, but then it doesn't reference
account.cust; instead, the update would be done by joining the two on
debtid/origid, which map one-to-one, are both indexed, and with both
tables clustered on those indices--exactly as was the CREATE TABLE AS
Aaron suggested elsethread.

Unfortunately, this isn't the only large update we'll have to do.  We
receive a daily, ~100K rows file that may have new values for any field
of any row in account.cust, .acct or sundry other tables.  The process
of updating from that file is time-critical; it must run in minutes, at
the outside.

/rls

--
Rosser Schwarz
Total Card, Inc.




view thread (39+ messages)  latest in thread

Message-ID: <001e01c40b91$181d66c0$2500fa0a@CardServices.TCI.com>
Permalink:  ../001e01c40b91$181d66c0$2500fa0a@CardServices.TCI.com/
Also on:    postgresql.org/message-id/001e01c40b91$181d66c0$2500fa0a@CardServices.TCI.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: rschwarz@totalcardinc.com, tgl@sss.pgh.pa.us
  Subject: Re: atrocious update performance
  In-Reply-To: <001e01c40b91$181d66c0$2500fa0a@CardServices.TCI.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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