pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNatural sort order
6+ messages / 4 participants
[nested] [flat]
* Natural sort order
@ 2011-12-17 10:33 Richard Klingler <richard@klingler.net>
0 siblings, 2 replies; 6+ messages in thread
From: Richard Klingler @ 2011-12-17 10:33 UTC (permalink / raw)
To: pgsql-sql
Morning...
What is the fastest way to achieve natural ordering from queries?
I found a function at:
http://2kan.tumblr.com/post/361326656/postgres-natural-ordering
But it increases the query time from around 0.4msecs to 74msecs...
Might be not much if occasional queries are made..but I use it for
building
up a hierarchical tree menu in a web application where every msecs
counts (o;
cheers
richard
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Natural sort order
@ 2011-12-17 15:16 Filip Rembiałkowski <plk.zuber@gmail.com>
parent: Richard Klingler <richard@klingler.net>
1 sibling, 1 reply; 6+ messages in thread
From: Filip Rembiałkowski @ 2011-12-17 15:16 UTC (permalink / raw)
To: Richard Klingler <richard@klingler.net>; +Cc: pgsql-sql
If you use btrsort(column) from the example, you can just create a
functional index on this expression.
CREATE INDEX mytable_column_btrsort_idx ON mytable( btrsort(column) );
this can help.
2011/12/17 Richard Klingler <richard@klingler.net>:
> Morning...
>
> What is the fastest way to achieve natural ordering from queries?
>
> I found a function at:
> http://2kan.tumblr.com/post/361326656/postgres-natural-ordering
>
> But it increases the query time from around 0.4msecs to 74msecs...
> Might be not much if occasional queries are made..but I use it for
> building
> up a hierarchical tree menu in a web application where every msecs
> counts (o;
>
>
> cheers
> richard
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Natural sort order
@ 2012-02-28 12:38 Richard Klingler <richard@klingler.net>
parent: Filip Rembiałkowski <plk.zuber@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Klingler @ 2012-02-28 12:38 UTC (permalink / raw)
To: pgsql-sql
Took some time until I could try out this...
But as soon I want to create the fcuntion based index it tells me:
Error : ERROR: functions in index expression must be marked IMMUTABLE
Deleteing the sort function and recreating with the IMMUTABLE attribute gives the same error..
Here the functions:
CREATE FUNCTION btrsort_nextunit(text) RETURNS text AS $$
SELECT
CASE WHEN $1 ~ '^[^0-9]+' THEN
COALESCE( SUBSTR( $1, LENGTH(SUBSTRING($1 FROM '[^0-9]+'))+1 ), '' )
ELSE
COALESCE( SUBSTR( $1, LENGTH(SUBSTRING($1 FROM '[0-9]+'))+1 ), '' )
END
$$ LANGUAGE SQL
IMMUTABLE;
CREATE FUNCTION btrsort(text) RETURNS text AS $$
SELECT
CASE WHEN char_length($1)>0 THEN
CASE WHEN $1 ~ '^[^0-9]+' THEN
RPAD(SUBSTR(COALESCE(SUBSTRING($1 FROM '^[^0-9]+'), ''), 1, 12), 12, ' ') || btrsort(btrsort_nextunit($1))
ELSE
LPAD(SUBSTR(COALESCE(SUBSTRING($1 FROM '^[0-9]+'), ''), 1, 12), 12, ' ') || btrsort(btrsort_nextunit($1))
END
ELSE
$1
END
;
$$ LANGUAGE SQL
IMMUTABLE;
And the index creation:
create index port_name_btrsort_index on port(btrsort(name));
Which should speed up my query:
select * from port where name not like '%Z' order by btrsort(name) asc
cheers
richard
On Sat, 17 Dec 2011 16:16:07 +0100, Filip Rembiałkowski wrote:
> If you use btrsort(column) from the example, you can just create a
> functional index on this expression.
>
> CREATE INDEX mytable_column_btrsort_idx ON mytable( btrsort(column) );
>
> this can help.
>
>
>
>
>
> 2011/12/17 Richard Klingler <richard@klingler.net>:
>> Morning...
>>
>> What is the fastest way to achieve natural ordering from queries?
>>
>> I found a function at:
>> http://2kan.tumblr.com/post/361326656/postgres-natural-ordering
>>
>> But it increases the query time from around 0.4msecs to 74msecs...
>> Might be not much if occasional queries are made..but I use it for
>> building
>> up a hierarchical tree menu in a web application where every msecs
>> counts (o;
>>
>>
>> cheers
>> richard
>>
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Natural sort order
@ 2012-02-29 16:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Klingler <richard@klingler.net>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2012-02-29 16:08 UTC (permalink / raw)
To: Richard Klingler <richard@klingler.net>; +Cc: pgsql-sql
Richard Klingler <richard@klingler.net> writes:
> Took some time until I could try out this...
> But as soon I want to create the fcuntion based index it tells me:
> Error : ERROR: functions in index expression must be marked IMMUTABLE
FWIW, this example works fine for me. Maybe you have some weird
user-defined version of substr() or ~ that isn't immutable?
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Natural sort order
@ 2012-02-29 16:14 Richard Klingler <richard@klingler.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Richard Klingler @ 2012-02-29 16:14 UTC (permalink / raw)
To: pgsql-sql
Actually got it figured...for some reason it had the function twice (o;
But with or without function index the time to query stays the same...around 110msec for 24 results...
cheers
richard
On Wed, 29 Feb 2012 11:08:46 -0500, Tom Lane wrote:
> Richard Klingler <richard@klingler.net> writes:
>> Took some time until I could try out this...
>> But as soon I want to create the fcuntion based index it tells me:
>> Error : ERROR: functions in index expression must be marked IMMUTABLE
>
> FWIW, this example works fine for me. Maybe you have some weird
> user-defined version of substr() or ~ that isn't immutable?
>
> regards, tom lane
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Natural sort order
@ 2012-03-01 17:23 F. BROUARD / SQLpro <sqlpro@club-internet.fr>
parent: Richard Klingler <richard@klingler.net>
1 sibling, 0 replies; 6+ messages in thread
From: F. BROUARD / SQLpro @ 2012-03-01 17:23 UTC (permalink / raw)
To: pgsql-sql
The fastest way is to create a ref table with all possible entries,
ordered with an additionnal numerical column, indexing it and make a
join from your table to this ref table.
A +
Le 17/12/2011 11:33, Richard Klingler a écrit :
> Morning...
>
> What is the fastest way to achieve natural ordering from queries?
>
> I found a function at:
> http://2kan.tumblr.com/post/361326656/postgres-natural-ordering
>
> But it increases the query time from around 0.4msecs to 74msecs...
> Might be not much if occasional queries are made..but I use it for
> building
> up a hierarchical tree menu in a web application where every msecs
> counts (o;
>
>
> cheers
> richard
>
>
--
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2012-03-01 17:23 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-12-17 10:33 Natural sort order Richard Klingler <richard@klingler.net>
2011-12-17 15:16 ` Filip Rembiałkowski <plk.zuber@gmail.com>
2012-02-28 12:38 ` Richard Klingler <richard@klingler.net>
2012-02-29 16:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-02-29 16:14 ` Richard Klingler <richard@klingler.net>
2012-03-01 17:23 ` F. BROUARD / SQLpro <sqlpro@club-internet.fr>
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