gistfile1.txt

--- query plan with MySQL 8.0.21

mysql> show create table cpu\G
*************************** 1. row ***************************
       Table: cpu
Create Table: CREATE TABLE `cpu` (
  `time` timestamp NOT NULL,
  `tags_id` bigint NOT NULL,
  `additional_tags` text,
  `usage_user` double NOT NULL,
  `usage_system` double NOT NULL,
  `usage_idle` double NOT NULL,
  `usage_nice` double NOT NULL,
  `usage_iowait` double NOT NULL,
  `usage_irq` double NOT NULL,
  `usage_softirq` double NOT NULL,
  `usage_steal` double NOT NULL,
  `usage_guest` double NOT NULL,
  `usage_guest_nice` double DEFAULT NULL,
  KEY `x_tags_time` (`tags_id`,`time` DESC),
  KEY `x_time` (`time` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> explain SELECT * FROM cpu WHERE usage_user > 90.0 and time >= '2016-01-01 00:26:02' AND time < '2016-01-01 12:26:02' AND tags_id=3 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cpu
   partitions: NULL
         type: range
possible_keys: x_tags_time,x_time
          key: x_tags_time
      key_len: 12
          ref: NULL
         rows: 4320
     filtered: 33.33
        Extra: Using index condition; Using where
1 row in set, 1 warning (0.00 sec)

mysql> explain SELECT * FROM cpu WHERE usage_user > 90.0 and time >= '2016-01-01 00:26:02' AND time < '2016-01-01 12:26:02' AND tags_id IN (SELECT id FROM tags WHERE hostname IN ('host_2')) \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tags
   partitions: NULL
         type: ref
possible_keys: PRIMARY,uniq1,xhost
          key: uniq1
      key_len: 259
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: cpu
   partitions: NULL
         type: ref
possible_keys: x_tags_time,x_time
          key: x_tags_time
      key_len: 8
          ref: test.tags.id
         rows: 3112
     filtered: 11.56
        Extra: Using index condition; Using where
2 rows in set, 1 warning (0.00 sec)

--- query plan with Postgres 12.3


benchmark=# explain SELECT * FROM cpu WHERE usage_user > 90.0 and time >= '2016-01-01 00:26:02' AND time < '2016-01-01 12:26:02' AND tags_id IN (SELECT id FROM tags WHERE hostname IN ('host_2'));
                                                                       QUERY PLAN                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=8.59..2581.68 rows=20 width=124)
   Hash Cond: (cpu.tags_id = tags.id)
   ->  Index Scan using cpu_time_idx on cpu  (cost=0.42..2561.26 rows=4583 width=124)
         Index Cond: (("time" >= '2016-01-01 00:26:02-08'::timestamp with time zone) AND ("time" < '2016-01-01 12:26:02-08'::timestamp with time zone))
         Filter: (usage_user > '90'::double precision)
   ->  Hash  (cost=8.16..8.16 rows=1 width=4)
         ->  Index Scan using tags_hostname_idx on tags  (cost=0.14..8.16 rows=1 width=4)
               Index Cond: (hostname = 'host_2'::text)
(8 rows)