search

timescale / timescaledb

An open-source time-series SQL database optimized for fast ingest and complex queries. Packaged as a PostgreSQL extension.
https://www.timescale.com/
Other
17.85k stars 884 forks source link

[Bug]: Very long planning times for `UPDATE`/`DELETE`/`SELECT FOR UPDATE` on a space-partitioned hypertable #4687

Open maxtwardowski opened 2 years ago

maxtwardowski commented 2 years ago

What type of bug is this?

Performance issue

What subsystems and features are affected?

Partitioning, Query planner

What happened?

Timescale's planning times are very long for single item, primary key defined UPDATE, DELETE and SELECT FOR UPDATE statements on a space-partitioned hypertable. It doesn't happen for the same dataset in a table without the space partitioning.

TimescaleDB version affected

2.9.0-dev (a26a5974dce08e1146bc7aa35eac7e1f6f1716e2)

PostgreSQL version used

14.5

What operating system did you use?

Ubuntu 20.04 x64

What installation method did you use?

Source

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

No response

How can we reproduce the bug?

1. Create a hypertable with the following schema:

create table test_ht (
    tenant_id bigint not null,
    id bigint not null,
    created_at timestamptz not null,
    value text,
    primary key(tenant_id, id, created_at)
);
select create_hypertable('test_ht', 'created_at');
select add_dimension('test_ht', 'tenant_id', chunk_time_interval := 1);

2. Populate the table with data - create 210 tenants (space partitions) and 200k rows for each of the tenant with "random" timestamps (it may take some time):

DO
$do$
BEGIN 
    FOR i IN 1..210 loop
        raise notice 'inserting for tenant %', i;
      insert into test_ht 
      select i, generate_series(1, 200000), clock_timestamp() - concat(floor(random() * 1000)::text, ' day')::interval, md5(random()::text);
    END LOOP;
END
$do$;

3. Analyze UPDATE query:

Query:
explain analyze
update test_ht set created_at = test_ht.created_at where tenant_id = 105 and id = 7279 and created_at = 'someActualTimestamp';

Result:
Custom Scan (HypertableModify)  (cost=0.28..2.40 rows=1 width=18) (actual time=0.097..0.098 rows=0 loops=1)
  ->  Update on test_ht  (cost=0.28..2.40 rows=1 width=18) (actual time=0.097..0.098 rows=0 loops=1)
        Update on _hyper_2416_464122_chunk test_ht_1
        ->  Index Scan using "464122_666263_test_ht_pkey" on _hyper_2416_464122_chunk test_ht_1  (cost=0.28..2.40 rows=1 width=18) (actual time=0.032..0.033 rows=1 loops=1)
              Index Cond: ((tenant_id = 105) AND (id = 7279) AND (created_at = '2020-10-06 19:09:32.9+00'::timestamp with time zone))
Planning Time: 3491.582 ms
Execution Time: 1.034 ms

4. Analyze SELECT FOR UPDATE query:

Query:
explain analyze
select * from test_ht where tenant_id = 105 and id = 7279 and created_at = 'someActualTimestamp' limit 1 for update;

Result:
Limit  (cost=0.00..1.21 rows=1 width=66) (actual time=20.858..20.862 rows=1 loops=1)
  ->  LockRows  (cost=0.00..2.43 rows=2 width=66) (actual time=20.856..20.859 rows=1 loops=1)
        ->  Append  (cost=0.00..2.41 rows=2 width=66) (actual time=0.082..0.084 rows=1 loops=1)
              ->  Seq Scan on test_ht test_ht_1  (cost=0.00..0.00 rows=1 width=66) (actual time=0.038..0.039 rows=0 loops=1)
                    Filter: ((tenant_id = 105) AND (id = 7279) AND (created_at = '2020-10-06 19:09:32.9+00'::timestamp with time zone))
              ->  Index Scan using "464122_666263_test_ht_pkey" on _hyper_2416_464122_chunk test_ht_2  (cost=0.28..2.40 rows=1 width=66) (actual time=0.042..0.042 rows=1 loops=1)
                    Index Cond: ((tenant_id = 105) AND (id = 7279) AND (created_at = '2020-10-06 19:09:32.9+00'::timestamp with time zone))
Planning Time: 3432.151 ms
Execution Time: 80.069 ms

5. Analyze DELETE query:

Query: 
explain analyze
delete from test_ht where tenant_id = 105 and id = 36732 and created_at = 'someActualTimestamp';

