Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

select version();

version

PostgreSQL 13.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit

SELECT 1

create table  test_spl_char ( char_nm text);
insert into test_spl_char ( char_nm)
     values ('YUM') 
          , ('CANDY')
          , ('.GEMS')
          , ('DONUT')            
          , ('_SHAKE')
          , ('BISCUIT');

CREATE TABLE

INSERT 0 6

-- what you currently have      
select * 
  from test_spl_char
  order by char_nm  COLLATE "C";

char_nm

.GEMS

BISCUIT

CANDY

DONUT

YUM

_SHAKE

SELECT 6

-- what you currently have  but with internal value of 1st character 
-- NOTE sorted properly by internal value 
select char_nm, ascii(substr(char_nm,1,1)) "Internal Code"
  from test_spl_char
  order by char_nm  COLLATE "C";

char_nm
Internal Code

.GEMS
46

BISCUIT
66

CANDY
67

DONUT
68

YUM
89

_SHAKE
95

SELECT 6

-- regexp then actual value
select * 
  from test_spl_char 
  order by char_nm ~ '^[A-Za-z0-9]' 
         , char_nm ; 

char_nm

.GEMS

_SHAKE

BISCUIT

CANDY

DONUT

YUM

SELECT 6

-- Sort True vs False
select  false < true "false < true" , false::integer  "False", true::integer "True"

false < true
False
True

t
0
1

SELECT 1