PostgreSQL 17 introduced a new built-in locale provider that supports the C and C.UTF-8 locales without relying on the operating system's libc implementation. For many database workloads, locale selection is something that is decided during database creation and then rarely revisited. However, that single decision can influence the execution plans chosen by the optimizer. In workloads that perform frequent text comparisons, sorting, or prefix searches, the selected locale provider can determine whether postgres reads only a handful of index pages or scans an entire table.
In this blog, we will build two databases with identical schemas and identical data. One database uses the traditional libc locale provider with the en_IN.UTF-8 locale, while the other uses postgres built-in locale provider with the C.UTF-8 locale. After loading one million rows into both databases, we will execute the same sql statements against each database and compare their execution plans and execution times.
Now, create the database with utf8 as encoding and locale as en_IN.UTF-8.
CREATE DATABASE locale_libc
ENCODING 'UTF8'
LOCALE_PROVIDER libc
LOCALE 'en_IN.UTF-8'
TEMPLATE template0;
CREATE DATABASE
Create a database with the encoding utf8 and locale c.utf-8 like this.
CREATE DATABASE locale_builtin
ENCODING 'UTF8'
LOCALE_PROVIDER builtin
LOCALE 'C.UTF-8'
TEMPLATE template0;
CREATE DATABASE
Check the metadata of all databases
\l+
Result :
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges | Size | Tablespace | Description
----------------+----------+----------+-----------------+-------------+-------------+---------+-----------+-----------------------+---------+------------+------------------------------------
locale_builtin | postgres | UTF8 | builtin | C.UTF-8 | C.UTF-8 | C.UTF-8 | | | 7521 kB | pg_default |
locale_libc | postgres | UTF8 | libc | en_IN.UTF-8 | en_IN.UTF-8 | | | | 7521 kB | pg_default |
postgres | postgres | UTF8 | libc | en_IN | en_IN | | | | 1030 MB | pg_default |
template0 | postgres | UTF8 | libc | en_IN | en_IN | | | =c/postgres +| 7678 kB | pg_default | unmodifiable empty database
| | | | | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | libc | en_IN | en_IN | | | =c/postgres +| 7742 kB | pg_default | default template for new databases
| | | | | | | | postgres=CTc/postgres | | |
(5 rows)
Now, we can see each database metadata, like the database owner and their locales.
Connect to the locale_libc database
\c locale_libc
We can check the current database metadata from the postgres catalogue named pg_stat_database like this.
SELECT
current_database(),
datlocprovider,
datcollate,
datctype,
datlocale
FROM pg_database
WHERE datname=current_database();
Result :
current_database | datlocprovider | datcollate | datctype | datlocale
------------------+----------------+-------------+-------------+-----------
locale_libc | c | en_IN.UTF-8 | en_IN.UTF-8 |
(1 row)
Now, create test tables for checking the performance between these two locales.
CREATE TABLE people
(
id BIGSERIAL PRIMARY KEY,
name TEXT,
email TEXT,
city TEXT,
created_at TIMESTAMP DEFAULT now()
);
Insert nearly 1 million records.
INSERT INTO people(name,email,city)
SELECT
CASE
WHEN random()<0.10 THEN 'alice'||g
WHEN random()<0.20 THEN 'Aaron'||g
WHEN random()<0.30 THEN 'Álvaro'||g
WHEN random()<0.40 THEN 'André'||g
WHEN random()<0.50 THEN 'Bob'||g
WHEN random()<0.60 THEN 'carol'||g
WHEN random()<0.70 THEN 'Café'||g
WHEN random()<0.80 THEN 'Zoo'||g
WHEN random()<0.90 THEN 'zebra'||g
ELSE 'Özil'||g
END,
'user'||g||'@mail.com',
CASE
WHEN random()<0.25 THEN 'Delhi'
WHEN random()<0.50 THEN 'Mumbai'
WHEN random()<0.75 THEN 'Chennai'
ELSE 'Kochi'
END
FROM generate_series(1,1000000) g;
Now, create an index and execute analyze and vacuum for better statistics.
CREATE INDEX idx_people_name
ON people(name);
CREATE INDEX idx_people_city
ON people(city);
ANALYZE people;
VACUUM ANALYZE people;
Check the total size, index size, and table size like this.
SELECT
pg_size_pretty(pg_relation_size('people')) table_size,
pg_size_pretty(pg_indexes_size('people')) index_size,
pg_size_pretty(pg_total_relation_size('people')) total_size;
Result :
table_size | index_size | total_size
------------+------------+------------
82 MB | 58 MB | 140 MB
(1 row)
Now, take the backup from the locale_libc database and restore it to the locale_builtin database like this.
pg_dump -Fc locale_libc > /tmp/locale_libc.dump
pg_restore -d locale_builtin /tmp/locale_libc.dump
Now, connect to the locale_builtin database like this.
\c locale_builtin
Now, execute each of the below queries in these two databases and check their plans and their execution timings also.
EXPLAIN (ANALYZE,BUFFERS)
SELECT *
FROM people
ORDER BY name;
Query plan from the locale_builtin database
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_people_name on people (cost=0.42..71902.83 rows=1000000 width=53) (actual time=0.047..1455.510 rows=1000000.00 loops=1)
Index Searches: 1
Buffers: shared hit=246395 read=11526 written=4685
Planning:
Buffers: shared hit=93 read=3
Planning Time: 0.279 ms
Execution Time: 2656.457 ms
(7 rows)
Query plan from locale_libc database
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_people_name on people (cost=0.42..71909.68 rows=1000000 width=53) (actual time=0.053..1639.099 rows=1000000.00 loops=1)
Index Searches: 1
Buffers: shared hit=257764 read=158
Planning Time: 0.086 ms
Execution Time: 2954.326 ms
Result :
Both databases used an Index Scan on idx_people_name to return the rows in sorted order without an explicit sort. The built-in C.UTF-8 database completed the query slightly faster (2656.457 ms) than the libc database (2954.326 ms).
EXPLAIN (ANALYZE,BUFFERS)
SELECT *
FROM people
ORDER BY name
LIMIT 100;
Query plan from the locale_builtin database
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.42..7.62 rows=100 width=53) (actual time=0.029..0.477 rows=100.00 loops=1)
Buffers: shared hit=17
-> Index Scan using idx_people_name on people (cost=0.42..71902.83 rows=1000000 width=53) (actual time=0.025..0.192 rows=100.00 loops=1)
Index Searches: 1
Buffers: shared hit=17
Planning Time: 0.093 ms
Execution Time: 0.637 ms
(7 rows)
Query plan from locale_libc database
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.42..7.62 rows=100 width=53) (actual time=0.021..0.463 rows=100.00 loops=1)
Buffers: shared hit=17
-> Index Scan using idx_people_name on people (cost=0.42..71909.68 rows=1000000 width=53) (actual time=0.018..0.182 rows=100.00 loops=1)
Index Searches: 1
Buffers: shared hit=17
Planning Time: 0.095 ms
Execution Time: 0.616 ms
Result:
Both databases used an Index Scan with a LIMIT to fetch only the first 100 rows, avoiding a full table scan and sort. The execution time was nearly identical, with libc (0.616 ms) performing marginally faster than builtin C.UTF-8 (0.637 ms).
EXPLAIN (ANALYZE,BUFFERS)
SELECT *
FROM people
ORDER BY name
OFFSET 500000
LIMIT 1000;
Query plan from the locale_builtin database
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=35951.63..36023.53 rows=1000 width=53) (actual time=1513.253..1517.652 rows=1000.00 loops=1)
Buffers: shared hit=119995
-> Index Scan using idx_people_name on people (cost=0.42..71902.83 rows=1000000 width=53) (actual time=0.028..848.760 rows=501000.00 loops=1)
Index Searches: 1
Buffers: shared hit=119995
Planning Time: 0.115 ms
Execution Time: 1518.992 ms
(7 rows)
Query plan from locale_libc database
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=35955.05..36026.96 rows=1000 width=53) (actual time=1500.443..1504.801 rows=1000.00 loops=1)
Buffers: shared hit=121459
-> Index Scan using idx_people_name on people (cost=0.42..71909.68 rows=1000000 width=53) (actual time=0.022..836.287 rows=501000.00 loops=1)
Index Searches: 1
Buffers: shared hit=121459
Planning Time: 0.092 ms
Execution Time: 1506.134 ms
(7 rows)
Result:
Both databases used an Index Scan and scanned approximately 501,000 rows to satisfy the large OFFSET before returning the next 1,000 rows. The execution times were nearly identical, with libc (1506.134 ms) being slightly faster than builtin C.UTF-8 (1518.992 ms).
EXPLAIN (ANALYZE,BUFFERS)
SELECT
name,
COUNT(*)
FROM people
GROUP BY name;
Query plan from the locale_builtin database
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=0.42..45432.10 rows=1000000 width=20) (actual time=0.044..3883.659 rows=1000000.00 loops=1)
Group Key: name
Buffers: shared hit=3833
-> Index Only Scan using idx_people_name on people (cost=0.42..30432.10 rows=1000000 width=12) (actual time=0.031..1270.594 rows=1000000.00 loops=1)
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=3833
Planning:
Buffers: shared hit=7 read=2
Planning Time: 0.132 ms
Execution Time: 5099.127 ms
(11 rows)
Query plan from locale_libc database
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=0.42..45404.43 rows=1000000 width=20) (actual time=0.065..3848.993 rows=1000000.00 loops=1)
Group Key: name
Buffers: shared hit=3832 read=1
-> Index Only Scan using idx_people_name on people (cost=0.42..30404.42 rows=1000000 width=12) (actual time=0.042..1256.594 rows=1000000.00 loops=1)
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=3832 read=1
Planning:
Buffers: shared hit=3 read=6
Planning Time: 0.170 ms
Execution Time: 5045.487 ms
Result:
Both databases used a GroupAggregate with an Index Only Scan, allowing PostgreSQL to perform the grouping without accessing the table heap. The execution times were almost identical, with libc (5045.487 ms) being slightly faster than builtin C.UTF-8 (5099.127 ms).
EXPLAIN (ANALYZE,BUFFERS)
SELECT DISTINCT name
FROM people;
Query plan from the locale_builtin database
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=0.42..32932.10 rows=1000000 width=12) (actual time=0.044..3865.084 rows=1000000.00 loops=1)
Buffers: shared hit=3833
-> Index Only Scan using idx_people_name on people (cost=0.42..30432.10 rows=1000000 width=12) (actual time=0.038..1296.758 rows=1000000.00 loops=1)
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=3833
Planning Time: 0.108 ms
Execution Time: 5095.595 ms
(8 rows)
Query plan from locale_libc database
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=0.42..32904.43 rows=1000000 width=12) (actual time=0.029..3802.756 rows=1000000.00 loops=1)
Buffers: shared hit=3833
-> Index Only Scan using idx_people_name on people (cost=0.42..30404.42 rows=1000000 width=12) (actual time=0.024..1274.124 rows=1000000.00 loops=1)
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=3833
Planning Time: 0.088 ms
Execution Time: 5000.336 ms
(8 rows)
Result:
Both databases used a Unique operation with an Index Only Scan to eliminate duplicate values without accessing the table heap. The execution times were nearly identical, with libc (5000.336 ms) performing slightly faster than builtin C.UTF-8 (5095.595 ms).
EXPLAIN (ANALYZE,BUFFERS)
SELECT *
FROM people
WHERE name='alice100';
Query plan from the locale_builtin database
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_people_name on people (cost=0.42..8.44 rows=1 width=53) (actual time=0.019..0.021 rows=0.00 loops=1)
Index Cond: (name = 'alice100'::text)
Index Searches: 1
Buffers: shared hit=3
Planning Time: 0.085 ms
Execution Time: 0.042 ms
(6 rows)
Query plan from locale_libc database
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_people_name on people (cost=0.42..8.44 rows=1 width=53) (actual time=0.039..0.040 rows=0.00 loops=1)
Index Cond: (name = 'alice100'::text)
Index Searches: 1
Buffers: shared hit=2 read=1
Planning Time: 0.100 ms
Execution Time: 0.062 ms
(6 rows)
Result:
EXPLAIN (ANALYZE,BUFFERS)
SELECT *
FROM people
WHERE name>'alice100'
AND name<'alice900';
Query plan from the locale_builtin database
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on people (cost=2303.99..14165.70 rows=89714 width=53) (actual time=4.258..140.952 rows=89228.00 loops=1)
Recheck Cond: ((name > 'alice100'::text) AND (name < 'alice900'::text))
Heap Blocks: exact=9344
Buffers: shared hit=9689
-> Bitmap Index Scan on idx_people_name (cost=0.00..2281.57 rows=89714 width=0) (actual time=3.351..3.353 rows=89228.00 loops=1)
Index Cond: ((name > 'alice100'::text) AND (name < 'alice900'::text))
Index Searches: 1
Buffers: shared hit=345
Planning:
Buffers: shared hit=3
Planning Time: 0.150 ms
Execution Time: 259.398 ms
(12 rows)
Query plan from locale_libc database
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on people (cost=2310.91..14169.90 rows=89999 width=53) (actual time=4.390..142.802 rows=89228.00 loops=1)
Recheck Cond: ((name > 'alice100'::text) AND (name < 'alice900'::text))
Heap Blocks: exact=9344
Buffers: shared hit=9689
-> Bitmap Index Scan on idx_people_name (cost=0.00..2288.41 rows=89999 width=0) (actual time=3.496..3.498 rows=89228.00 loops=1)
Index Cond: ((name > 'alice100'::text) AND (name < 'alice900'::text))
Index Searches: 1
Buffers: shared hit=345
Planning:
Buffers: shared read=3
Planning Time: 0.183 ms
Execution Time: 261.799 ms
(12 rows)
Result:
Both databases used a Bitmap Index Scan followed by a Bitmap Heap Scan to efficiently retrieve the matching range of rows. The execution times were very similar, with builtin C.UTF-8 (259.398 ms) performing slightly faster than libc (261.799 ms).
EXPLAIN (ANALYZE,BUFFERS)
SELECT *
FROM people
WHERE name LIKE 'alice%';
Query plan from the locale_builtin database
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on people (cost=2313.68..13954.68 rows=101010 width=53) (actual time=4.753..161.496 rows=100384.00 loops=1)
Filter: (name ~~ 'alice%'::text)
Heap Blocks: exact=10509
Buffers: shared hit=10897
-> Bitmap Index Scan on idx_people_name (cost=0.00..2288.43 rows=90000 width=0) (actual time=3.779..3.781 rows=100384.00 loops=1)
Index Cond: ((name >= 'alice'::text) AND (name < 'alicf'::text))
Index Searches: 1
Buffers: shared hit=388
Planning:
Buffers: shared hit=17 read=4
Planning Time: 0.330 ms
Execution Time: 295.023 ms
(12 rows)
Query plan from locale_libc database
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Seq Scan on people (cost=0.00..23009.00 rows=111111 width=53) (actual time=0.016..184.304 rows=100384.00 loops=1)
Filter: (name ~~ 'alice%'::text)
Rows Removed by Filter: 899616
Buffers: shared hit=10509
Planning:
Buffers: shared hit=2 read=4
Planning Time: 0.235 ms
Execution Time: 319.104 ms
(8 rows)
Result:
The built-in C.UTF-8 database used a Bitmap Index Scan by converting the LIKE 'alice%' predicate into an index range, while the libc database performed a Sequential Scan. As a result, built-in C.UTF-8 (295.023 ms) executed the query faster than libc (319.104 ms).
EXPLAIN (ANALYZE,BUFFERS)
SELECT *
FROM people
WHERE name LIKE 'alice%'
ORDER BY name;
Query plan from the locale_builtin database
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=25805.20..26057.72 rows=101010 width=53) (actual time=321.515..458.140 rows=100384.00 loops=1)
Sort Key: name
Sort Method: external merge Disk: 6488kB
Buffers: shared hit=10900, temp read=811 written=813
-> Bitmap Heap Scan on people (cost=2313.68..13954.68 rows=101010 width=53) (actual time=5.221..164.965 rows=100384.00 loops=1)
Filter: (name ~~ 'alice%'::text)
Heap Blocks: exact=10509
Buffers: shared hit=10897
-> Bitmap Index Scan on idx_people_name (cost=0.00..2288.43 rows=90000 width=0) (actual time=4.224..4.225 rows=100384.00 loops=1)
Index Cond: ((name >= 'alice'::text) AND (name < 'alicf'::text))
Index Searches: 1
Buffers: shared hit=388
Planning Time: 0.150 ms
Execution Time: 593.299 ms
(14 rows)
Query plan from locale_libc database
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Gather Merge (cost=20304.97..33245.56 rows=111110 width=53) (actual time=179.612..440.610 rows=100384.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=10582 read=3
-> Sort (cost=19304.95..19420.69 rows=46296 width=53) (actual time=171.402..219.034 rows=33461.33 loops=3)
Sort Key: name
Sort Method: quicksort Memory: 3502kB
Buffers: shared hit=10582 read=3
Worker 0: Sort Method: quicksort Memory: 3058kB
Worker 1: Sort Method: quicksort Memory: 3059kB
-> Parallel Seq Scan on people (cost=0.00..15717.33 rows=46296 width=53) (actual time=0.018..58.990 rows=33461.33 loops=3)
Filter: (name ~~ 'alice%'::text)
Rows Removed by Filter: 299872
Buffers: shared hit=10509
Planning Time: 0.118 ms
Execution Time: 578.659 ms
(16 rows)
Result:
The built-in C.UTF-8 database used a Bitmap Index Scan followed by a sort, while the libc database used a Parallel Sequential Scan with Gather Merge. The execution times were very close, with libc (578.659 ms) being slightly faster than builtin C.UTF-8 (593.299 ms).
EXPLAIN (ANALYZE,BUFFERS)
SELECT MIN(name)
FROM people;
Query plan from the locale_builtin database
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.46..0.47 rows=1 width=32) (actual time=0.038..0.046 rows=1.00 loops=1)
Buffers: shared hit=4
InitPlan 1
-> Limit (cost=0.42..0.46 rows=1 width=12) (actual time=0.029..0.034 rows=1.00 loops=1)
Buffers: shared hit=4
-> Index Only Scan using idx_people_name on people (cost=0.42..32932.10 rows=1000000 width=12) (actual time=0.025..0.027 rows=1.00 loops=1)
Index Cond: (name IS NOT NULL)
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=4
Planning:
Buffers: shared hit=3
Planning Time: 0.113 ms
Execution Time: 0.072 ms
(14 rows)
Query plan from locale_libc database
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.46..0.47 rows=1 width=32) (actual time=0.030..0.038 rows=1.00 loops=1)
Buffers: shared hit=4
InitPlan 1
-> Limit (cost=0.42..0.46 rows=1 width=12) (actual time=0.020..0.025 rows=1.00 loops=1)
Buffers: shared hit=4
-> Index Only Scan using idx_people_name on people (cost=0.42..32904.43 rows=1000000 width=12) (actual time=0.015..0.017 rows=1.00 loops=1)
Index Cond: (name IS NOT NULL)
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=4
Planning:
Buffers: shared hit=3
Planning Time: 0.119 ms
Execution Time: 0.062 ms
Result:
Both databases used an Index Only Scan to retrieve the minimum value directly from the index without accessing the table heap. The execution times were nearly identical, with libc (0.062 ms) being slightly faster than builtin C.UTF-8 (0.072 ms).
EXPLAIN (ANALYZE,BUFFERS)
SELECT MAX(name)
FROM people;
Query plan from the locale_builtin database
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.46..0.47 rows=1 width=32) (actual time=0.062..0.073 rows=1.00 loops=1)
Buffers: shared hit=3 read=1
InitPlan 1
-> Limit (cost=0.42..0.46 rows=1 width=12) (actual time=0.052..0.059 rows=1.00 loops=1)
Buffers: shared hit=3 read=1
-> Index Only Scan Backward using idx_people_name on people (cost=0.42..32932.10 rows=1000000 width=12) (actual time=0.048..0.050 rows=1.00 loops=1)
Index Cond: (name IS NOT NULL)
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=3 read=1
Planning:
Buffers: shared hit=8
Planning Time: 0.134 ms
Execution Time: 0.099 ms
(14 rows)
Query plan from locale_libc database
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.46..0.47 rows=1 width=32) (actual time=0.073..0.085 rows=1.00 loops=1)
Buffers: shared hit=3 read=1
InitPlan 1
-> Limit (cost=0.42..0.46 rows=1 width=12) (actual time=0.062..0.070 rows=1.00 loops=1)
Buffers: shared hit=3 read=1
-> Index Only Scan Backward using idx_people_name on people (cost=0.42..32904.43 rows=1000000 width=12) (actual time=0.058..0.060 rows=1.00 loops=1)
Index Cond: (name IS NOT NULL)
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=3 read=1
Planning:
Buffers: shared hit=8
Planning Time: 0.172 ms
Execution Time: 0.120 ms
Result:
Both databases used a backward Index Only Scan to retrieve the maximum value directly from the index without accessing the table heap. The built-in C.UTF-8 database completed the query slightly faster (0.099 ms) than the libc database (0.120 ms).
EXPLAIN (ANALYZE,BUFFERS)
SELECT
name,
ROW_NUMBER()
OVER(ORDER BY name)
FROM people;
Query plan from the locale_builtin database
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
WindowAgg (cost=0.47..45432.10 rows=1000000 width=20) (actual time=0.035..3817.131 rows=1000000.00 loops=1)
Window: w1 AS (ORDER BY name ROWS UNBOUNDED PRECEDING)
Storage: Memory Maximum Storage: 17kB
Buffers: shared hit=3833
-> Index Only Scan using idx_people_name on people (cost=0.42..30432.10 rows=1000000 width=12) (actual time=0.017..1254.114 rows=1000000.00 loops=1)
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=3833
Planning Time: 0.102 ms
Execution Time: 5030.690 ms
(10 rows)
Query plan from locale_libc database
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
WindowAgg (cost=0.47..45404.43 rows=1000000 width=20) (actual time=0.025..3847.892 rows=1000000.00 loops=1)
Window: w1 AS (ORDER BY name ROWS UNBOUNDED PRECEDING)
Storage: Memory Maximum Storage: 17kB
Buffers: shared hit=3833
-> Index Only Scan using idx_people_name on people (cost=0.42..30404.42 rows=1000000 width=12) (actual time=0.015..1264.654 rows=1000000.00 loops=1)
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=3833
Planning Time: 0.097 ms
Execution Time: 5072.904 ms
Result:
Both databases used a WindowAgg with an Index Only Scan, allowing ROW_NUMBER() to be calculated directly from the index order without accessing the table heap. The execution times were nearly identical, with builtin C.UTF-8 (5030.690 ms) being slightly faster than libc (5072.904 ms).
EXPLAIN (ANALYZE,BUFFERS)
SELECT *
FROM people
Query plan from the locale_builtin database
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan Backward using idx_people_name on people (cost=0.42..71902.83 rows=1000000 width=53) (actual time=0.042..1551.056 rows=1000000.00 loops=1)
Index Searches: 1
Buffers: shared hit=257921
Planning Time: 0.111 ms
Execution Time: 2816.482 ms
(5 rows)
Query plan from locale_libc database
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan Backward using idx_people_name on people (cost=0.42..71909.68 rows=1000000 width=53) (actual time=0.024..1569.449 rows=1000000.00 loops=1)
Index Searches: 1
Buffers: shared hit=257922
Planning Time: 0.092 ms
Execution Time: 2840.586 ms
(5 rows)
Result:
Both databases used a backward Index Scan on idx_people_name to return the rows in descending order without an additional sort operation. The built-in C.UTF-8 database completed the query slightly faster (2816.482 ms) than the libc database (2840.586 ms).
SELECT *
FROM people
ORDER BY name DESC;
Query plan from the locale_builtin database
QUERY PLAN
--------------------------------------------------------------------------
Index Scan Backward using idx_people_name on people (cost=0.42..71909.68 rows=1000000 width=53) (actual time=0.021..1572.244 rows=1000000.00 loops=1)
Index Searches: 1
Buffers: shared hit=257922
Planning Time: 0.086 ms
Execution Time: 2847.783 ms
(5 rows)
Query plan from locale_libc database
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan Backward using idx_people_name on people (cost=0.42..71909.68 rows=1000000 width=53) (actual time=0.024..1569.449 rows=1000000.00 loops=1)
Index Searches: 1
Buffers: shared hit=257922
Planning Time: 0.092 ms
Execution Time: 2840.586 ms
(5 rows)
Both databases used a backward Index Scan on idx_people_name to return the rows in descending order. The execution times were nearly identical, with libc (2840.586 ms) performing marginally faster than builtin C.UTF-8 (2847.783 ms).
The test results for the database show that both locale providers work in a way for basic things like sorting and grouping data. They also work similarly for things like finding items using aggregate functions and searching for equal items. In these cases ppstgresql comes up with plans to get the data, and it takes almost the same amount of time to do it.
The big difference happened when we used a query that looked for things that start with a set of characters. The built-in C.UTF-8 locale allowed PostgreSQL to use an existing index to find the matching things. On the other hand, the libc locale had to look through all the data one by one. This shows that picking the locale provider can make a big difference in how well the database works for certain types of queries. So it is really important to think about the locale provider when you are creating a PostgreSQL database.