Optimized Postgresql like and group by clause

Question

• Database: PostgresSQL PostgreSQL 12.11 (Ubuntu 12.11-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
• RAM : 8 GB
• Processor : i7 4510U (dual core 2 Ghz)

I would to like to optimized below query

select  a.gender from "employees" as a 
where  lower( gender ) LIKE 'f%'  group by gender
limit 20

Total number of records in table : 2,088,290 rows

Index

CREATE INDEX ix_employees_gender_lower ON public.employees USING btree (lower((gender)::text) varchar_pattern_ops)

query execution plan

https://explain.dalibo.com/plan/h7e

Please use gdrive link to download and restore the sql to database for above query SQL TABLE with data

I tried to index but unavail also i am not able to understand explain analyze so any pointers on the same as well

Answer 1

It sounds like you need an index skip-scan. PostgreSQL currently doesn't implement those automatically but you can emulate it with a recursive CTE. People are working on adding this to the planner so it will be chosen automatically, but even if they succeed it would probably not work with your case-folding LIKE condition. I couldn't see how to integrate the case-folding LIKE condition into the recursive CTE, but if you return all distinct genders preserving case, you can then filter that small list quickly without needing to use an index.

WITH RECURSIVE t AS (
   SELECT min(gender) AS gender FROM employees
   UNION ALL
   SELECT (SELECT min(gender) FROM employees WHERE gender > t.gender)
   FROM t WHERE t.gender IS NOT NULL
   )
SELECT gender FROM t WHERE gender IS NOT NULL and lower(gender) like 'f%';

This took less than 2 ms for me, but it does require you add a plain index on gender, which you don't seem to have already.