Re: 121+ million record table perf problems

From: Vivek Khera <vivek(at)khera(dot)org>
To: PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: 121+ million record table perf problems
Date: 2007-05-21 21:24:09
Message-ID: FC775E8C-28AE-40FE-99D1-E909A013C1A7@khera.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


On May 18, 2007, at 2:30 PM, Andrew Sullivan wrote:

> Note also that your approach of updating all 121 million records in
> one statement is approximately the worst way to do this in Postgres,
> because it creates 121 million dead tuples on your table. (You've
> created some number of those by killing the query as well.)
>
> All of that said, 17 hours seems kinda long.

I don't think that is too long. Growing the table one page at a time
takes a long time when you add a lot of pages to a table that big.
Add in the single disk and you're flying the disk head all over the
place so it will just be slow. No way around it.

And just for good measure, I ran a count on one of my big tables
which consists of two integers and a varchar(7):

db=> select count(*) from mytable;
count
-----------
311994721
(1 row)

Time: 157689.057 ms

So I'm going to bet $1 that you're I/O starved.

Also, for memory usage, postgres won't use more than you tell it to...

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2007-05-21 21:32:05 Re: Rewriting DISTINCT and losing performance
Previous Message Jim C. Nasby 2007-05-21 21:05:22 Re: Postgres Benchmark Results