Result:
Custom Scan (HypertableModify)  (cost=0.28..2.40 rows=1 width=10) (actual time=0.053..0.055 rows=0 loops=1)
  ->  Delete on test_ht  (cost=0.28..2.40 rows=1 width=10) (actual time=0.053..0.055 rows=0 loops=1)
        Delete on _hyper_2416_464122_chunk test_ht_1
        ->  Index Scan using "464122_666263_test_ht_pkey" on _hyper_2416_464122_chunk test_ht_1  (cost=0.28..2.40 rows=1 width=10) (actual time=0.030..0.032 rows=1 loops=1)
              Index Cond: ((tenant_id = 105) AND (id = 7279) AND (created_at = '2020-10-06 19:09:32.9+00'::timestamp with time zone))
Planning Time: 3547.878 ms
Execution Time: 1.034 ms

6. Now create the same table but WITHOUT the space partitioning: 

create table test_ht_no_part (
    tenant_id bigint not null,
    id bigint not null,
    created_at timestamptz not null,
    value text,
    primary key(tenant_id, id, created_at)
);
select create_hypertable('test_ht_no_part', 'created_at');

7. Populate it with THE SAME data as the space-partitioned table:

insert into test_ht_no_part select * from test_ht;

8. Analyze UPDATE query on the hypertable without space partitioning:

Query: 
explain analyze
update test_ht_no_part set created_at = test_ht_no_part.created_at where tenant_id = 105 and id = 7279 and created_at = 'someActualTimestamp';

Result:
Custom Scan (HypertableModify)  (cost=0.42..2.54 rows=1 width=18) (actual time=0.080..0.081 rows=0 loops=1)
  ->  Update on test_ht_no_part  (cost=0.42..2.54 rows=1 width=18) (actual time=0.080..0.081 rows=0 loops=1)
        Update on _hyper_2418_480970_chunk test_ht_no_part_1
        ->  Index Scan using "480970_683111_test_ht_no_part_pkey" on _hyper_2418_480970_chunk test_ht_no_part_1  (cost=0.42..2.54 rows=1 width=18) (actual time=0.044..0.046 rows=1 loops=1)
              Index Cond: ((tenant_id = 105) AND (id = 653) AND (created_at = '2020-05-18 19:09:32.837+00'::timestamp with time zone))
Planning Time: 7.824 ms
Execution Time: 0.122 ms

9. Analyze SELECT FOR UPDATE query on the hypertable without space partitioning:

Query: 
explain analyze
select * from test_ht_no_part where tenant_id = 105 and id = 7279 and created_at = 'someActualTimestamp' limit 1 for update;

Result:
Limit  (cost=0.00..1.29 rows=1 width=66) (actual time=0.104..0.105 rows=1 loops=1)
  ->  LockRows  (cost=0.00..2.57 rows=2 width=66) (actual time=0.103..0.104 rows=1 loops=1)
        ->  Append  (cost=0.00..2.55 rows=2 width=66) (actual time=0.040..0.041 rows=1 loops=1)
              ->  Seq Scan on test_ht_no_part test_ht_no_part_1  (cost=0.00..0.00 rows=1 width=66) (actual time=0.008..0.008 rows=0 loops=1)
                    Filter: ((tenant_id = 105) AND (id = 653) AND (created_at = '2020-05-18 19:09:32.837+00'::timestamp with time zone))
              ->  Index Scan using "480970_683111_test_ht_no_part_pkey" on _hyper_2418_480970_chunk test_ht_no_part_2  (cost=0.42..2.54 rows=1 width=66) (actual time=0.032..0.032 rows=1 loops=1)
                    Index Cond: ((tenant_id = 105) AND (id = 653) AND (created_at = '2020-05-18 19:09:32.837+00'::timestamp with time zone))
Planning Time: 7.861 ms
Execution Time: 0.263 ms

10. Analyze DELETE query on the hypertable without space partitioning:

Query: 
explain analyze
delete from test_ht_no_part where tenant_id = 105 and id = 36732 and created_at = 'someActualTimestamp';

Result:
Custom Scan (HypertableModify)  (cost=0.42..2.54 rows=1 width=10) (actual time=0.046..0.047 rows=0 loops=1)
  ->  Delete on test_ht_no_part  (cost=0.42..2.54 rows=1 width=10) (actual time=0.046..0.047 rows=0 loops=1)
        Delete on _hyper_2418_480970_chunk test_ht_no_part_1
        ->  Index Scan using "480970_683111_test_ht_no_part_pkey" on _hyper_2418_480970_chunk test_ht_no_part_1  (cost=0.42..2.54 rows=1 width=10) (actual time=0.028..0.029 rows=1 loops=1)
              Index Cond: ((tenant_id = 105) AND (id = 653) AND (created_at = '2020-05-18 19:09:32.837+00'::timestamp with time zone))
Planning Time: 7.766 ms
Execution Time: 0.090 ms
pzduniak commented 2 years ago

Additional context: we have 36000 chunks in one of the offending tables.

maxtwardowski commented 2 years ago

