Skip site navigation (1) Skip section navigation (2)

indexes on multiple columns

From: Lewis Bergman <lbergman(at)abi(dot)tconline(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: indexes on multiple columns
Date: 2002-02-22 20:59:02
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
I have read the manual on indexes but i am still in the dark as to how to 
construct these indexes so that I can use the fewest indexes but still have 
my query make use of them. An example follows.

A table, item, has these columns among others:
id		serial	pk
name		varchar
description	text
automated	boolean
available		boolean
class		varchar	fk
subclass		varchar	fk

Most of the time searches will take place soley based on the id:
SELECT id FROM item WHERE id='123456';
But on occasion I might want to see info relating to a specific item and 
its availability:
SELECT id FROM item WHERE name='access' AND available='true';
or just
SELECT id FROM item WHERE available='true';

If I make an index on both the name and available columns, will both the 
second and third query use it?

Lewis Bergman
Texas Communications
4309 Maple St.
Abilene, TX 79602-8044
915-695-6962 ext 115


pgsql-novice by date

Next:From: john-paul delaneyDate: 2002-02-22 21:11:42
Subject: Re: Nu-B\Column:Alter type
Previous:From: john-paul delaneyDate: 2002-02-22 19:43:35
Subject: Re: Nu-B\psql:Command Recall,Repeat?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group