Search code examples
postgresqlperformancequery-optimizationgist

Postgresql 12: performance issue with overlap operator and join on very same table

I'm having trouble with a "quite simple" request performance:

DB schema:

CREATE TABLE bigdata3.data_1_2021
(
   p_value      float8      NOT NULL,
   p_timestamp  tsrange     NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_data_1_2021_ts ON bigdata3.data_1_2021 USING gist (p_timestamp);
CREATE INDEX IF NOT EXISTS idx_data_1_2021_ts2 ON bigdata3.data_1_2021 USING btree (p_timestamp);

FYI I'm using btree_gist extention

CREATE EXTENSION IF NOT EXISTS btree_gist;

Also, there are 19037 rows in my table. So now, the request:

WITH data_1 AS
(
  SELECT t1.p_value AS value,
         t1.p_timestamp AS TS
  FROM "bigdata3".data_1_2021 AS t1
  WHERE TSRANGE( '2021-02-01 00:00:00.000'::TIMESTAMP,'2021-02-17 09:51:54.000'::TIMESTAMP) && t1.p_timestamp
)
SELECT t1.ts AS ts,
       t2.ts AS ts,
       t1.value,
       t2.value
      FROM data_1 as t1
        INNER JOIN data_1 as t2 ON t1.ts && t2.ts

This request takes 1 minute. When I run an explain, many things seems strange to me:

QUERY PLAN
Nested Loop  (cost=508.96..8108195.71 rows=1801582 width=80)
  Join Filter: (t1.ts && t2.ts)
  CTE data_1
    ->  Seq Scan on data_1_2021 t1_1  (cost=0.00..508.96 rows=18982 width=29)
          Filter: ('["2021-02-01 00:00:00","2021-02-17 09:51:54")'::tsrange && p_timestamp)
  ->  CTE Scan on data_1 t1  (cost=0.00..379.64 rows=18982 width=40)
  ->  CTE Scan on data_1 t2  (cost=0.00..379.64 rows=18982 width=40)

1) I expect the sequence scan on the ts range to use the "idx_data_1_2021_ts" index

2) I expect the join to use the very same index for a hash or merge join

The stranger thing comes now:

WITH data_1 AS
(
  SELECT t1.p_value AS value,
         t1.p_timestamp AS TS
  FROM "bigdata3".data_1_2021 AS t1
  WHERE TSRANGE( '2021-02-01 00:00:00.000'::TIMESTAMP,'2021-02-17 09:51:54.000'::TIMESTAMP) && t1.p_timestamp
),
data_2 AS
(
  SELECT t1.p_value AS value,
         t1.p_timestamp AS TS
  FROM "bigdata3".data_1_2021 AS t1
  WHERE TSRANGE( '2021-02-01 00:00:00.000'::TIMESTAMP,'2021-02-17 09:51:54.000'::TIMESTAMP) && t1.p_timestamp
)
SELECT t1.ts AS ts,
       t2.ts AS ts,
       t1.value,
       t2.value
      FROM data_1 as t1
        INNER JOIN data_2 as t2 ON t1.ts && t2.ts

I only duplicate my data_1 as a data_2 and change my join to join data_1 with data_2:

Nested Loop  (cost=0.28..116154.41 rows=1801582 width=58)
  ->  Seq Scan on data_1_2021 t1  (cost=0.00..508.96 rows=18982 width=29)
        Filter: ('["2021-02-01 00:00:00","2021-02-17 09:51:54")'::tsrange && p_timestamp)
  ->  Index Scan using idx_data_1_2021_ts on data_1_2021 t1_1  (cost=0.28..4.19 rows=190 width=29)
        Index Cond: ((p_timestamp && t1.p_timestamp) AND (p_timestamp && '["2021-02-01 00:00:00","2021-02-17 09:51:54")'::tsrange))

The request take 1 second and now uses the index! But ... it's still not perfect because of the seq scan and the nested loop.

Another piece of info: switching to = operator on the join makes the first case faster, but the second case slower ...

Does anybody have an explanation for why it is not properly using the index when joining the very same table? Also I take any advice to make this request going faster.

Many thanks, Clément

PS: I know this request can look stupid, I made my real case simple to point out my issue.

Edit 1: As requested, the analyze+buffer explain of the first request:

QUERY PLAN
Nested Loop  (cost=509.04..8122335.52 rows=1802721 width=40) (actual time=0.025..216996.205 rows=19680 loops=1)
  Join Filter: (t1.ts && t2.ts)
  Rows Removed by Join Filter: 359841220
  Buffers: shared hit=271
  CTE data_1
    ->  Seq Scan on data_1_2021 t1_1  (cost=0.00..509.04 rows=18988 width=29) (actual time=0.013..38.263 rows=18970 loops=1)
          Filter: ('["2021-02-01 00:00:00","2021-02-17 09:51:54")'::tsrange && p_timestamp)
          Rows Removed by Filter: 73
          Buffers: shared hit=271
  ->  CTE Scan on data_1 t1  (cost=0.00..379.76 rows=18988 width=40) (actual time=0.016..8.083 rows=18970 loops=1)
        Buffers: shared hit=1
  ->  CTE Scan on data_1 t2  (cost=0.00..379.76 rows=18988 width=40) (actual time=0.000..4.723 rows=18970 loops=18970)
        Buffers: shared hit=270
Planning Time: 0.176 ms
Execution Time: 217208.300 ms

AND the second:

QUERY PLAN
Nested Loop  (cost=0.28..116190.34 rows=1802721 width=58) (actual time=280.133..817.611 rows=19680 loops=1)
  Buffers: shared hit=76361
  ->  Seq Scan on data_1_2021 t1  (cost=0.00..509.04 rows=18988 width=29) (actual time=0.030..7.909 rows=18970 loops=1)
        Filter: ('["2021-02-01 00:00:00","2021-02-17 09:51:54")'::tsrange && p_timestamp)
        Rows Removed by Filter: 73
        Buffers: shared hit=271
  ->  Index Scan using idx_data_1_2021_ts on data_1_2021 t1_1  (cost=0.28..4.19 rows=190 width=29) (actual time=0.041..0.042 rows=1 loops=18970)
        Index Cond: ((p_timestamp && t1.p_timestamp) AND (p_timestamp && '["2021-02-01 00:00:00","2021-02-17 09:51:54")'::tsrange))
        Buffers: shared hit=76090
Planning Time: 709.820 ms
Execution Time: 981.659 ms
Solution

  • There are too many questions here, I'll answer the first two:

    1. The index is not used, because the query fetches almost all the rows from the table anyway.

    2. Hash or merge joins can only be used with join conditions that use the = operator. This is quite obvious: a hash can only be probed for equality, and a merge join requires sorting and total order.