@pzduniak number of chunks definitely affects these planning times. I've tested it for the same data in a hypertable with monthly chunks (test_ht in the original post uses the default interval which is per docs 7 days):

  1. Create the table:

    create table test_ht_month_chunks (
tenant_id bigint not null,
id bigint not null,
created_at timestamptz not null,
value text,
primary key(tenant_id, id, created_at)
);
select create_hypertable('test_ht_month_chunks', 'created_at', chunk_time_interval := '1 month'::interval);
select add_dimension('test_ht_month_chunks', 'tenant_id', chunk_time_interval := 1);

  2. Populate it with the data from the reproduction section from the original post:

    insert into test_ht_month_chunks select * from test_ht;

  3. Analyze the SELECT FOR UPDATE query:

    explain analyze 
select * from test_ht_month_chunks where tenant_id = 105 and id = 7210 and created_at = 'someTimestamp' for update;

Output:

LockRows  (cost=0.00..2.43 rows=2 width=66) (actual time=5.913..7.529 rows=1 loops=1)
  ->  Append  (cost=0.00..2.41 rows=2 width=66) (actual time=0.073..0.080 rows=1 loops=1)
        ->  Seq Scan on test_ht_month_chunks test_ht_month_chunks_1  (cost=0.00..0.00 rows=1 width=66) (actual time=0.038..0.038 rows=0 loops=1)
              Filter: ((tenant_id = 105) AND (id = 7210) AND (created_at = '2022-02-17 19:09:32.899+00'::timestamp with time zone))
        ->  Index Scan using "494988_697129_test_ht_month_chunks_pkey" on _hyper_2420_494988_chunk test_ht_month_chunks_2  (cost=0.28..2.40 rows=1 width=66) (actual time=0.034..0.038 rows=1 loops=1)
              Index Cond: ((tenant_id = 105) AND (id = 7210) AND (created_at = '2022-02-17 19:09:32.899+00'::timestamp with time zone))
Planning Time: 646.155 ms
Execution Time: 21.053 ms

The planning time has improved over 5 times - it was 3432.151 ms for test_ht.

jnidzwetzki commented 2 years ago

Hello @maxtwardowski,

Thank you very much for reaching out and for the excellent script to reproduce the issue. I was able to reproduce the high planning times in my local environment, even with the latest version of timescaledb (2.9-0-dev / 88e2f24ea3e09f4c39256d27b558d997d0367738).

Hypertable with space partitioning

Normal SELECT

test2=# explain analyze
select * from test_ht where tenant_id = 105 and id = 7279 and created_at = '2020-10-06 19:09:32.9+00'::timestamp limit 1;
                                                                                  QUERY PLAN                                                                                   
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.28..8.58 rows=1 width=57) (actual time=0.150..0.152 rows=0 loops=1)
   ->  Custom Scan (ChunkAppend) on test_ht  (cost=0.28..1186.90 rows=143 width=57) (actual time=0.148..0.150 rows=0 loops=1)
         Chunks excluded during startup: 142
         ->  Index Scan using _hyper_3_27032_chunk_test_ht_created_at_idx on _hyper_3_27032_chunk  (cost=0.28..8.30 rows=1 width=57) (actual time=0.146..0.147 rows=0 loops=1)
               Index Cond: (created_at = '2020-10-06 19:09:32.9'::timestamp without time zone)
               Filter: ((tenant_id = 105) AND (id = 7279))
 Planning Time: 228.104 ms
 Execution Time: 7.071 ms
(8 rows)

SELECT with 'FOR UPDATE'

test2=# explain analyze
select * from test_ht where tenant_id = 105 and id = 7279 and created_at = '2020-10-06 19:09:32.9+00'::timestamp limit 1 for update;
                                                                                          QUERY PLAN                                                                           

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------
 Limit  (cost=0.00..8.25 rows=1 width=67) (actual time=0.096..0.099 rows=0 loops=1)
   ->  LockRows  (cost=0.00..1188.34 rows=144 width=67) (actual time=0.094..0.096 rows=0 loops=1)
         ->  Custom Scan (ChunkAppend) on test_ht  (cost=0.00..1186.90 rows=144 width=67) (actual time=0.092..0.095 rows=0 loops=1)
               Chunks excluded during startup: 142
               ->  Seq Scan on test_ht test_ht_1  (cost=0.00..0.00 rows=1 width=66) (actual time=0.039..0.040 rows=0 loops=1)
                     Filter: ((tenant_id = 105) AND (id = 7279) AND (created_at = '2020-10-06 19:09:32.9'::timestamp without time zone))
               ->  Index Scan using _hyper_3_27032_chunk_test_ht_created_at_idx on _hyper_3_27032_chunk test_ht_2  (cost=0.28..8.30 rows=1 width=67) (actual time=0.048..0.048 
rows=0 loops=1)
                     Index Cond: (created_at = '2020-10-06 19:09:32.9'::timestamp without time zone)
                     Filter: ((tenant_id = 105) AND (id = 7279))
 Planning Time: 8392.908 ms
 Execution Time: 129.956 ms
