Postgresql Index case-sensitive

Witam!

Mam kolumny company_id oraz name. Stworzylem index:

CREATE INDEX ON my_table (company_id, (name));

Zauwazylem, ze index ten nie jest uzywty w zapytaniu:

SELECT *
FROM my_table 
WHERE company_id = 888
   AND name = 'name';

Jakies pomysly?

A jak znalazłeś, że nie jest używany? Ile masz danych w tabeli? Mogło sie okazać, że full scan na tabeli jest szybszy niż wczytywanie indeksu.

@poniatowski: Zastanawialem sie tylko nad inedxem company id, name i lower(name). Wiem, ze niby taki z lower() jest wydajniejszy. Ale ja musze sprawdzac wielkosc liter takze nawet jak jest szybszy to nie moge go uzyc. Dzieki.

Nie wiem czy zrozumiałem Twój przypadek, ale co z query, w którym dostęp będzie po indeksie case insensitive + filtrowanie case sensitive?

Na zasadzie:

select * from foo where 
id=:id and lower(name)=lower(:name)  -- dostęp po indeksie
and name=:name; -- filtrowanie

create table foo (
  id integer,
  name text
 );
 
 insert into foo 
   select x,'ABC'  from generate_series(1,10000) x;
   
 insert into foo 
   select x,'AbC'  from generate_series(1,10000) x;
 
 create index ix_foo_id_lower_name on foo (id, lower(name));
                                                     
explain select * from foo where id=5 and lower(name)=lower('ABC') and name='ABC';
                                     

@yarel Przeanalizowalem twoj przyklad. U mnie kolumna name to nazwa kodu rabatowego. I wydaje mi sie, ze ten kod bedzie wrazliwy na wielkie litery. Kod10Procent nie rowna sie KOD10PROCENT. Czekam na potwierdzenie od gory.

@poniatowski: No i Co Ci zwraca postgres dla tych dwóch zapytań?

select * from foo where lower(name)=lower('KOD10PROCENT')  and name='KOD10PROCENT';
select * from foo where lower(name)=lower('Kod10Procent')  and name='Kod10Procent';

Te dwa zapytania zwracaja odpowiednie kody. Tylko index jest wykorzystany na warunki wylacznie z lower. Nie rozumiem dlaczego dolaczasz jeszcze name=bleble? Przeciez to nie jest wyszukiwanie przy uzyciu indexu.

edit OK, kumam juz po co jest name. On wyszukuje juz tylko z dwoch ostatnich krotek.

Ja musze po prostu stworzyc index ktory rozroznia wielkosc liter. Dlatego nie uzylem lower czy upper.

@poniatowski:

1. lower(name)=lower('Kod10Procent') - tu używany jest indeks funkcyjny, silnik wybiera pasujące rekordy po indeksie, ignorując przy tym wielkość liter
2. and name='Kod10Procent' - tu silnik filtruje wybrane indeksem rekordy i ten filtr jest już case sensitive

Nie znam Twojego pełnego case, więc przykład jedynie pokazuje, że można połączyć indeks case insensitive z filtrem case sensitive.

@poniatowski:

Danych jest tylko kilka krotek. Z 20 :D

Tu masz odpowiedź. Masz za mało danych by DB stwierdziła, że opłaca się w ogóle używać indeksu. Przegląd sekwencyjny będzie wg DB szybszy więc używa tego, musisz skonfigurować seq_page_cost oraz random_page_cost, ale nawet wtedy indeks może być nie użyty, bo masz tak mało danych.