Having problems with a 25 million row table on 8.1.3

From: Tony Caduto <tony_caduto(at)amsoftwaredesign(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Having problems with a 25 million row table on 8.1.3
Date: 2006-04-25 20:10:32
Message-ID: 444E8238.10603@amsoftwaredesign.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,
I have a client who has a 25 million row table that is used to keep
track of financial security info.
So far it has worked great, but today someone wanted to get all the tax
codes(there are lot's of dupes) from the table.
So we tried this:

select DISTINCT tax_code from warehouse.sec_trans
We let this run for 1/2 hour or so and canceled it.

Then I tried select DISTINCT ON (tax_code) tax_code from warehouse.sec_trans

same deal, had to cancel it.

The server has a mirrored raid setup on two drives(yes I know this is
not a good setup, but it's what they have) with 2GB of ram.

I have the kernels (Linux CentOS 4.3) shared memory size set to:
kernel.shmmax = 262144000

Here is the postgresql.conf entries for memory that have been changed:

# - Memory -

shared_buffers = 15000 # min 16 or max_connections*2,
8KB each
#temp_buffers = 1000 # min 100, 8KB each
#max_prepared_transactions = 5 # can be 0 or more
# note: increasing max_prepared_transactions costs ~600 bytes of shared
memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
work_mem = 10240 # min 64, size in KB
maintenance_work_mem = 32768 # min 1024, size in KB
#max_stack_depth = 2048 # min 100, size in KB

Anyone have any ideas on how to get all the unique tax codes from this
table?

Thanks in advance :-)

Tony

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2006-04-25 20:15:54 Re: Having problems with a 25 million row table on 8.1.3
Previous Message Don Walker 2006-04-25 19:45:09 Please comment on the following OpenFTS/tsearch2 issues!