(11 rows)

DELETE

test2=# explain analyze
test2-# delete from test_ht where tenant_id = 105 and id = 36732 and created_at = '2020-10-06 19:09:32.9+00'::timestamp;
                                                                                          QUERY PLAN                                                                           

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------
 Custom Scan (HypertableModify)  (cost=0.28..1186.90 rows=143 width=10) (actual time=0.042..0.044 rows=0 loops=1)
   ->  Delete on test_ht  (cost=0.28..1186.90 rows=143 width=10) (actual time=0.042..0.044 rows=0 loops=1)
         Delete on _hyper_3_26963_chunk test_ht
         Delete on _hyper_3_26964_chunk test_ht
         Delete on _hyper_3_26965_chunk test_ht
         Delete on _hyper_3_26966_chunk test_ht
         Delete on _hyper_3_26967_chunk test_ht
         Delete on _hyper_3_26968_chunk test_ht
         Delete on _hyper_3_26969_chunk test_ht
         Delete on _hyper_3_26970_chunk test_ht
         Delete on _hyper_3_26971_chunk test_ht
         Delete on _hyper_3_26972_chunk test_ht
         Delete on _hyper_3_26973_chunk test_ht
         Delete on _hyper_3_26974_chunk test_ht
         Delete on _hyper_3_26975_chunk test_ht
         Delete on _hyper_3_26976_chunk test_ht
         Delete on _hyper_3_26977_chunk test_ht
         Delete on _hyper_3_26978_chunk test_ht
         Delete on _hyper_3_26979_chunk test_ht
         Delete on _hyper_3_26980_chunk test_ht
         Delete on _hyper_3_26981_chunk test_ht
         Delete on _hyper_3_26982_chunk test_ht
         Delete on _hyper_3_26983_chunk test_ht
         Delete on _hyper_3_26984_chunk test_ht
         Delete on _hyper_3_26985_chunk test_ht
         Delete on _hyper_3_26986_chunk test_ht
         Delete on _hyper_3_26987_chunk test_ht
         Delete on _hyper_3_26988_chunk test_ht
         Delete on _hyper_3_26989_chunk test_ht
         Delete on _hyper_3_26990_chunk test_ht
         Delete on _hyper_3_26991_chunk test_ht
         Delete on _hyper_3_26992_chunk test_ht
         Delete on _hyper_3_26993_chunk test_ht
         Delete on _hyper_3_26994_chunk test_ht
         Delete on _hyper_3_26995_chunk test_ht
         Delete on _hyper_3_26996_chunk test_ht
         Delete on _hyper_3_26997_chunk test_ht
         Delete on _hyper_3_26998_chunk test_ht
         Delete on _hyper_3_26999_chunk test_ht
         Delete on _hyper_3_27000_chunk test_ht
         Delete on _hyper_3_27001_chunk test_ht
         Delete on _hyper_3_27002_chunk test_ht
         Delete on _hyper_3_27003_chunk test_ht
         Delete on _hyper_3_27004_chunk test_ht
         Delete on _hyper_3_27005_chunk test_ht
         Delete on _hyper_3_27006_chunk test_ht
         Delete on _hyper_3_27007_chunk test_ht
         Delete on _hyper_3_27008_chunk test_ht
         Delete on _hyper_3_27009_chunk test_ht
         Delete on _hyper_3_27010_chunk test_ht
         Delete on _hyper_3_27011_chunk test_ht
         Delete on _hyper_3_27012_chunk test_ht
         Delete on _hyper_3_27013_chunk test_ht
         Delete on _hyper_3_27014_chunk test_ht
         Delete on _hyper_3_27015_chunk test_ht
         Delete on _hyper_3_27016_chunk test_ht
         Delete on _hyper_3_27017_chunk test_ht
         Delete on _hyper_3_27018_chunk test_ht
         Delete on _hyper_3_27019_chunk test_ht
         Delete on _hyper_3_27020_chunk test_ht
         Delete on _hyper_3_27021_chunk test_ht
         Delete on _hyper_3_27022_chunk test_ht
         Delete on _hyper_3_27023_chunk test_ht
         Delete on _hyper_3_27024_chunk test_ht
         Delete on _hyper_3_27025_chunk test_ht
         Delete on _hyper_3_27026_chunk test_ht
         Delete on _hyper_3_27027_chunk test_ht
         Delete on _hyper_3_27028_chunk test_ht
         Delete on _hyper_3_27029_chunk test_ht
         Delete on _hyper_3_27030_chunk test_ht
         Delete on _hyper_3_27031_chunk test_ht
         Delete on _hyper_3_27032_chunk test_ht_1
         Delete on _hyper_3_27033_chunk test_ht
         Delete on _hyper_3_27034_chunk test_ht
         Delete on _hyper_3_27035_chunk test_ht
         Delete on _hyper_3_27036_chunk test_ht
         Delete on _hyper_3_27037_chunk test_ht
         Delete on _hyper_3_27038_chunk test_ht
         Delete on _hyper_3_27039_chunk test_ht
         Delete on _hyper_3_27040_chunk test_ht
         Delete on _hyper_3_27041_chunk test_ht
         Delete on _hyper_3_27042_chunk test_ht
         Delete on _hyper_3_27043_chunk test_ht
         Delete on _hyper_3_27044_chunk test_ht
         Delete on _hyper_3_27045_chunk test_ht
         Delete on _hyper_3_27046_chunk test_ht
         Delete on _hyper_3_27047_chunk test_ht
         Delete on _hyper_3_27048_chunk test_ht
         Delete on _hyper_3_27049_chunk test_ht
         Delete on _hyper_3_27050_chunk test_ht
         Delete on _hyper_3_27051_chunk test_ht
         Delete on _hyper_3_27052_chunk test_ht
         Delete on _hyper_3_27053_chunk test_ht
         Delete on _hyper_3_27054_chunk test_ht
         Delete on _hyper_3_27055_chunk test_ht
         Delete on _hyper_3_27056_chunk test_ht
         Delete on _hyper_3_27057_chunk test_ht
         Delete on _hyper_3_27058_chunk test_ht
         Delete on _hyper_3_27059_chunk test_ht
         Delete on _hyper_3_27060_chunk test_ht
         Delete on _hyper_3_27061_chunk test_ht
         Delete on _hyper_3_27062_chunk test_ht
         Delete on _hyper_3_27063_chunk test_ht
         Delete on _hyper_3_27064_chunk test_ht
         Delete on _hyper_3_27065_chunk test_ht
         Delete on _hyper_3_27066_chunk test_ht
         Delete on _hyper_3_27067_chunk test_ht
         Delete on _hyper_3_27068_chunk test_ht
         Delete on _hyper_3_27069_chunk test_ht
         Delete on _hyper_3_27070_chunk test_ht
         Delete on _hyper_3_27071_chunk test_ht
         Delete on _hyper_3_27072_chunk test_ht
         Delete on _hyper_3_27073_chunk test_ht
         Delete on _hyper_3_27074_chunk test_ht
         Delete on _hyper_3_27075_chunk test_ht
         Delete on _hyper_3_27076_chunk test_ht
         Delete on _hyper_3_27077_chunk test_ht
         Delete on _hyper_3_27078_chunk test_ht
         Delete on _hyper_3_27079_chunk test_ht
         Delete on _hyper_3_27080_chunk test_ht
         Delete on _hyper_3_27081_chunk test_ht
         Delete on _hyper_3_27082_chunk test_ht
         Delete on _hyper_3_27083_chunk test_ht
         Delete on _hyper_3_27084_chunk test_ht
         Delete on _hyper_3_27085_chunk test_ht
         Delete on _hyper_3_27086_chunk test_ht
         Delete on _hyper_3_27087_chunk test_ht
         Delete on _hyper_3_27088_chunk test_ht
         Delete on _hyper_3_27089_chunk test_ht
         Delete on _hyper_3_27090_chunk test_ht
         Delete on _hyper_3_27091_chunk test_ht
         Delete on _hyper_3_27092_chunk test_ht
         Delete on _hyper_3_27093_chunk test_ht
         Delete on _hyper_3_27094_chunk test_ht
         Delete on _hyper_3_27095_chunk test_ht
         Delete on _hyper_3_27096_chunk test_ht
         Delete on _hyper_3_27097_chunk test_ht
         Delete on _hyper_3_27098_chunk test_ht
         Delete on _hyper_3_27099_chunk test_ht
         Delete on _hyper_3_27100_chunk test_ht
         Delete on _hyper_3_27101_chunk test_ht
         Delete on _hyper_3_27102_chunk test_ht
         Delete on _hyper_3_27103_chunk test_ht
         Delete on _hyper_3_27104_chunk test_ht
         Delete on _hyper_3_27105_chunk test_ht
         ->  Custom Scan (ChunkAppend) on test_ht  (cost=0.28..1186.90 rows=143 width=10) (actual time=0.039..0.040 rows=0 loops=1)
               Chunks excluded during startup: 142
               ->  Index Scan using _hyper_3_27032_chunk_test_ht_created_at_idx on _hyper_3_27032_chunk test_ht_1  (cost=0.28..8.30 rows=1 width=10) (actual time=0.037..0.038 
rows=0 loops=1)
                     Index Cond: (created_at = '2020-10-06 19:09:32.9'::timestamp without time zone)
                     Filter: ((tenant_id = 105) AND (id = 36732))
 Planning Time: 8396.622 ms
 Execution Time: 11.121 ms
