Czy są one równoważne? Jeśli nie to dlaczego?
Indeks (identyfikator_użytkownika1, identyfikator_użytkownika2) i indeks (identyfikator_użytkownika2, identyfikator_użytkownika1)
Nie są one równoważne i ogólnie mówiąc indeks (bar, baz) nie będzie skuteczny w przypadku zapytań formularza select * from foo where baz=?
Erwin wykazał, że takie indeksy rzeczywiście mogą przyspieszyć zapytanie, ale ten efekt jest ograniczony i nie ma takiej samej kolejności, jak zwykle oczekuje się, że indeks poprawi wyszukiwanie - opiera się na fakcie, że „pełne skanowanie” indeksu jest często szybsze niż „pełne skanowanie” indeksowanej tabeli ze względu na dodatkowe kolumny w tabeli, które nie pojawiają się w indeksie.
Podsumowanie: indeksy mogą pomagać w zapytaniach nawet w kolumnach nie prowadzących, ale na jeden z dwóch drugorzędnych i względnie drobnych sposobów, a nie w dramatyczny sposób, zwykle oczekuje się, że indeks pomoże z powodu jego struktury btree
nb dwa sposoby, w jakie indeks może pomóc, jeśli pełne skanowanie indeksu jest znacznie tańsze niż pełne skanowanie tabeli i: 1. wyszukiwanie tabel jest tanie (ponieważ jest ich niewiele lub są one zgrupowane), lub 2. Indeks obejmuje, więc nie ma wyszukiwania tabel we wszystkich przypadkach , patrz komentarze Erwins tutaj
testbed:
create table foo(bar integer not null, baz integer not null, qux text not null);
insert into foo(bar, baz, qux)
select random()*100, random()*100, 'some random text '||g from generate_series(1,10000) g;
zapytanie 1 (bez indeksu, trafienie w 74 bufory ):
explain (buffers, analyze, verbose) select max(qux) from foo where baz=0;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Aggregate (cost=181.41..181.42 rows=1 width=32) (actual time=3.301..3.302 rows=1 loops=1)
Output: max(qux)
Buffers: shared hit=74
-> Seq Scan on stack.foo (cost=0.00..181.30 rows=43 width=32) (actual time=0.043..3.228 rows=52 loops=1)
Output: bar, baz, qux
Filter: (foo.baz = 0)
Buffers: shared hit=74
Total runtime: 3.335 ms
zapytanie 2 (z indeksem - optymalizator ignoruje indeks - ponownie uderza 74 bufory ):
create index bar_baz on foo(bar, baz);
explain (buffers, analyze, verbose) select max(qux) from foo where baz=0;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Aggregate (cost=199.12..199.13 rows=1 width=32) (actual time=3.277..3.277 rows=1 loops=1)
Output: max(qux)
Buffers: shared hit=74
-> Seq Scan on stack.foo (cost=0.00..199.00 rows=50 width=32) (actual time=0.043..3.210 rows=52 loops=1)
Output: bar, baz, qux
Filter: (foo.baz = 0)
Buffers: shared hit=74
Total runtime: 3.311 ms
zapytanie 2 (z indeksem - i oszukujemy optymalizatora, aby go użył):
explain (buffers, analyze, verbose) select max(qux) from foo where bar>-1000 and baz=0;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=115.56..115.57 rows=1 width=32) (actual time=1.495..1.495 rows=1 loops=1)
Output: max(qux)
Buffers: shared hit=36 read=30
-> Bitmap Heap Scan on stack.foo (cost=73.59..115.52 rows=17 width=32) (actual time=1.370..1.428 rows=52 loops=1)
Output: bar, baz, qux
Recheck Cond: ((foo.bar > (-1000)) AND (foo.baz = 0))
Buffers: shared hit=36 read=30
-> Bitmap Index Scan on bar_baz (cost=0.00..73.58 rows=17 width=0) (actual time=1.356..1.356 rows=52 loops=1)
Index Cond: ((foo.bar > (-1000)) AND (foo.baz = 0))
Buffers: shared read=30
Total runtime: 1.535 ms
Zatem dostęp za pośrednictwem indeksu jest w tym przypadku dwukrotnie szybszy niż w przypadku 30 buforów - co pod względem indeksowania jest „nieco szybsze” !, i YMMV w zależności od względnego rozmiaru tabeli i indeksu, wraz z liczbą przefiltrowanych wierszy i cech klastrowania danych w tabeli
Natomiast zapytania w kolumnie wiodącej wykorzystują strukturę btree indeksu - w tym przypadku uderzając w 2 bufory :
explain (buffers, analyze, verbose) select max(qux) from foo where bar=0;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=75.70..75.71 rows=1 width=32) (actual time=0.172..0.173 rows=1 loops=1)
Output: max(qux)
Buffers: shared hit=38
-> Bitmap Heap Scan on stack.foo (cost=4.64..75.57 rows=50 width=32) (actual time=0.036..0.097 rows=59 loops=1)
Output: bar, baz, qux
Recheck Cond: (foo.bar = 0)
Buffers: shared hit=38
-> Bitmap Index Scan on bar_baz (cost=0.00..4.63 rows=50 width=0) (actual time=0.024..0.024 rows=59 loops=1)
Index Cond: (foo.bar = 0)
Buffers: shared hit=2
Total runtime: 0.209 ms