Small Queries Really Fast, Large Queries Really Slow...

Hi again!

I have finally got my Ubuntu VirtualBox VM running PostgreSQL with PL/Python
and am now looking at performance.

So here's the scenario:

We have a great big table:

cse=# \d nlpg.match_data
Table "nlpg.match_data"
Column | Type |
Modifiers
-------------------+----------+--------------------------------------------------------------------------
premise_id | integer |
usrn | bigint |
org | text |
sao | text |
level | text |
pao | text |
name | text |
street | text |
town | text |
postcode | text |
match_data_id | integer | not null default
nextval('nlpg.match_data_match_data_id_seq1'::regclass)
addr_str | text |
tssearch_name | tsvector |
tssearch_street | tsvector |
tssearch_addr_str | tsvector |
Indexes:
"match_data_pkey1" PRIMARY KEY, btree (match_data_id)
"index_match_data_mid" btree (match_data_id)
"index_match_data_pid" btree (premise_id)
"index_match_data_tssearch_addr_str" gin (tssearch_addr_str)
"index_match_data_tssearch_name" gin (tssearch_name)
"index_match_data_tssearch_street" gin (tssearch_street)
"index_match_data_usrn" btree (usrn)

KEY NOTE:
nlpg.match_data has approximately 27,000,000 rows..

Running this query:

EXPLAIN ANALYZE UPDATE nlpg.match_data SET org = org WHERE match_data_id <
1000000;

I get this:

"Index Scan using match_data_pkey1 on match_data (cost=0.00..1452207.14
rows=1913756 width=302) (actual time=23.448..61559.652 rows=999999 loops=1)"
" Index Cond: (match_data_id < 1000000)"
"Total runtime: 403855.675 ms"

I copied a chunk of the table like this:

CREATE TABLE nlpg.md_copy AS SELECT * FROM nlpg.match_data WHERE
match_data_id < 1000000;

Then ran the same query on the smaller copy table:

EXPLAIN ANALYZE UPDATE nlpg.md_copy SET org = org WHERE match_data_id <
1000000;

And get this:

"Seq Scan on md_copy (cost=0.00..96935.99 rows=999899 width=301) (actual
time=26.745..33944.923 rows=999999 loops=1)"
" Filter: (match_data_id < 1000000)"
"Total runtime: 57169.419 ms"

As you can see this is much faster per row with the smaller table chunk. I
then tried running the same first query with 10 times the number of rows:

EXPLAIN ANALYZE UPDATE nlpg.match_data SET org = org WHERE match_data_id <
10000000;

This takes a massive amount of time (still running) and is definitely a
non-linear increase in the run time in comparison with the previous query.

EXPLAIN UPDATE nlpg.match_data SET org = org WHERE match_data_id < 10000000;
"Seq Scan on match_data (cost=0.00..3980053.11 rows=19172782 width=302)"
" Filter: (match_data_id < 10000000)"

Any suggestions on what I can do to speed things up? I presume if I turn off
Sequential Scan then it might default to Index Scan.. Is there anything
else?

Cheers,
Tom