Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2010
    Posts
    40

    Unanswered: Creating indexes

    Dear all,
    I would like to accelerated the queries to a postgresql database and as I have been told the best way to do it is to index.
    I have searched through internet and I have found various even contradictory tips and therefore I would like to ask:
    1) Is indexing a guarantee for faster database search?
    2) If I have compound id should index also be compound
    3) If some columns are queried more and some other more often should I index them all together or group them and then index them or to index each column separately?
    What is the best practice?
    Thanks

  2. #2
    Join Date
    Mar 2011
    Posts
    27
    1 - no
    2 - sometimes
    3 - maybe

    It entirely depends on what queries you have. First realise that adding indexes will slow down inserts/updates/deletes and consume more disk space.

    For starters, I would make sure you have primary key indexes (created automatically if you specified the primary constraint), and foreign key indexes if suitable for your schema. Then, understand how your tables will be queried - the decision to use single column or compound column indexes will depend on the queries. PostgreSQL can use multiple single column indexes on a table but it is more efficient to use a compound column if every column is used in a where clause.

    There is a lot of documentation out there (eg Table of Contents | SQL Performance Explained | Use The Index, Luke!), but maybe the best way to figure it out is to try something, use explain analyze to see what is working and what is not, and try again.

  3. #3
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by someidiot View Post
    make sure you have primary key indexes (created automatically if you specified the primary constraint), and foreign key indexes if suitable for your schema.
    Does this mean you always put indexes on foreign key columns? I generally don't, but then again maybe I just haven't worked with large enough datasets where slow JOINs are an issue. Or is that what you mean by "suitable for your schema"?

  4. #4
    Join Date
    Mar 2011
    Posts
    27
    Quote Originally Posted by futurity View Post
    Does this mean you always put indexes on foreign key columns? I generally don't, but then again maybe I just haven't worked with large enough datasets where slow JOINs are an issue. Or is that what you mean by "suitable for your schema"?
    Oh no sorry, I miss-spoke. I meant foreign key constraints with indexes on the parent table's keys. Of course if your queries could benefit from indexes on the child table foreign keys I'd add them.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •