# abbreviation Published articles for abbreviation. This is one page of public article previews, not the complete archive. Follow Next page to continue. Summaries are not the original full articles. ## Abbreviated keys for numeric to accelerate numeric sorts DevFeed: [Abbreviated keys for numeric to accelerate numeric sorts]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2015-04-04T16:19:00Z Content type: article Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [Sorting](), [Development]() Tags: [abbreviation](), [benchmarks](), [c](), [cardinality](), [commit](), [count](), [fast](), [improvements](), [indexes](), [internals](), [maintenance](), [postgresql](), [sorting](), [table]() ### 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 ## Abbreviated keys: exploiting locality to improve PostgreSQL's text sort performance DevFeed: [Abbreviated keys: exploiting locality to improve PostgreSQL's text sort performance]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2015-01-24T00:43:00Z Content type: article Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [Sorting](), [Databases](), [SQL]() Tags: [abbreviation](), [c](), [index](), [internals](), [performance](), [postgresql](), [sorting](), [sql]() ### AI overview The article explains PostgreSQL's abbreviated keys patch, which improves text sorting performance by exploiting locality. It reports that, in realistic cases, text-based CREATE INDEX operations in PostgreSQL 9.5 are over three times faster than in PostgreSQL 9.4, while noting that the improvement varies by workload. ### Source excerpt On Monday, Robert Haas committed a patch of mine that considerably speeds up the sorting of text in PostgreSQL. This was the last and the largest in a series of such patches, the patch that adds "abbreviated keys". PostgreSQL 9.5 will have big improvements in sort performance. In realistic cases, CREATE INDEX operations on text are over 3 times faster than in PostgreSQL 9.4. Not every such utility operation, or data warehousing query involving a big sort is sped up by that much, but many will be. This was a piece of work that I spent a considerable amount of time on over the past few months. It's easy to justify that effort, though: sorting text is a very fundamental capability of any database system. Sorting is likely the dominant cost when creating B-Tree indexes, performing CLUSTER operations, and, most obviously, for sort nodes that are required by many plans that are executed in the service of queries with ORDER BY or DISTINCT clauses, or aggregates using the GroupAggregate strategy. Most of the utility statements that need to perform sorts must perform them with a very disruptive lock on the target relation (CREATE INDEX CONCURRENTLY is a notable exception), so quite apart from the expense of the sort, the duration of sorts often strongly influences how long a production system is seriously disrupted. My interest in sorting is not new: I first worked on it in 2011. Early research on it back then prompted Robert Haas and Tom Lane to write the SortSupport infrastructure, which I've now extended here. Originally, the SortSupport infrastructure was all about providing alternative versions of comparators for use in sort routines, versions that avoided certain overhead otherwise inherent to calling functions that are generally accessible from SQL. As a highly extensible system, PostgreSQL requires that sort behavior be defined in terms of a default B-Tree operator class, which is itself defined in terms of SQL operators with underlying SQL-callable functions. These