# Abbreviated keys for numeric to accelerate numeric sorts

DevFeed: [Abbreviated keys for numeric to accelerate numeric sorts](<https://devfeed.tech/articles/abbreviated-keys-for-numeric-to-accelerate-numeric-sorts-33651.md>)

Original publisher: [Read original article](<https://pgeoghegan.blogspot.com/2015/04/abbreviated-keys-for-numeric-to.html>)

Author: Peter Geoghegan (noreply@blogger.com)

Published: 2015-04-04T16:19:00Z

Content type: article

Language: en

Sources: [Peter Geoghegan's blog](<https://devfeed.tech/sources/peter-geoghegan-s-blog.md>)

Topics: [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [Sorting](<https://devfeed.tech/topics/sorting.md>), [Development](<https://devfeed.tech/topics/development.md>)

Tags: [abbreviation](<https://devfeed.tech/tags/abbreviation.md>), [benchmarks](<https://devfeed.tech/tags/benchmarks.md>), [c](<https://devfeed.tech/tags/c.md>), [cardinality](<https://devfeed.tech/tags/cardinality.md>), [commit](<https://devfeed.tech/tags/commit.md>), [count](<https://devfeed.tech/tags/count.md>), [fast](<https://devfeed.tech/tags/fast.md>), [improvements](<https://devfeed.tech/tags/improvements.md>), [indexes](<https://devfeed.tech/tags/indexes.md>), [internals](<https://devfeed.tech/tags/internals.md>), [maintenance](<https://devfeed.tech/tags/maintenance.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [sorting](<https://devfeed.tech/tags/sorting.md>), [table](<https://devfeed.tech/tags/table.md>)

## AI overview

The article discusses PostgreSQL numeric abbreviated keys, a committed patch intended to accelerate numeric sorting. It reports 6x-7x improvements for representative in-memory queries and a 12x improvement in a PostgreSQL 9.5 text-column CREATE INDEX benchmark compared with PostgreSQL 9.4.

## Source excerpt

Andrew Gierth's numeric abbreviated keys patch was committed recently. This commit added abbreviation/sortsupport for the numeric type (the PostgreSQL type which allows practically arbitrary precision, typically recommended for representing monetary values). The encoding scheme that Andrew came up with is rather clever - it has an excellent tendency to concentrate entropy from the original values into the generated abbreviated keys in real world cases. As far as accelerating sorts goes, numeric abbreviation is at least as effective as the original text abbreviation scheme. I easily saw improvements of 6x-7x with representative queries that did not spill to disk (i.e. that used quicksort). In essence, the patch makes sorting numeric values almost as cheap as sorting simple integers, since that is often all that is actually required during sorting proper (the abbreviated keys compare as integers, except that the comparison is inverted to comport with how abbreviation builds abbreviated values from numerics as tuples are copied into local memory ahead of sorting - see the patch for exact details). Separately, over lunch at pgConf.US in New York, Corey Huinker complained about a slow, routine data warehousing CREATE INDEX operation that took far too long. The indexes in question were built on a single text column. I suggested that Corey check out how PostgreSQL 9.5 performs, where this operation is accelerated by text abbreviation, often very effectively. Corey chose an organic set of data that could be taken as a reasonable proxy for how PostgreSQL behaves when he performs these routine index builds. In all cases maintenance_work_mem was set to 64MB, meaning that an external tapesort is always required - those details were consistent. This was a table with 18 million rows. Apparently, on PostgreSQL 9.4, without abbreviation, the CREATE INDEX took 10 minutes and 19 seconds in total. On PostgreSQL 9.5, with identical settings, it took only 51.3 seconds - a 12x improvemen