which index approach is better in my case?

Question

In a table I have 4 columns:

(a(int),b(int),c(var-char),d(var-char))

I have two query as follows:

select *from table where a=1 and b=2 and c="abc";

select *from table where a=1 and b=2 and d="zyz";

Now Index approach:

• index on (a,b,c)
• index on (a,b,d)

OR

• index on(a,b)
• index on(c)
• index on(d)

Which one is better approach ?

Answer 1

The best is to try it. It's impossible to say which indexes are better unless you know what's the data you're planning to work with. The best is to create the table, insert some sampled data and then do a few typical queries with EXPLAIN or EXPLAIN ANALYZE.

An example would be

EXPLAIN select *from table where a=1 and b=2 and c="abc";

EXPLAIN select *from table where a=1 and b=2 and d="zyz";

or

EXPLAIN ANALYZE select *from table where a=1 and b=2 and c="abc";

EXPLAIN ANALYZE select *from table where a=1 and b=2 and d="zyz";

See as well http://www.postgresql.org/docs/9.5/static/sql-explain.html