Create Index in Postgres

If your query is forcing the db to search through millions of rows to fetch data, indexing a table can help improve performance. Here’s how to do it on Postgres.

CREATE INDEX tablename_columnname_anothercolumn_index ON tablename (columnname, anothercolumn)

ELI5 Index

If you’re actually five you probably haven’t seen Library indexes, but it behaves the same way. Instead of searching through each and every book (row), with the index you’ll know the exact place of a book. In db terms, you’ll search through fewer rows.

Caveats

  • A DB server will cache indexes in memory so don’t create too many.
  • Indexes will effect insertion performance negatively.
  • Personally saw 2-300% performance gains but mileage may vary.

Look Up Current Indexes

select *
from pg_indexes
where tablename not like 'pg%';

Laravel

For Laravel users you can create indexes in your migrations

    public function up()

        {

            Schema::create('tasks', function (Blueprint $table) {

                $table->increments('id');

                $table->integer('user_id')->index();

                $table->string('name');

            $table->timestamps();

        });

    }

Leave a Comment