CODEWITHSUNDEEP
databasepostgresql
stmax
stmax

Reputation: 6605

Is there a way to tell how postgresql uses an index?

I have a table with an index:

create index on foo (a, b, c);

When searching for a and b Postgres can use the index to quickly look up the rows:

test=# explain analyze select a from foo where a = 3 and b = 4;
 Index Only Scan using foo_a_b_c_idx on foo  (cost=0.43..486.83 rows=120 width=4) (actual time=0.141..23.981 rows=59049 loops=1)
   Index Cond: ((a = 3) AND (b = 4))
   Heap Fetches: 59049
 Total runtime: 25.894 ms

While searching for b and c is much slower, since it has to linearly scan the whole index (or table):

test=# explain analyze select a from foo where b = 4 and c = 5;
 Index Only Scan using foo_a_b_c_idx on foo  (cost=0.43..121987.32 rows=120 width=4) (actual time=7.377..159.793 rows=59049 loops=1)
   Index Cond: ((b = 4) AND (c = 5))
   Heap Fetches: 59049
 Total runtime: 160.735 ms

However the query plans look equal in both cases (both are called "Index Only Scan" with some "Index Condition").. Is it possible to tell if access is possible in logarithmic or linear time (without having to look at each index definition)?

Other database systems are much more explicit about how they are using an index. In MS SQL the first query would be an "Index Seek" (fast) while the second one would be an "Index Scan" (slow). In Sqlite the first one would be a "SEARCH TABLE foo USING COVERING INDEX" (fast) while the second one would be a "SCAN TABLE foo USING COVERING INDEX" (slow).

Upvotes: 3

Views: 1640

Answers (1)

Denis de Bernardy
Denis de Bernardy

Reputation: 78561

Apart from the more detailed query plan options highlighted by @horse, the answer is: no. There is no hint or way to know besides having a basic understanding of how indexes work (and knowing your own schema, of course).

Upvotes: 3

Related Questions