current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I want to fetch users that has 1 or more processed bets. I do this by using next sql:

SELECT user_id FROM bets 
WHERE bets.state in ('guessed', 'losed') 
GROUP BY user_id 
HAVING count(*) > 0;

But running EXPLAIN ANALYZE I noticed no index is used and query execution time is very high. I tried add partial index like:

CREATE INDEX processed_bets_index ON bets(state) WHERE state in ('guessed', 'losed');

But EXPLAIN ANALYZE output not changed:

 HashAggregate  (cost=34116.36..34233.54 rows=9375 width=4) (actual time=235.195..237.623 rows=13310 loops=1)
   Filter: (count(*) > 0)
   ->  Seq Scan on bets  (cost=0.00..30980.44 rows=627184 width=4) (actual time=0.020..150.346 rows=626674 loops=1)
     Filter: ((state)::text = ANY ('{guessed,losed}'::text[]))
     Rows Removed by Filter: 20951
 Total runtime: 238.115 ms
 (6 rows)

Records with other statuses except (guessed, losed) a little.

How do I create proper index?

I'm using PostgreSQL 9.3.4.

share|improve this question
    
Please stick to one question per post - post a new question if there are two separate things you want to discuss. Link between them if you like. –  Craig Ringer Jun 28 at 7:14
1  
"Records with other statuses except (guessed, losed) a little.". Huh? I do not understand what this means. Anyway: first idea, did you ANALYZE bets;? What proportion of rows are in guessed and losed (it's "lost" by the way) state? –  Craig Ringer Jun 28 at 7:15
    
Sorry for my bad english) Bet has three states includes: guessed, losed, in_process. The proportion is about 45/45/10. –  Sergey Gernyak Jun 28 at 10:25

1 Answer 1

up vote 2 down vote accepted

I assume that the state mostly consists of 'guessed' and 'losed', with maybe a few other states as well in there. So most probably the optimizer do not see the need to use the index since it would still fetch most of the rows.

What you do need is an index on the user_id, so perhaps something like this would work:

CREATE INDEX idx_bets_user_id_in_guessed_losed ON bets(user_id) WHERE state in ('guessed', 'losed');

Or, by not using a partial index:

CREATE INDEX idx_bets_state_user_id ON bets(state, user_id);
share|improve this answer
    
Without using a partial index gave me the expected result! Thanks! –  Sergey Gernyak Jun 28 at 10:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.