Problem z ideksami

Mam taką tabele

CREATE TABLE rachunki
(
  id_rach int8 NOT NULL,
  typ_oper int8,
  id_dostawca int8,
  id_odbiorca int8,
  numer_rach varchar(20),
  ...
  data_r_rach date,
  ...
  CONSTRAINT klucz PRIMARY KEY (id_rach, id_sklep)
) 
WITHOUT OIDS;
ALTER TABLE rachunki OWNER TO centrala;
REVOKE ALL ON TABLE rachunki FROM postgres;
GRANT ALL ON TABLE rachunki TO public;

Tabela zawiera 600.000 rekordów Zindexowaniu podanne zostały id_rach i data_r_rach

Zapytanie 1: czas wykonania : 172 sec. (bez EXPLAIN)

EXPLAIN SELECT id_rach,r.numer_rach,  data_r_rach
FROM rachunki r 
WHERE r.id_rach=71943;

Zapytanie zwraca: Seq Scan on rachunki r (cost=0.00..77571.36 rows=2 width=30) Filter: (id_rach = 71943)

Zapytanie 2: czas wykonania : 0,006 sec. (bez EXPLAIN)

Zapytanie zwraca: Index Scan using "rachunki.data_r_rach" on rachunki r (cost=0.00..1768.72 rows=495 width=30) Index Cond: (data_r_rach = '2005-01-01'::date)

Moja diagnoza:

id_rach nie szuka po indexach tylko tak jak by nie bylo indexów (Sprawdzałem 1000 razy id_rach jest z indexowany).

Proźba

Jak wymusić zapytanie żeby szukało po indexach?

Chyba że gdzieś indziej popełniłem błąd, to proszę o pomoc

Tak się fajnie składa, że w naszym systemie kiedyś też mieliśmy taki problem ;)

Rozwiązaniem są... apostrofy:

EXPLAIN SELECT id_rach,r.numer_rach, data_r_rach
FROM rachunki r 
WHERE r.id_rach='71943';

Zabójcze rozwiązanie, co? Kto nie jest informatykiem, ten traci połowę fajniejszej strony życia :D [rotfl]

Jeśli nie możesz dodać '', to możesz rzutować: int8(71943). Ewentualnie możesz kolumnę id_rach zrobić int4, wtedy wszystko ładnie działa jak powinno.

Dostanę marchewkę? :D

MAM NOWY PROBLEM :(

SELECT p.id_sklep,sum(ilosc),sum(netto_zak)
FROM poz_rach p
WHERE (p.id_rach='43088' and p.id_sklep='7') and p.vat_tow!=''
GROUP BY 1;

Index Cond: (id_sklep = 7::smallint) Filter: ((id_rach = 43088::bigint) AND (vat_tow <> ''::bpchar))

Czemu id_rach nie jest szukane wg indexow ??

Jeśli wymusze zeby szukal wg id_rach to nie ma sprawy ale niestety w tedy id_sklep nie jest szukany wg indexow

Bo to zależy od tego co masz w bazie. Nie zawsze jest tak, że korzystanie z indeksu przyspiesza. Optimizer może zignorować indeks, jeśli z analizy kosztów wyjdzie mu, że np. filtrowanie jest szybsze.

Inna sprawa, że złe indeksy założyłeś. Nie można wyszukiwać za pomocą 2 indeksów jednokolumnowych na raz (w jednym wyrażeniu). Musisz mieć do tego indeks dwukolumnowy. Np. tak:

create index idx_poz_rach_id_rach_id_sklep on
poz_rach using btree(id_rach, id_sklep);

Oczywiscie nadal nie oznacza to, że system musi tego indeksu użyć w całości, zwłaszcza jeśli wyszukiwanie po jednej kolumnie od razu spowoduje znaczne ograniczenie liczby rekordów tak, że będzie ich tylko kilka. Wtedy koszt odwołania się do drugiej części indeksu może być większy niż po prostu przefiltrowanie tych kilku rekordów.