3

I have the following query:

SELECT * FROM plane_tracks 
    WHERE created_at >= '2016-12-29 08:00:00' AND updated_at <= '2016-12-30 00:00:00';

I've created the following indexes:

create index plane_tracks_on_created_at on plane_tracks(created_at);
create index plane_tracks_on_updated_at on plane_tracks(updated_at);
create index plane_tracks_on_created_at_updated_at on plane_tracks(created_at,updated_at);
create index plane_tracks_on_created_at_updated_at_desc on plane_tracks(created_at,updated_at DESC);

Yet when I run the query, it scans all elements in plane_tracks.

# EXPLAIN ANALYZE SELECT * FROM plane_tracks WHERE created_at >= '2016-12-29 08:00:00' AND updated_at <= '2016-12-30 00:00:00';
                                                                      QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on plane_tracks  (cost=0.00..582.78 rows=5425 width=471) (actual time=1.507..3.435 rows=1978 loops=1)
   Filter: ((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))
   Rows Removed by Filter: 15941
 Planning time: 0.366 ms
 Execution time: 3.618 ms
(5 rows)

When I drop the updated_at part, like this:

SELECT * FROM plane_tracks WHERE created_at >= '2016-12-29 08:00:00';

Then I get this:

# EXPLAIN ANALYZE SELECT * FROM plane_tracks WHERE created_at >= '2016-12-29 08:00:00';
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Seq Scan on plane_tracks  (cost=0.00..537.99 rows=8595 width=471) (actual time=0.022..3.261 rows=8593 loops=1)
   Filter: (created_at >= '2016-12-29 08:00:00'::timestamp without time zone)
   Rows Removed by Filter: 9326
 Planning time: 0.093 ms
 Execution time: 3.901 ms
(5 rows)

At least this last part makes sense.

But I don't understand why

  1. the first query isn't at least as efficient as the second one.
  2. why first query isn't much better than the second one, given that there are also indexes on updated_at.
  3. what index to create to make it really efficient

Tom

1
  1. How are you defining efficient?
    • The first query takes 3.618ms
    • The second query takes 3.901ms
  2. If PostgreSQL doesn't take the combined index, then the planner will assess the cost of two index scans, which will make the seq scan all that much more attractive.
  3. A few things to try.

    1. Drop these (unless you have other queries) and see if Pg takes the index. They're not needed and only serve to confuse things and make UPDATE/INSERT slower slower.

      create index plane_tracks_on_created_at on plane_tracks(created_at);
      create index plane_tracks_on_updated_at on plane_tracks(updated_at);
      create index plane_tracks_on_created_at_updated_at on plane_tracks(created_at,updated_at);
      
    2. If Pg doesn't take the index. Try running set enable_seqscan off in psql and running the same query. Paste back the response. This will tell us whether or not Pg can even do it without a seqscan. If PostgreSQL is still using a seq scan, it's a last resort, and then for some reason it feels it can not use compound index plane_tracks(created_at,updated_at DESC), and we have to look at rewriting that to another compound index.
    3. Also, as a side note. The first query assumes that 5425 rows will be returned, but actually 1978 are returned. This could throw the planner off. PostgreSQL thinks it has to visit 5425 rows, but it is instead only needs to visit 1978
      1. Try ANALYZE plane_tracks. And then EXPLAIN ANALYZE the query again to see if a new analysis has fixed this. If not,
      2. Try set default_statistics_target 1000; and rerunning ANALYZE plane_tracks. If that works, then consider ALTER TABLE plane_tracks SET STATISTICS 1000 (or something higher than what default_statistics_target is currently set at.

Proof that it's possible with test data

As proof that it's possible with one index

CREATE TEMP TABLE foo AS
SELECT
  floor(random()*10)::int AS lower,
  floor(random()*10)::int AS upper
FROM generate_series(1,1e4) AS t(gs);

CREATE INDEX foo_idx ON foo(lower, upper DESC);

VACUUM ANALYZE foo;

EXPLAIN ANALYZE SELECT *
FROM foo
WHERE lower >= 6 AND upper <= 8;

Query plan

 Index Only Scan using foo_idx on foo  (cost=0.29..123.86 rows=3584 width=8) (actual time=0.021..0.678 rows=3580 loops=1)
   Index Cond: ((lower >= 6) AND (upper <= 8))
   Heap Fetches: 0
 Planning time: 0.097 ms
 Execution time: 0.886 ms

Default analytics come close guessing 3584, when it returns 3580. I almost never tinker with default_statistics or table statistics.

  • Dropping all indexes except the one with (created_at, updated_at DESC) turned out to be the key solution. Thanks! – Tom Verbeure Jan 7 '17 at 20:15
  • Mark the solution as accepted if you're satisfied with it. – Evan Carroll Jan 7 '17 at 20:17
  • I seriously doubt that ASC, DESC is any more useful (that normal ASC, ASC index) for this type of query. – ypercubeᵀᴹ Jan 7 '17 at 20:36
2

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,
    some_pay_load TEXT
) ;

