Documentation for node.js sequelize index lookup

Question

I'm having trouble finding information about how to look up records by an index using sequelize/postgres for node.js.

The only documentation of indexes appears to be here: http://sequelizejs.com/documentation#migrations-functions

To illustrate what I'm asking, let's take a simple model where there is a person there are Persons, Projects, and Tasks. Each person references a number of assigned tasks, and each project has a number of assigned tasks. Each task has a back-reference to the project and person. We'll assume that each person only has one task per project.

Let's say I have a person and project, and I need to find if there is a task associated. I've tried implementing this through an index on task of person/project.

I've found through searches that you can also create indexes through the slightly unintuitive syntax:

        global.db.sequelize.getQueryInterface().addIndex('Tasks',
        ['ProjectId', 'PersonId'],
        {
            indexName: 'IndexName',
            indicesType: 'UNIQUE'
        }

This seems to work, and the index is created. However, I can't find a reference anywhere in the docs or even on the internet about how to use this index to find the task.

Any suggestions?

Answer 1

You have a fundamental misunderstanding of how a RDBMS is supposed to work.

It is supposed to pick the best indexes for each query based upon the pattern of database access required. This is performed by the "planner" in the RDBMS.

Some terms you will find useful to search against as you use PostgreSQL: - Primary Key - Foreign Key - Constraint (both the above are these) - EXPLAIN ANALYSE (or ANALYZE depending on your dialect of English) - http://explain.depesz.com/ - a useful site to colour the above explains - pg_dump / pg_restore - make sure you can use these tools to backup your database

Finally, make yourself a good hot cup of tea or coffee and sit down and at least skim through the PostgreSQL manuals. At least it will give you an idea of where to find further information.

Good Luck!