pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
What's faster?
6+ messages / 4 participants
[nested] [flat]

* What's faster?
@ 2003-12-27 00:11  Keith Bottner <kbottner@comcast.net>
  0 siblings, 3 replies; 6+ messages in thread

From: Keith Bottner @ 2003-12-27 00:11 UTC (permalink / raw)
  To: pgsql-performance

I have a database where the vast majority of information that is related to
a customer never changes. However, there is a single field (i.e. balance)
that changes potentially tens to hundreds of times per day per customer
(customers ranging in the 1000s to 10000s). This information is not indexed.
Because Postgres requires VACUUM ANALYZE more frequently on updated tables,
should I break this single field out into its own table, and if so what kind
of a speed up can I expect to achieve. I would be appreciative of any
guidance offered.
 
BTW, currently using Postgres 7.3.4
 
 
Keith
 
 

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: What's faster?
@ 2003-12-27 00:49  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Keith Bottner <kbottner@comcast.net>
  2 siblings, 0 replies; 6+ messages in thread

From: Tom Lane @ 2003-12-27 00:49 UTC (permalink / raw)
  To: Keith Bottner <kbottner@comcast.net>; +Cc: pgsql-performance

"Keith Bottner" <kbottner@comcast.net> writes:
> I have a database where the vast majority of information that is related to
> a customer never changes. However, there is a single field (i.e. balance)
> that changes potentially tens to hundreds of times per day per customer
> (customers ranging in the 1000s to 10000s). This information is not indexed.
> Because Postgres requires VACUUM ANALYZE more frequently on updated tables,
> should I break this single field out into its own table,

Very likely a good idea, if the primary key that you'd need to add to
identify the balance is narrow.  Hard to say exactly how large the
benefit would be, but I'd think the update costs would be reduced
considerably.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: What's faster?
@ 2003-12-27 01:06  Mike Nolan <nolan@gw.tssi.com>
  parent: Keith Bottner <kbottner@comcast.net>
  2 siblings, 1 reply; 6+ messages in thread

From: Mike Nolan @ 2003-12-27 01:06 UTC (permalink / raw)
  To: kbottner@comcast.net; +Cc: pgsql-performance

> Because Postgres requires VACUUM ANALYZE more frequently on updated tables,
> should I break this single field out into its own table, and if so what kind
> of a speed up can I expect to achieve. I would be appreciative of any
> guidance offered.

Unless that field is part of the key, I wouldn't think that a vacuum 
analyze would be needed, as the key distribution isn't changing. 

I don't know if that is still true if that field is indexed.  Tom?

Even then, as I understand things vacuum analyze doesn't rebuild indexes, 
so I could see a need to drop and rebuild indexes on a regular basis,
even if you move that field into a separate table.  
--
Mike Nolan



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: What's faster?
@ 2003-12-27 04:00  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Mike Nolan <nolan@gw.tssi.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Tom Lane @ 2003-12-27 04:00 UTC (permalink / raw)
  To: Mike Nolan <nolan@gw.tssi.com>; +Cc: kbottner@comcast.net; pgsql-performance

Mike Nolan <nolan@gw.tssi.com> writes:
>> Because Postgres requires VACUUM ANALYZE more frequently on updated tables,
>> should I break this single field out into its own table, and if so what kind
>> of a speed up can I expect to achieve. I would be appreciative of any
>> guidance offered.

> Unless that field is part of the key, I wouldn't think that a vacuum 
> analyze would be needed, as the key distribution isn't changing. 

The "analyze" wouldn't matter ... but the "vacuum" would.  He needs to
get rid of the dead rows in a timely fashion.  The wider the rows, the
more disk space is at stake.

Also, if he has more than just a primary index on the main table,
the cost of updating the secondary indexes must be considered.
A balance-only table would presumably have just one index to update.

Against all this you have to weigh the cost of doing a join to get the
balance, so it's certainly not a no-brainer choice.  But I think it's
surely worth considering such a design.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: What's faster?
@ 2003-12-27 10:52  D'Arcy J.M. Cain <darcy@druid.net>
  parent: Keith Bottner <kbottner@comcast.net>
  2 siblings, 1 reply; 6+ messages in thread

From: D'Arcy J.M. Cain @ 2003-12-27 10:52 UTC (permalink / raw)
  To: Keith Bottner <kbottner@comcast.net>; pgsql-performance

On December 26, 2003 07:11 pm, Keith Bottner wrote:
> I have a database where the vast majority of information that is related to
> a customer never changes. However, there is a single field (i.e. balance)
> that changes potentially tens to hundreds of times per day per customer
> (customers ranging in the 1000s to 10000s). This information is not
> indexed. Because Postgres requires VACUUM ANALYZE more frequently on
> updated tables, should I break this single field out into its own table,
> and if so what kind of a speed up can I expect to achieve. I would be
> appreciative of any guidance offered.

We went through this recently.  One thing we found that may apply to you is 
how many fields in the client record have a foreign key constraint.  We find 
that tables with lots of FKeys are a lot more intensive on updates.  In our 
case it was another table, think of it as an order or header table with a 
balance, that has over 10 million records.  Sometimes we have 200,000 
transactions a day where we have to check the balance.  We eventually moved 
every field that could possibly be updated on a regular basis out to separate 
tables.  The improvement was dramatic.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: What's faster?
@ 2003-12-27 18:30  Tom Lane <tgl@sss.pgh.pa.us>
  parent: D'Arcy J.M. Cain <darcy@druid.net>
  0 siblings, 0 replies; 6+ messages in thread

From: Tom Lane @ 2003-12-27 18:30 UTC (permalink / raw)
  To: D'Arcy J.M. Cain <darcy@druid.net>; +Cc: Keith Bottner <kbottner@comcast.net>; pgsql-performance

"D'Arcy J.M. Cain" <darcy@druid.net> writes:
> We went through this recently.  One thing we found that may apply to you is 
> how many fields in the client record have a foreign key constraint.  We find 
> that tables with lots of FKeys are a lot more intensive on updates.

BTW, this should have gotten better in 7.3.4 and later --- there is
logic to skip checking an FKey reference if the referencing columns
didn't change during the update.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2003-12-27 18:30 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-27 00:11 What's faster? Keith Bottner <kbottner@comcast.net>
2003-12-27 00:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-12-27 01:06 ` Mike Nolan <nolan@gw.tssi.com>
2003-12-27 04:00   ` Tom Lane <tgl@sss.pgh.pa.us>
2003-12-27 10:52 ` D'Arcy J.M. Cain <darcy@druid.net>
2003-12-27 18:30   ` Tom Lane <tgl@sss.pgh.pa.us>

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