Postgresql ignoring index on timestamp column even if query is faster using index

Question

On postgresql 9.3, I have a table with a little over a million records, the table was created as:

CREATE TABLE entradas
(
 id serial NOT NULL,
 uname text,
 contenido text,
 fecha date,
 hora time without time zone,
 fecha_hora timestamp with time zone,
 geom geometry(Point,4326),
 CONSTRAINT entradas_pkey PRIMARY KEY (id)
)
WITH (
 OIDS=FALSE
);
ALTER TABLE entradas
OWNER TO postgres;

CREATE INDEX entradas_date_idx
 ON entradas
 USING btree
 (fecha_hora);

CREATE INDEX entradas_gix
 ON entradas
 USING gist
 (geom);

I'm executing a query to aggregate rows on time intervals as follows:

WITH x AS (
        SELECT t1, t1 + interval '15min' AS t2
        FROM   generate_series('2014-12-02 0:0' ::timestamp
                  ,'2014-12-02 23:45' ::timestamp, '15min') AS t1
        )

    select distinct
        x.t1,
        count(t.id) over w
    from x
    left join entradas  t  on t.fecha_hora >= x.t1
            AND t.fecha_hora < x.t2
    window w as (partition by x.t1)
    order by x.t1

This query takes about 50 seconds. From the output of explain, you can see that the timestamp index is not used:

Unique  (cost=86569161.81..87553155.15 rows=131199111 width=12)
 CTE x
   ->  Function Scan on generate_series t1  (cost=0.00..12.50 rows=1000 width=8)
   ->  Sort  (cost=86569149.31..86897147.09 rows=131199111 width=12)
     Sort Key: x.t1, (count(t.id) OVER (?))
     ->  WindowAgg  (cost=55371945.38..57667929.83 rows=131199111 width=12)
           ->  Sort  (cost=55371945.38..55699943.16 rows=131199111 width=12)
                 Sort Key: x.t1
                 ->  Nested Loop Left Join  (cost=0.00..26470725.90 rows=131199111 width=12)
                       Join Filter: ((t.fecha_hora >= x.t1) AND (t.fecha_hora < x.t2))
                       ->  CTE Scan on x  (cost=0.00..20.00 rows=1000 width=16)
                       ->  Materialize  (cost=0.00..49563.88 rows=1180792 width=12)
                             ->  Seq Scan on entradas t  (cost=0.00..37893.92 rows=1180792 width=12)

However, if i do set enable_seqscan=false (I know, one should never do this), then the query executes in less than a second and the output of explain shows that it is using the index on the timestamp column:

Unique  (cost=91449584.16..92433577.50 rows=131199111 width=12)
CTE x
  ->  Function Scan on generate_series t1  (cost=0.00..12.50 rows=1000 width=8)
->  Sort  (cost=91449571.66..91777569.44 rows=131199111 width=12)
      Sort Key: x.t1, (count(t.id) OVER (?))
      ->  WindowAgg  (cost=60252367.73..62548352.18 rows=131199111 width=12)
            ->  Sort  (cost=60252367.73..60580365.51 rows=131199111 width=12)
                  Sort Key: x.t1
                  ->  Nested Loop Left Join  (cost=1985.15..31351148.25 rows=131199111 width=12)
                       ->  CTE Scan on x  (cost=0.00..20.00 rows=1000 width=16)
                        ->  Bitmap Heap Scan on entradas t  (cost=1985.15..30039.14 rows=131199 width=12)
                              Recheck Cond: ((fecha_hora >= x.t1) AND (fecha_hora < x.t2))
                              ->  Bitmap Index Scan on entradas_date_idx  (cost=0.00..1952.35 rows=131199 width=0)
                                   Index Cond: ((fecha_hora >= x.t1) AND (fecha_hora < x.t2))

Why is postgres not using entradas_date_idx unless I force it to even if executing the query is way faster using it?

How could I make postgres use entradas_date_idx without resorting to set enable_seqscan=false?

Answer 1

You can simplify your query quite a bit:

SELECT x.t1, count(*) AS ct
FROM   generate_series('2014-12-02'::timestamp
                     , '2014-12-03'::timestamp
                     , '15 min'::interval) x(t1)