I populate the table with some simulated data:

-- Generate data for a few days (60.475 rows).
INSERT INTO 
    plane_tracks (created_at, some_pay_load)
SELECT
    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)
UPDATE
    plane_tracks
SET
    updated_at = created_at + ((random()*10) || ' minutes')::interval
WHERE
    plane_track_id IN 
    (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:

CREATE INDEX 
    plane_tracks_on_created_at_updated_at 
    ON plane_tracks(created_at,updated_at);

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:

CREATE INDEX 
    plane_tracks_on_created_at_updated_at 
    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:

CREATE INDEX
    plane_tracks_gist_idx 
    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.

NOTEs:

  1. 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.

  2. 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.

  3. 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.

  • Hello joanolo, thanks for your extensive reply! I've tried to reproduce your whole example to the letter, and that works fine. My data set as of today is ~35000 entries, with around 5000 entries added each day. So my particular query will typically return that amount. The time difference between created_at and updated_at is usually no more than about 30 minutes. Others have noted that a total execution time of 8ms (as is the case today) is basically in the noise, and that's true indeed. I will revisit the issue if, a year from now, the issue becomes a real problem. Thanks! – Tom Verbeure Jan 7 '17 at 19:59
  • I tried reducing time range more to narrow the amount of data that needs to be returned (to see if the index gets triggered at all), and now I do see the bitmap index scan being used. Success. Thanks! – Tom Verbeure Jan 7 '17 at 20:05
  • Look at the considerations given by the answer of @TypoCube. I think they're really worth reading. Writing extra restrictions (although not strictly needed) helps the planner. – joanolo Jan 7 '17 at 20:12
  • I did! And they work. :-) But meanwhile, I also found out that it wasn't the reduction in time range, but the removal of multiple indexes on the same table that fixed it. – Tom Verbeure Jan 7 '17 at 20:13
2

Queries with two or more range conditions are in general hard to optimize. Even with composite indexes, they'll often require full (or partial) index scans.
No matter the order you define the composite index, (a,b) or (b,a), there will be query parameters and value distributions that will perform badly.

But sometimes it's good to analyze queries from different perspectives. The query:

SELECT * 
FROM plane_tracks 
WHERE created_at >= '2016-12-29 08:00:00' 
  AND updated_at <= '2016-12-30 00:00:00';

a logical perspective / idea:
Is it possible that there are rows that have values such as created_at > updated_at? I.e. values that say that a plane track was updated before it was created?

This sounds absurd (from a logical perspective) but unless you have the necessary constraint, there is no guarantee that such values don't exist on the table.

So, what can we do if we know that no such values exist or (even better) if we have a constraint defined?

We can change the query by adding one more condition.

We know that created_at <= updated_at and updated_at <= '2016-12-30 00:00:00', therefore we know that:
created_at <= '2016-12-30 00:00:00'

The query becomes and (we just proved) it will return same results:

SELECT * 
FROM plane_tracks 
WHERE created_at >= '2016-12-29 08:00:00' 
  AND created_at <= '2016-12-30 00:00:00' 
  AND updated_at <= '2016-12-30 00:00:00';

And this query will be more efficient than the original. Because it restricts the values of created_at more (at both ends) and the composite index (created_at, updated_at) will be used more efficiently. It will still do a partial index scan but in a much smaller range, (not open on one side as before).


We could alternatively add a condition on the other column, which will better use the reverse index on (updated_at, created_at):

SELECT * 
FROM plane_tracks 
WHERE created_at >= '2016-12-29 08:00:00' 
  AND updated_at >= '2016-12-29 08:00:00' 
  AND updated_at <= '2016-12-30 00:00:00';

or add both conditions and let the optimizer pick the appropriate index (either of the two indexes could be used in this case):

SELECT * 
FROM plane_tracks 
WHERE created_at >= '2016-12-29 08:00:00' 
  AND created_at <= '2016-12-30 00:00:00' 
  AND updated_at >= '2016-12-29 08:00:00' 
  AND updated_at <= '2016-12-30 00:00:00';
  • Hello TypoCube, I was able to show that my initial index was working by reducing the data range to something smaller. However, after adding your additional condition (which, indeed, is guaranteed to be the case), the query time got indeed more efficient. So I've added that to my code as well. Thanks! – Tom Verbeure Jan 7 '17 at 20:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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