I have a sample calendar like application which stores events, their repetitions and event repeat rule. Here is database schema in PostgreSQL:

CREATE TABLE event ( id serial NOT NULL, title character varying(2000) NOT NULL, description character varying(2000) DEFAULT NULL::character varying, location character varying(2000) DEFAULT NULL::character varying, CONSTRAINT pk_event_id PRIMARY KEY (id) ) CREATE TABLE event_repeat_rule ( id serial NOT NULL, event_id integer NOT NULL, start_date bigint NOT NULL, end_date bigint, count integer, repeat_type repeat_t NOT NULL, fixed_interval integer NOT NULL, day_of_month integer[] NOT NULL, day_of_week integer[] NOT NULL, week_of_month week_of_month_t[] NOT NULL, month_of_year integer[] NOT NULL, CONSTRAINT pk_event_repeat_rule PRIMARY KEY (id), CONSTRAINT fk_event_repeat_rule FOREIGN KEY (event_id) REFERENCES event (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT uq_event_repeat_rule_event_id UNIQUE (event_id) ) -- each event can be labeled with multiple tags. Tag table is not shown here. CREATE TABLE event_tag ( id serial NOT NULL, event_id integer NOT NULL, tag_id integer NOT NULL, CONSTRAINT pk_event_tag_id PRIMARY KEY (id), CONSTRAINT fk_event_tag_event_id FOREIGN KEY (event_id) REFERENCES event (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_event_tag_tag_id FOREIGN KEY (tag_id) REFERENCES tag (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT uq_evnet_tag_event_id_tag_id UNIQUE (event_id, tag_id) ) CREATE INDEX idx_event_tag_tag_id ON event_tag USING btree (tag_id); CREATE TABLE event_time ( id serial NOT NULL, event_id integer NOT NULL, start_time bigint NOT NULL, end_time bigint, CONSTRAINT pk_event_time_id PRIMARY KEY (id), CONSTRAINT fk_event_time_event_id FOREIGN KEY (event_id) REFERENCES event (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE ) CREATE INDEX idx_event_time_event_id_start_time_end_time ON event_time USING btree (event_id, start_time, end_time); 

The overall description of schema: Each event has a repeat rule or not. each event can be labeled with tags.(many to many relation with Tag table). and all times of each event (single or repetitive) is in event_time table so the relation is 1 to many. There is an index on (event_id, start_time, end_time) in event_time table.

I query this schema based on tag_id and start_time. This is my query:

SELECT * FROM event_time JOIN event ON event_time.event_id = event.id JOIN event_tag ON event_tag.event_id = event.id LEFT OUTER JOIN event_repeat_rule ON event.id = event_repeat_rule.event_id WHERE event_tag.tag_id = 1 AND event_time.start_time <= 1411465037 AND event_time.end_time >= 1408873037; 

When I run this query with EXPLAIN on this query, I get this:

-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------- QUERY PLAN | Nested Loop Left Join (cost=3.08..15.75 rows=2 width=587) -[ RECORD 2 ]------------------------------------------------------------------------------------------------------------- QUERY PLAN | -> Hash Join (cost=2.93..9.75 rows=2 width=423) -[ RECORD 3 ]------------------------------------------------------------------------------------------------------------- QUERY PLAN | Hash Cond: (event_time.event_id = event.id) -[ RECORD 4 ]------------------------------------------------------------------------------------------------------------- QUERY PLAN | -> Seq Scan on event_time (cost=0.00..6.69 rows=22 width=24) -[ RECORD 5 ]------------------------------------------------------------------------------------------------------------- QUERY PLAN | Filter: ((start_time <= 1411465037) AND (start_time >= 1408873037)) -[ RECORD 6 ]------------------------------------------------------------------------------------------------------------- QUERY PLAN | -> Hash (cost=2.87..2.87 rows=5 width=399) -[ RECORD 7 ]------------------------------------------------------------------------------------------------------------- QUERY PLAN | -> Hash Join (cost=1.52..2.87 rows=5 width=399) -[ RECORD 8 ]------------------------------------------------------------------------------------------------------------- QUERY PLAN | Hash Cond: (event.id = event_tag.event_id) -[ RECORD 9 ]------------------------------------------------------------------------------------------------------------- QUERY PLAN | -> Seq Scan on event (cost=0.00..1.17 rows=17 width=386) -[ RECORD 10 ]------------------------------------------------------------------------------------------------------------ QUERY PLAN | -> Hash (cost=1.45..1.45 rows=6 width=13) -[ RECORD 11 ]------------------------------------------------------------------------------------------------------------ QUERY PLAN | -> Seq Scan on event_tag (cost=0.00..1.45 rows=6 width=13) -[ RECORD 12 ]------------------------------------------------------------------------------------------------------------ QUERY PLAN | Filter: (tag_id = 1) -[ RECORD 13 ]------------------------------------------------------------------------------------------------------------ QUERY PLAN | -> Index Scan using uq_event_repeat_rule_event_id on event_repeat_rule (cost=0.15..2.99 rows=1 width=164) -[ RECORD 14 ]------------------------------------------------------------------------------------------------------------ QUERY PLAN | Index Cond: (event.id = event_id) 

I'm getting Seq Scan on almost all tables. Low number of records might be the cause. But I don't want design based on estimation. Is my index on event_time table which is (event_id, start_time, end_time) can satisfy this query?