From: PFC <lists@peufeu.com>
To: Ben K. <bkim@coe.tamu.edu>
To: Miroslav Šulc <miroslav.sulc@startnet.cz>
Cc: pgsql-sql@postgresql.org
Subject: Re: Most efficient way to hard-sort records
Date: Sun, 07 May 2006 10:32:29 +0200
Message-ID: <op.s8590fficigqcu@apollo13> (raw)
In-Reply-To: <Pine.GSO.4.64.0605061507500.7602@coe.tamu.edu>
References: <445C5A94.4020907@startnet.cz>
<Pine.GSO.4.64.0605061507500.7602@coe.tamu.edu>
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
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-sql@postgresql.org
Cc: lists@peufeu.com, bkim@coe.tamu.edu, miroslav.sulc@startnet.cz
Subject: Re: Most efficient way to hard-sort records
In-Reply-To: <op.s8590fficigqcu@apollo13>
* 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