From: Craig James <craig_james@emolecules.com>
To: cyber-postgres@midnightfantasy.com
Cc: pgsql-performance@postgresql.org
Subject: Re: 121+ million record table perf problems
Date: Fri, 18 May 2007 15:33:08 -0700
Message-ID: <464E29A4.4050309@emolecules.com> (raw)
In-Reply-To: <Pine.LNX.4.64.0705181215160.23563@cerberus.midnightfantasy.com>
References: <Pine.LNX.4.64.0705181215160.23563@cerberus.midnightfantasy.com>
>
> I've got a table with ~121 million records in it. Select count on it
> currently takes ~45 minutes, and an update to the table to set a value
> on one of the columns I finally killed after it ran 17 hours and had
> still not completed. Queries into the table are butt slow, and
>
> The update query that started this all I had to kill after 17hours.
> It should have updated all 121+ million records. That brought my
> select count down to 19 minutes, but still a far cry from acceptable.
If you have a column that needs to be updated often for all rows,
separate it into a different table, and create a view that joins it back
to the main table so that your application still sees the old schema.
This will greatly speed your update since (in Postgres) and update is
the same as a delete+insert. By updating that one column, you're
re-writing your entire 121 million rows. If you separate it, you're
only rewriting that one column. Don't forget to vacuum/analyze and
reindex when you're done.
Better yet, if you can stand a short down time, you can drop indexes on
that column, truncate, then do 121 million inserts, and finally
reindex. That will be MUCH faster.
Craig
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: craig_james@emolecules.com, cyber-postgres@midnightfantasy.com
Subject: Re: 121+ million record table perf problems
In-Reply-To: <464E29A4.4050309@emolecules.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