LEFT   JOIN entradas t ON t.fecha_hora >= x.t1
                      AND t.fecha_hora <  x.t1 + interval '15 min' 
GROUP  BY 1
ORDER  BY 1;

DISTINCT in combination with a window function is typically much more expensive (and also harder to estimate) for the query planner.

The CTE is not necessary and typically more expensive than a subquery. And also harder to estimate for the query planner since CTEs are optimization barriers.

It looks like you want to cover a whole day, but you were missing out on the last 15 minutes. Use a simpler generate_series() expression to cover the whole day (still not overlapping with adjacent days).

Next, why do you have fecha_hora timestamp with time zone, while you also have have fecha date and hora time [without time zone]? Looks like it should be fecha_hora timestamp and drop the redundant columns?
This would also avoid the subtle difference to the data type of your generate_series() expression - which should not normally be a problem, but timestamp depends on the time zone of your session and is not IMMUTABLE like timestamptz.

If that's sill not good enough, add a redundant WHERE condition as advised by @Daniel to instruct the query planner.

Basic advise for bad plans is applicable as well:

• Keep PostgreSQL from sometimes choosing a bad query plan

Answer 2

Analysis of the wrong estimate

The gist of the problem here is that the postgres planner has no idea what values and how many rows are coming out of the generate_series call, and yet has to estimate how much of them will satisfy the JOIN condition against the big entradas table. In your case, it fails big time.

In reality, only a small portion of the table will be joined, but the estimate errs on the opposite side, as shown in this part of the EXPLAIN:

->  Nested Loop Left Join  (cost=0.00..26470725.90 rows=131199111 width=12)
      Join Filter: ((t.fecha_hora >= x.t1) AND (t.fecha_hora < x.t2))
      ->  CTE Scan on x  (cost=0.00..20.00 rows=1000 width=16)
      ->  Materialize  (cost=0.00..49563.88 rows=1180792 width=12)
            ->  Seq Scan on entradas t  (cost=0.00..37893.92 rows=1180792 width=12)

entradas is estimated at 1180792 rows, x is estimated at 1000 rows which I believe is just the default for any SRF call. The result of the JOIN is estimated at 131199111 rows, more than 100 times the number of rows of the big table!

Trick the planner into a better estimate

Since we know that the timestamps in x belong to a narrow range (one day), we may help the planner with that information in the form of an additional JOIN condition:

 left join entradas  t 
         ON t.fecha_hora >= x.t1
        AND t.fecha_hora < x.t2
        AND (t.fecha_hora BETWEEN '2014-12-02'::timestamp
                             AND '2014-12-03'::timestamp)

(it does not matter that the BETWEEN range includes the upper bound or is generally a bit bigger, it will be filtered out strictly by the other conditions).

The planner should then be able to make use of the statistics, recognize that only a small portion of the index is concerned by this range of values, and use the index rather than sequentially scanning the entire big table.

Answer 3

In terms of index usage, the query planner tries to make an educated guess (based on the available indexes, table statistics, and the query itself, among other things) about the best way to execute the query. There are cases where it will always end up doing a sequential scan even when using the index would be much, much faster. It's just that the query planner doesn't know that in those cases (in many cases, particularly when a query is going to return a lot of rows, a sequential scan is faster than doing a bunch of index scans).

Essentially, this is an example of a case where you know your data for this very specific case better than the query planner does (which has to take a more generic, wider look that covers a variety of cases and possible inputs).

For cases like this where you know forcing index use via enable_seqscan=false, I don't think there is an issue with using it. I do this myself for some specific cases, as to do otherwise would be a huge performance sink, and I know that for those certain queries, forcing index usage results in queries which are orders of magnitude faster.

There are two things to keep in mind with this though:

1. You should always make sure to re-enable sequential scans right after the query, or else it will remain for the rest of the connection for all other queries, which is not likely what you want. If your query changes a bit, or if the data in the table grows significantly, it may no longer be faster to do an index query, although this is certainly a testable thing.

2. The use of CTEs can have a significant impact on the query planner's ability to optimize a query efficiently. I don't think that's the crux of the issue in this case.

Answer 4

If your table is new and the rows are recently added postgres may not have collected enough statistics on the new data. If this is the case, you can try to ANALYZE the table.

PS: Make sure the statistics target is not set to zero on the table.