James PK's Technical Journal

[ Home | Journal ]

Wed, 04 Oct 2017

Indexing Lightning talk at Postgresql PG Day Conf UK 2016

Very informative day at PG Conf 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 support staff & developers, along the lines of "how can I speed up my queries" - a number of times the the DBA said after some analysis, he found himself saying.....

"...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:

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

Made with Pyblosxom