title: How to query multiple COUNT(*) with good performance
body: <p>I have this table:</p>
  
  <pre><code>CREATE TABLE schedule (
  schedule_id serial NOT NULL,
  start_date date,
  CONSTRAINT schedule_id PRIMARY KEY (schedule_element_id)
  )
  </code></pre>
  
  <p>And this table:</p>
  
  <pre><code>CREATE TABLE schedule_user (
  schedule_user_id serial NOT NULL,
  schedule_id integer,
  state int,
  CONSTRAINT fk_schedule_id   FOREIGN KEY (schedule_id)
        REFERENCES schedule (schedule_id) MATCH SIMPLE
        ON UPDATE NO ACTION ON DELETE NO ACTION
  )
  </code></pre>
  
  <pre>
  schedule 
   -------------------------
  |schedule_id |date        |
  |------------+------------|
  |1           |'2013-10-10'|
  |2           |'2013-10-20'|
  |3           |'2013-08-13'|
   -------------------------
  
  schedule_user 
   -----------------------------------
  |schedule_user_id|schedule_id |state|
  |----------------+------------+-----|
  |1               | 1          |0    |
  |2               | 1          |1    |
  |3               | 1          |2    |
  |4               | 1          |0    |
  |5               | 1          |1    |
  |6               | 1          |1    |
  |4               | 2          |0    |
  |5               | 2          |1    |
  |7               | 2          |0    |
  |2               | 3          |1    |
   -----------------------------------
  </pre>
  
  <p>And I want a table like this:</p>
  
  <pre>
  characteristic
   ---------------------------------------
  |schedule_id |state0|state1|state2|total|
  |------------+------+------+------+-----|
  |1           |2     |3     |1     |6    |
  |2           |2     |1     |0     |3    |
  |3           |1     |1     |0     |2    |
   ---------------------------------------
  </pre>
  
  <p>I've made this query that looks as as horrible as it's performance.</p>
  
  <pre><code>SELECT 
      schedule.schedule_id AS id, 
      (( SELECT count(*) AS count
           FROM schedule_user
           WHERE schedule_user.schedule_id = schedule.schedule_id
             AND state=0))::integer AS state0, 
      (( SELECT count(*) AS count
           FROM schedule_user
           WHERE schedule_user.schedule_id = schedule.schedule_id
             AND state=1))::integer AS state1,
      (( SELECT count(*) AS count
           FROM schedule_user
           WHERE schedule_user.schedule_id = schedule.schedule_id
             AND state=2))::integer AS state2,          
      (( SELECT count(*) AS count
           FROM schedule_user
           WHERE schedule_user.schedule_id = schedule.schedule_id))::integer
         AS total
    FROM schedule
  </code></pre>
  
  <p>Is there a better way to perform such a query?
  Should I create an Index to 'state' column? if so, how should it look like?</p>