Most efficient way to hard-sort records - Mailing list pgsql-sql

Hello,

I have a table with cca 100,000 records. I need to hard-sort the records
by a key from second table. By hard-sorting I mean storing position of
each row in the main table. Here is what my tables look like:

main_table: id, name, position
key_table: id, main_table_id, key, value

Here is how I need to sort the records:
SELECT * FROM main_table
INNER JOIN key_table ON main_table.id = key_table.main_table_id
WHERE key = 'param'
ORDER BY value

I currently collect all ids from main_table in sorted order and then
update the position field for each row in the main_table one-by-one. Is
there a better/faster/more efficient solution?

Thank you for your suggestions.

--
Miroslav Šulc