Re: Most efficient way to hard-sort records - Mailing list pgsql-sql
From | PFC |
---|---|
Subject | Re: Most efficient way to hard-sort records |
Date | |
Msg-id | op.s8590fficigqcu@apollo13 Whole thread Raw |
In response to | Re: Most efficient way to hard-sort records ("Ben K." <bkim@coe.tamu.edu>) |
Responses |
Re: Most efficient way to hard-sort records
|
List | pgsql-sql |
Is it possible to do this : CREATE TABLE sorted (order_no SERIAL PRIMARY KEY, other columns...) INSERT INTO sorted (columns) SELECT * FROM main_table INNER JOIN key_table ON main_table.id = key_table.main_table_id WHERE key = 'param' ORDER BY value SELECT The SERIAL will automatically generate the order_no you want, which corresponds to the position in the sorted set. Then, to get the records in-order : SELECT * FROM sorted ORDER BY order_no As the records have been inserted in-order in the "sorted" table, this table is, in fact, clustered, so a full table scan using the index on "order_no" will be very fast.Of course this is only interesting if this data is quite static, because you'll have to re-generate the table when the data changes. There is another solution : CREATE INDEX on key_table( key, value ) Now, the index can optimize ordering by (key,value), which is equivalent to ordering by value if key = constant. A bit of query manipulation might get you what you want ; I suppose all rows in "key_table" reference a row in "main_table" ; so it is faster to sort (and limit) first on key_table, then grab the rows from main_table : SELECT k.value, m.* FROM key_table k LEFT JOIN main_table m ON m.id=k.main_table_id WHERE k.key='param' ORDER BY k.key, k.value If key_table REFERENCES main_table, LEFT JOIN is equivalent to INNER JOIN ; however if the planner is smart enough, it might notice that it can index-scan key_table in key,value order, grabbing rows from main_table in order and skip the sort entirely. On Sun, 07 May 2006 08:53:46 +0200, Ben K. <bkim@coe.tamu.edu> wrote: >> 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? > > > A cheap solution if you don't care about the position value as long as > sort order is ok. > > 1) > # SELECT main_table.id into temp_table FROM main_table INNER JOIN > key_table ON main_table.id = key_table.main_table_id ORDER BY value; > > 2) > # update main_table set position = (select oid from temp_table where id > = main_table.id ); > > I guess I'll get a set of consecutive oids by this. > > You can make the number begin at arbitrary number, by > > 2-a) > # update main_table set position = ( (select oid::int4 from temp_table > where id = main_table.id ) - (select min(oid::int4) from temp_table) > + 1) ; > > I read that oid wraps around (after ~ billions) so you might want to > check your current oid. > > > > > Regards, > > Ben K. > Developer > http://benix.tamu.edu > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings