Whether PostgreSQL will use one index or not depends (a lot!) on the actual values of your tables, the conditions on your queries, whether statistics are up-to-date for the values in the table, and your configuration settings. So... whatever comes next depends a lot on your actual data. If you can provide a better description of your actual values, I guess we'll be able to better help you.
Anyhow, a first guess:
I assume your table has a structure similar to this:
CREATE TABLE plane_tracks
plane_track_id SERIAL PRIMARY KEY,
created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT now(),
updated_at TIMESTAMP WITHOUT TIME ZONE NULL,
I populate the table with some simulated data:
-- Generate data for a few days (60.475 rows).
plane_tracks (created_at, some_pay_load)
generate_series(timestamp '2016-12-25 00:00:00',
timestamp '2016-12-31 23:59:00', interval '10 seconds'), 'payload' ;
I simulate that (about) half your rows are updated, and that changes "udpated_at":
-- Update randomly some of these data (just simulate updated_at)
updated_at = created_at + ((random()*10) || ' minutes')::interval
(SELECT plane_track_id FROM plane_tracks ORDER BY random() LIMIT 30000) ;
I make sure PostgreSQL has good statistics:
ANALYZE VERBOSE plane_tracks ;
If I use under these conditions an index on (created_at, updated_at) with your query:
The index IS really used:
explain analyze SELECT * FROM plane_tracks
WHERE created_at >= '2016-12-29 08:00:00'
AND updated_at <= '2016-12-30 00:00:00';
Bitmap Heap Scan on plane_tracks (cost=890.34..1679.47 rows=12209 width=28) (actual time=4.611..5.194 rows=2834 loops=1)
Recheck Cond: ((created_at >= '2016-12-29 08:00:00'::timestamp without time zone) AND (updated_at <= '2016-12-30 00:00:00'::timestamp without time zone))
Heap Blocks: exact=22
-> Bitmap Index Scan on plane_tracks_on_created_at_updated_at (cost=0.00..887.29 rows=12209 width=0) (actual time=4.596..4.596 rows=2834 loops=1)
Index Cond: ((created_at >= '2016-12-29 08:00:00'::timestamp without time zone) AND (updated_at <= '2016-12-30 00:00:00'::timestamp without time zone))
Planning time: 0.147 ms
Execution time: 5.443 ms
The reason why the index is used: PostgreSQL thinks it will help it select 12.209 out of the 60.475, and this will take less time than a sequential scan. This is giving good selectivity.
If you just change your query, and you make the condition broader, see what happens:
explain analyze SELECT * FROM plane_tracks
WHERE created_at >= '2016-12-26 08:00:00' /* 26 instead of 29 */
AND updated_at <= '2016-12-31 00:00:00'; /* 31 instead of 30 */
Seq Scan on plane_tracks (cost=0.00..1963.12 rows=31066 width=28) (actual time=12.890..21.236 rows=19993 loops=1)
Filter: ((created_at >= '2016-12-26 08:00:00'::timestamp without time zone) AND (updated_at <= '2016-12-31 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 40482
Planning time: 0.086 ms
Execution time: 22.441 ms
The reason: PostgreSQL has decided that, if the number of rows returned is going to be much higher than before, it makes more sense to scan the whole table than to use the index. Using an index means: first check the index, then gather the data from the main table. The savings on data gathering (SELECT *) would not be compensated by the expense of also checking the index.
In practice: I'd probably have one ("reasonable") index, and let PostgreSQL decide whether it is worth using it or not. The way you use your index (one comparison >=, the other <=), I think the best suited should be:
ON plane_tracks (created_at, updated_at DESC);
Anyhow, it's normally good to check a few variants. You could also try to use a GIST index:
ON plane_tracks USING gist (created_at, updated_at);
... and see if that helps. GIST indexes tend to be good when you're going to use ranges, but you need to try which combination fits best your use-case.
My test were done on 9.6.1 on Mac OS X 10.12, with "out of the box" settings. If you perform the same tests with other settings, results might change.
The choice of sequential versus index scans depends not only on your data, but also on a number of PostgreSQL settings. You can actually force PostgreSQL to not use index scans, or set some cost parameters for random access so high that, in practice, the usage of indexes will be heavily restricted. Check the Query Planning documentation.
When you're not sure about which indices to use, you can start by creating (a few) more than you actually need, analyze the index usage statistics after the system has been running in practice for a period that you think is long enough, and remove the seldom used ones.