(152 rows)

Normal Hypertable

Normal SELECT

test2=# explain analyze
select * from test_ht_no_part where tenant_id = 105 and id = 7279 and created_at = '2020-10-06 19:09:32.9+00'::timestamp limit 1;
                                                                                      QUERY PLAN                                                                               

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------
 Limit  (cost=0.42..8.86 rows=1 width=57) (actual time=0.079..0.081 rows=0 loops=1)
   ->  Custom Scan (ChunkAppend) on test_ht_no_part  (cost=0.42..1207.63 rows=143 width=57) (actual time=0.076..0.077 rows=0 loops=1)
         Chunks excluded during startup: 142
         ->  Index Scan using _hyper_6_42221_chunk_test_ht_no_part_created_at_idx on _hyper_6_42221_chunk  (cost=0.42..8.45 rows=1 width=57) (actual time=0.074..0.075 rows=0 l
oops=1)
               Index Cond: (created_at = '2020-10-06 19:09:32.9'::timestamp without time zone)
               Filter: ((tenant_id = 105) AND (id = 7279))
 Planning Time: 229.037 ms
 Execution Time: 8.938 ms
(8 rows)

SELECT with 'FOR UPDATE'


test2=# explain analyze
select * from test_ht_no_part where tenant_id = 105 and id = 7279 and created_at = '2020-10-06 19:09:32.9+00'::timestamp limit 1 for update;
                                                                                                  QUERY PLAN                                                                   

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------
 Limit  (cost=0.00..8.40 rows=1 width=67) (actual time=0.097..0.100 rows=0 loops=1)
   ->  LockRows  (cost=0.00..1209.07 rows=144 width=67) (actual time=0.094..0.097 rows=0 loops=1)
         ->  Custom Scan (ChunkAppend) on test_ht_no_part  (cost=0.00..1207.63 rows=144 width=67) (actual time=0.093..0.095 rows=0 loops=1)
               Chunks excluded during startup: 142
               ->  Seq Scan on test_ht_no_part test_ht_no_part_1  (cost=0.00..0.00 rows=1 width=66) (actual time=0.019..0.020 rows=0 loops=1)
                     Filter: ((tenant_id = 105) AND (id = 7279) AND (created_at = '2020-10-06 19:09:32.9'::timestamp without time zone))
               ->  Index Scan using _hyper_6_42221_chunk_test_ht_no_part_created_at_idx on _hyper_6_42221_chunk test_ht_no_part_2  (cost=0.42..8.45 rows=1 width=67) (actual ti
me=0.069..0.069 rows=0 loops=1)
                     Index Cond: (created_at = '2020-10-06 19:09:32.9'::timestamp without time zone)
                     Filter: ((tenant_id = 105) AND (id = 7279))
 Planning Time: 109.840 ms
 Execution Time: 11.789 ms
