Are custom content types a good way to model the classic entity relationship model? I have several content types which will store thousands of records each one.
Those content types will be related to each other as in a classic 1..N or N..M relationships I would like to query those content types taking this relationship into account.

I have searched for this on d.o but I am puzzled with the big quantity of modules available.
For instance is entity and entity reference any good for this ?

What is the best way to model the classic entity-relationship model of the database systems ?
Also, will I be able to tune the database (mainly create new indexes) to increase the performance of the queries ? I am asking this because I will need to tune the database because of the big amount of data, so this is something to take into account at the time of considering different approaches to the modeling decision.
In Drupal 7, you'll want the Entity module as it extends the core Entity API to do lots of useful things, and a lot of contributed modules depend on it.

To create relationships between entities (a "node reference" relationship, if you will) you'll want the Entity Reference module. Another more radical approach is the Relation module which provides two-way relationships – this is in early development stages and is a bit more complicated. I would recommend the Entity Reference module, as even though the initial relationship is 1-way, it's simple enough to find the referencing-entity on a referenced-entity when needed.

Regarding your question about tuning the database – the relationships are not setup as foreign keys or anything like that, as Drupal handles your database constraints in code and not within the database schema itself. That's a whole other conversation. The tables that contain fields for a specific node type, including the table that includes a reference to another node ID, are just joined with the node table on "entity_id". There are pros and cons to this – if you're a traditional database admin type, this may seem problematic, but it's the job of the CMS to maintain integrity in this model. I don't think you'll find ways to tune the database via new indexes, although you could certainly try. Thousands of nodes is not a lot of data and you should see Drupal 7 – in a properly tuned environment, with queries and views that do not contain extraneous joins – performs quite speedily. 

My 2 cents.