Let's move on to partial indexes. We've got the same dev_award table:
CREATE TABLE dev_award ( id integer PRIMARY KEY, year integer, category varchar(32), first_name varchar(64), last_name varchar(64), company varchar(64), vote_count integer );
We've noticed that users almost always use a developer's last name to look for awards. And most of the time they are only interested in the category 'sql'. We've got quite a lot of rows in other categories that are almost never queried.
In several databases, including PostgreSQL, you can create a partial index, i.e., an index built on a subset of a table. Partial indexes take up less space than regular indexes. If you're sure that many of your database's rows are rarely queried, it makes sense to exclude them with a partial index.
In this case, we'd like to create an index on the column last_name, but only for the category 'sql'. (Remember, the other categories are almost never queried.) Take a look:
CREATE INDEX lname_sql ON dev_award (last_name) WHERE category = 'sql';
As you can see, we add a WHERE clause to specify which rows should be included in the index.
You're given the house table again:
CREATE TABLE house ( id integer PRIMARY KEY, state char(2), zip_code char(5), price decimal(10, 2), bedrooms integer, built_year integer );
The real estate agency discovered that the vast majority of searches are for houses at various prices in New York State. Create a partial index on the column price that will only include houses in New York state (state = 'NY').