(11 rows)

DELETE

test2=# explain analyze delete from test_ht_no_part where tenant_id = 105 and id = 36732 and created_at = '2020-10-06 19:09:32.9+00'::timestamp;
                                                                                                  QUERY PLAN                                                                   

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------
 Custom Scan (HypertableModify)  (cost=0.42..1207.63 rows=143 width=10) (actual time=1.098..1.101 rows=0 loops=1)
   ->  Delete on test_ht_no_part  (cost=0.42..1207.63 rows=143 width=10) (actual time=1.098..1.101 rows=0 loops=1)
         Delete on _hyper_6_42121_chunk test_ht_no_part
         Delete on _hyper_6_42122_chunk test_ht_no_part
         Delete on _hyper_6_42123_chunk test_ht_no_part
         Delete on _hyper_6_42124_chunk test_ht_no_part
         Delete on _hyper_6_42125_chunk test_ht_no_part
         Delete on _hyper_6_42126_chunk test_ht_no_part
         Delete on _hyper_6_42127_chunk test_ht_no_part
         Delete on _hyper_6_42128_chunk test_ht_no_part
         Delete on _hyper_6_42129_chunk test_ht_no_part
         Delete on _hyper_6_42130_chunk test_ht_no_part
         Delete on _hyper_6_42131_chunk test_ht_no_part
         Delete on _hyper_6_42132_chunk test_ht_no_part
         Delete on _hyper_6_42133_chunk test_ht_no_part
         Delete on _hyper_6_42134_chunk test_ht_no_part
         Delete on _hyper_6_42135_chunk test_ht_no_part
         Delete on _hyper_6_42136_chunk test_ht_no_part
         Delete on _hyper_6_42137_chunk test_ht_no_part
         Delete on _hyper_6_42138_chunk test_ht_no_part
         Delete on _hyper_6_42139_chunk test_ht_no_part
         Delete on _hyper_6_42140_chunk test_ht_no_part
         Delete on _hyper_6_42141_chunk test_ht_no_part
         Delete on _hyper_6_42142_chunk test_ht_no_part
         Delete on _hyper_6_42143_chunk test_ht_no_part
         Delete on _hyper_6_42144_chunk test_ht_no_part
         Delete on _hyper_6_42145_chunk test_ht_no_part
         Delete on _hyper_6_42146_chunk test_ht_no_part
         Delete on _hyper_6_42147_chunk test_ht_no_part
         Delete on _hyper_6_42148_chunk test_ht_no_part
         Delete on _hyper_6_42149_chunk test_ht_no_part
         Delete on _hyper_6_42150_chunk test_ht_no_part
         Delete on _hyper_6_42151_chunk test_ht_no_part
         Delete on _hyper_6_42152_chunk test_ht_no_part
         Delete on _hyper_6_42153_chunk test_ht_no_part
         Delete on _hyper_6_42154_chunk test_ht_no_part
         Delete on _hyper_6_42155_chunk test_ht_no_part
         Delete on _hyper_6_42156_chunk test_ht_no_part
         Delete on _hyper_6_42157_chunk test_ht_no_part
         Delete on _hyper_6_42158_chunk test_ht_no_part
         Delete on _hyper_6_42159_chunk test_ht_no_part
         Delete on _hyper_6_42160_chunk test_ht_no_part
         Delete on _hyper_6_42161_chunk test_ht_no_part
         Delete on _hyper_6_42162_chunk test_ht_no_part
         Delete on _hyper_6_42163_chunk test_ht_no_part
         Delete on _hyper_6_42164_chunk test_ht_no_part
         Delete on _hyper_6_42165_chunk test_ht_no_part
         Delete on _hyper_6_42166_chunk test_ht_no_part
         Delete on _hyper_6_42167_chunk test_ht_no_part
         Delete on _hyper_6_42168_chunk test_ht_no_part
         Delete on _hyper_6_42169_chunk test_ht_no_part
         Delete on _hyper_6_42170_chunk test_ht_no_part
         Delete on _hyper_6_42171_chunk test_ht_no_part
         Delete on _hyper_6_42172_chunk test_ht_no_part
         Delete on _hyper_6_42173_chunk test_ht_no_part
         Delete on _hyper_6_42174_chunk test_ht_no_part
         Delete on _hyper_6_42175_chunk test_ht_no_part
         Delete on _hyper_6_42176_chunk test_ht_no_part
         Delete on _hyper_6_42177_chunk test_ht_no_part
         Delete on _hyper_6_42178_chunk test_ht_no_part
         Delete on _hyper_6_42179_chunk test_ht_no_part
         Delete on _hyper_6_42180_chunk test_ht_no_part
         Delete on _hyper_6_42181_chunk test_ht_no_part
         Delete on _hyper_6_42182_chunk test_ht_no_part
         Delete on _hyper_6_42183_chunk test_ht_no_part
         Delete on _hyper_6_42184_chunk test_ht_no_part
         Delete on _hyper_6_42185_chunk test_ht_no_part
         Delete on _hyper_6_42186_chunk test_ht_no_part
         Delete on _hyper_6_42187_chunk test_ht_no_part
         Delete on _hyper_6_42188_chunk test_ht_no_part
         Delete on _hyper_6_42189_chunk test_ht_no_part
         Delete on _hyper_6_42190_chunk test_ht_no_part
         Delete on _hyper_6_42191_chunk test_ht_no_part
         Delete on _hyper_6_42192_chunk test_ht_no_part
         Delete on _hyper_6_42193_chunk test_ht_no_part
         Delete on _hyper_6_42194_chunk test_ht_no_part
         Delete on _hyper_6_42195_chunk test_ht_no_part
         Delete on _hyper_6_42196_chunk test_ht_no_part
         Delete on _hyper_6_42197_chunk test_ht_no_part
         Delete on _hyper_6_42198_chunk test_ht_no_part
         Delete on _hyper_6_42199_chunk test_ht_no_part
         Delete on _hyper_6_42200_chunk test_ht_no_part
         Delete on _hyper_6_42201_chunk test_ht_no_part
         Delete on _hyper_6_42202_chunk test_ht_no_part
         Delete on _hyper_6_42203_chunk test_ht_no_part
         Delete on _hyper_6_42204_chunk test_ht_no_part
         Delete on _hyper_6_42205_chunk test_ht_no_part
         Delete on _hyper_6_42206_chunk test_ht_no_part
         Delete on _hyper_6_42207_chunk test_ht_no_part
         Delete on _hyper_6_42208_chunk test_ht_no_part
         Delete on _hyper_6_42209_chunk test_ht_no_part
         Delete on _hyper_6_42210_chunk test_ht_no_part
         Delete on _hyper_6_42211_chunk test_ht_no_part
         Delete on _hyper_6_42212_chunk test_ht_no_part
         Delete on _hyper_6_42213_chunk test_ht_no_part
         Delete on _hyper_6_42214_chunk test_ht_no_part
         Delete on _hyper_6_42215_chunk test_ht_no_part
         Delete on _hyper_6_42216_chunk test_ht_no_part
         Delete on _hyper_6_42217_chunk test_ht_no_part
         Delete on _hyper_6_42218_chunk test_ht_no_part
         Delete on _hyper_6_42219_chunk test_ht_no_part
         Delete on _hyper_6_42220_chunk test_ht_no_part
         Delete on _hyper_6_42221_chunk test_ht_no_part_1
         Delete on _hyper_6_42222_chunk test_ht_no_part
         Delete on _hyper_6_42223_chunk test_ht_no_part
         Delete on _hyper_6_42224_chunk test_ht_no_part
         Delete on _hyper_6_42225_chunk test_ht_no_part
         Delete on _hyper_6_42226_chunk test_ht_no_part
         Delete on _hyper_6_42227_chunk test_ht_no_part
         Delete on _hyper_6_42228_chunk test_ht_no_part
         Delete on _hyper_6_42229_chunk test_ht_no_part
         Delete on _hyper_6_42230_chunk test_ht_no_part
         Delete on _hyper_6_42231_chunk test_ht_no_part
         Delete on _hyper_6_42232_chunk test_ht_no_part
         Delete on _hyper_6_42233_chunk test_ht_no_part
         Delete on _hyper_6_42234_chunk test_ht_no_part
         Delete on _hyper_6_42235_chunk test_ht_no_part
         Delete on _hyper_6_42236_chunk test_ht_no_part
         Delete on _hyper_6_42237_chunk test_ht_no_part
         Delete on _hyper_6_42238_chunk test_ht_no_part
         Delete on _hyper_6_42239_chunk test_ht_no_part
         Delete on _hyper_6_42240_chunk test_ht_no_part
         Delete on _hyper_6_42241_chunk test_ht_no_part
         Delete on _hyper_6_42242_chunk test_ht_no_part
         Delete on _hyper_6_42243_chunk test_ht_no_part
         Delete on _hyper_6_42244_chunk test_ht_no_part
         Delete on _hyper_6_42245_chunk test_ht_no_part
         Delete on _hyper_6_42246_chunk test_ht_no_part
         Delete on _hyper_6_42247_chunk test_ht_no_part
         Delete on _hyper_6_42248_chunk test_ht_no_part
         Delete on _hyper_6_42249_chunk test_ht_no_part
         Delete on _hyper_6_42250_chunk test_ht_no_part
         Delete on _hyper_6_42251_chunk test_ht_no_part
         Delete on _hyper_6_42252_chunk test_ht_no_part
         Delete on _hyper_6_42253_chunk test_ht_no_part
         Delete on _hyper_6_42254_chunk test_ht_no_part
         Delete on _hyper_6_42255_chunk test_ht_no_part
         Delete on _hyper_6_42256_chunk test_ht_no_part
         Delete on _hyper_6_42257_chunk test_ht_no_part
         Delete on _hyper_6_42258_chunk test_ht_no_part
         Delete on _hyper_6_42259_chunk test_ht_no_part
         Delete on _hyper_6_42260_chunk test_ht_no_part
         Delete on _hyper_6_42261_chunk test_ht_no_part
         Delete on _hyper_6_42262_chunk test_ht_no_part
         Delete on _hyper_6_42263_chunk test_ht_no_part
         ->  Custom Scan (ChunkAppend) on test_ht_no_part  (cost=0.42..1207.63 rows=143 width=10) (actual time=1.093..1.094 rows=0 loops=1)
               Chunks excluded during startup: 142
               ->  Index Scan using _hyper_6_42221_chunk_test_ht_no_part_created_at_idx on _hyper_6_42221_chunk test_ht_no_part_1  (cost=0.42..8.45 rows=1 width=10) (actual ti
me=1.091..1.091 rows=0 loops=1)
                     Index Cond: (created_at = '2020-10-06 19:09:32.9'::timestamp without time zone)
                     Filter: ((tenant_id = 105) AND (id = 36732))
 Planning Time: 64.722 ms
 Execution Time: 7.621 ms
(152 rows)
svenklemm commented 2 years ago

Seems like most of the time is spent in postgres table inheritance code and not in timescaledb code: out