James PK's Technical Journal

Very informative day at PG Day UK in London on 5th July with many interesting talks.

A particular lightning talk from a database administrator (DBA) said he had a few queries from colleagues doing support & development, along the lines of "how can I speed up my queries" - a number of times the DBA said after some analysis, he found himself responding...

"...I looked at the tables in question, you should...create index MY_INDEX on MY_TABLE(MY_COLUMN)...ok?". The speaker then said something like - "I've said that so often I feel like having a t-shirt made with that on"

The \d meta command should show existing indexes:

basic_example=# \d employee
       Table "public.employee"
  Column  |     Type      | Modifiers 
----------+---------------+-----------
 name     | character(20) | 
 dept     | character(20) | 
 jobtitle | character(20) | 
Indexes:
    "dept_index" btree (dept)

Obvious caveats for "creating indexes will increase performance" are:

• A general understanding of Indexes helps before adding more
• Decide what to index, which might require obtaining metrics, is important
• Performance issues might be caused somethings else. Using EXPLAIN on queries might be a useful starting point.

posted at: 00:00 | path: /postgresql | permanent link to this entry