CODEWITHSUNDEEP
sqlpostgresqlindexingdatabase-indexes
Pmsmm
Pmsmm

Reputation: 440

Which Index to use

I have two queries that are used frequently so an Index is something I should really use, after seing all indexes I still can't decide which is better for the following queries:

select distinct F.nif, F.nome
from fornecedor F, produto P
where F.nif = P.forn_primario
and P.categoria = 'Frutos'

select ean, count(nif)
from produto P, fornece_sec F
where P.ean = F.ean
group by P.ean

Since the first querie is multicolumn I think it should be a B-tree but then again in the documentation they don't mention columns from different tables.

Upvotes: 0

Views: 93

Answers (2)

Ruslan Tolkachev
Ruslan Tolkachev

Reputation: 644

The way you do it, when you are not sure, you test it on potential candidates (hopefully in your dev environment). So you create it with b-tree then run 

explain analyse select ...

and make a matrix with your results 

b-tree(select whatever)     hash (select whatever)
time 0.0001 ms              time 9999 ms
plan ...                    plan ...

and this is how you are going to know exactly the one you need, because we don't know the size of your table, selectivity of your columns and lot's of other stuff

Probably btree on your join and predicates though :)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

For this query (note: written using correct modern syntax):

select distinct F.nif, F.nome
from fornecedor F join
     produto P
     on F.nif = P.forn_primario
where P.categoria = 'Frutos';

You probably want an index on produto(categoria, forn_primario) and fornecedor(nif, nome). I would question whether you really need the select distinct and with more information might suggest another version of the query.

For this query (note: correctly written to avoid syntax errors):

select P.ean, count(F.nif)
from produto P join
     fornece_sec F
     on P.ean = F.ean
group by P.ean;

You want indexes on ean in each table. I would suggest that you use count(*) instead of count(nif), unless you specifically want to count non-NULL values of that column.

Upvotes: 1

Related Questions