title: how to properly index multiple queried columns for best performance
body: <p>Let's say I have a table with multiple columns:</p>
  
  <pre><code>CREATE TABLE offers (
      id integer NOT NULL, -- not queried, just a unique id
      rooms integer,
      price integer,
      size integer,
      hood_id integer,
      city_id integer,
      ... (some other columns, not queried)
  );
  </code></pre>
  
  <p>Users make queries on that table with many combinations of the above columns. Sometimes they query just by one column, sometimes by all of them. I want to cover all cases for best performance.</p>
  
  <p>So the question is would it be sufficient to index each column independently:</p>
  
  <pre><code>CREATE INDEX index_offers_on_rooms ON offers USING btree (rooms);
  CREATE INDEX index_offers_on_price ON offers USING btree (price);
  CREATE INDEX index_offers_on_size ON offers USING btree (size);
  CREATE INDEX index_offers_on_hood_id ON offers USING btree (hood_id);
  CREATE INDEX index_offers_on_city_id ON offers USING btree (city_id);
  </code></pre>
  
  <p>and create one index for all of the above:</p>
  
  <pre><code>CREATE INDEX index_all_offers_data ON offers USING btree (rooms, price, size, hood_id, city_id);
  </code></pre>
  
  <p>Or should I cover all the possible cases like:</p>
  
  <pre><code>CREATE INDEX index_offers_on_rooms_and_price ON offers USING btree (rooms, price);
  CREATE INDEX index_offers_on_rooms_and_size ON offers USING btree (rooms, size);
  CREATE INDEX index_offers_on_rooms_and_price_and_size ON offers USING btree (rooms, price, size);
  ...
  </code></pre>
  
  <p>The above doesn't seem right, as updating and inserting would get really slow. Those records are not going to be updated, however there's going to be a lot of creating (at least 600 records per hour) and deleting.</p>