D
D
danker2014-11-12 17:35:45
PostgreSQL
danker, 2014-11-12 17:35:45

How to correctly arrange indexes in PostgreSQL (movie database)?

Guys please help with the request.
I decided to transfer the project to PostgreSQL (9.4). But with this base while on "you". I create several indexes, but in one query the index is used and in the other it is not.
Below is a selection, tell me which fields should I put an index on?
genres and countries like how should the gin index be?

  1. SELECT title FROM movies WHERE type='films' AND is_open=1 ORDER BY publish DESC LIMIT 10
  2. SELECT title FROM movies WHERE type='films' AND is_open=1 AND genres && ARRAY[1] ORDER BY publish DESC LIMIT 10
  3. SELECT title FROM movies WHERE type='films' AND is_open=1 AND genres && ARRAY[1] AND countries @> ARRAY[1] ORDER BY publish DESC LIMIT 10
  4. SELECT title FROM movies WHERE type='films' AND is_open=1 AND genres && ARRAY[1] AND countries @> ARRAY[1] AND year IN(2013,2014) ORDER BY publish DESC LIMIT 10

Currently the indexes are:
CREATE INDEX movies_genres_idx ON movies USING gin (genres)
CREATE INDEX movies_countries_idx ON movies USING gin (countries)
CREATE INDEX movies_type_open_idx ON movies USING btree (type, is_open)
CREATE INDEX movies_type_open_pub_idx ON movies USING btree (type, is_open, publish)

Answer the question

In order to leave comments, you need to log in

1 answer(s)
A
Alexander, 2014-11-12
@SashaSkot

You would write to begin with what indexes you already have.
Instead of "films", it's better to make a reference to the video content type reference so that this field is an integer, it will work a little faster on a small amount of data and faster on a large one than a string comparison.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question