这些SQL查询的执行时间是否相同？ [英] Are execution times of these SQL queries the same?

问题描述

我相信这两个查询获得的结果是相同的吗？

I believe the result obtained by these 2 queries is the same?

第一个查询：

SELECT 
  sensor_id,
  measurement_time,
  measurement_value
FROM 
  public.measurement_pm2_5
  WHERE (sensor_id = 12 AND measurement_time BETWEEN to_timestamp(3000) AND to_timestamp(12000))
  OR (sensor_id = 27 AND measurement_time BETWEEN to_timestamp(3000) AND to_timestamp(12000))
  OR (sensor_id = 1 AND measurement_time BETWEEN to_timestamp(500) AND to_timestamp(1000))
  OR (sensor_id = 1 AND measurement_time BETWEEN to_timestamp(6000) AND to_timestamp(9000));

第二个查询：

SELECT 
  sensor_id,
  measurement_time,
  measurement_value
FROM 
  public.measurement_pm2_5
  WHERE (sensor_id in (12,27) AND measurement_time BETWEEN to_timestamp(3000) AND to_timestamp(12000))
  OR (sensor_id = 1 AND ((measurement_time BETWEEN to_timestamp(500) AND to_timestamp(1000)) OR (measurement_time BETWEEN to_timestamp(6000) AND to_timestamp(9000))));

执行时间如何？

第一个查询：

Start-up Cost: 0
Total Cost: 580.56
Number of Rows: 1
Row Width: 18
Start-up Time: 2.676
Total Time: 2.676
Real Number of Rows: 0
Loops: 1

Hash Join  (cost=0.10..280.06 rows=115 width=18) (actual time=8.596..8.596 rows=0 loops=1)
  Hash Cond: (p.sensor_id = "*VALUES*".column1)
  Join Filter: ((p.measurement_time >= to_timestamp(("*VALUES*".column2)::double precision)) AND (p.measurement_time <= to_timestamp(("*VALUES*".column3)::double precision)))
  Rows Removed by Join Filter: 590
  ->  Seq Scan on measurement_pm2_5 p  (cost=0.00..207.39 rows=12439 width=18) (actual time=0.010..2.558 rows=12443 loops=1)
  ->  Hash  (cost=0.05..0.05 rows=4 width=12) (actual time=0.017..0.017 rows=4 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        ->  Values Scan on "*VALUES*"  (cost=0.00..0.05 rows=4 width=12) (actual time=0.002..0.003 rows=4 loops=1)
Planning time: 0.148 ms
Execution time: 8.627 ms

第二个查询：

Start-up Cost: 0
Total Cost: 456.17
Number of Rows: 1
Row Width: 18
Start-up Time: 2.237
Total Time: 2.237
Real Number of Rows: 0
Loops: 1

Hash Join  (cost=0.10..280.06 rows=115 width=18) (actual time=8.596..8.596 rows=0 loops=1)
  Hash Cond: (p.sensor_id = "*VALUES*".column1)
  Join Filter: ((p.measurement_time >= to_timestamp(("*VALUES*".column2)::double precision)) AND (p.measurement_time <= to_timestamp(("*VALUES*".column3)::double precision)))
  Rows Removed by Join Filter: 590
  ->  Seq Scan on measurement_pm2_5 p  (cost=0.00..207.39 rows=12439 width=18) (actual time=0.010..2.558 rows=12443 loops=1)
  ->  Hash  (cost=0.05..0.05 rows=4 width=12) (actual time=0.017..0.017 rows=4 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        ->  Values Scan on "*VALUES*"  (cost=0.00..0.05 rows=4 width=12) (actual time=0.002..0.003 rows=4 loops=1)
Planning time: 0.148 ms
Execution time: 8.627 ms

@Mike的查询：

Hash Join  (cost=0.10..280.06 rows=115 width=18) (actual time=8.596..8.596 rows=0 loops=1)
  Hash Cond: (p.sensor_id = "*VALUES*".column1)
  Join Filter: ((p.measurement_time >= to_timestamp(("*VALUES*".column2)::double precision)) AND (p.measurement_time <= to_timestamp(("*VALUES*".column3)::double precision)))
  Rows Removed by Join Filter: 590
  ->  Seq Scan on measurement_pm2_5 p  (cost=0.00..207.39 rows=12439 width=18) (actual time=0.010..2.558 rows=12443 loops=1)
  ->  Hash  (cost=0.05..0.05 rows=4 width=12) (actual time=0.017..0.017 rows=4 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        ->  Values Scan on "*VALUES*"  (cost=0.00..0.05 rows=4 width=12) (actual time=0.002..0.003 rows=4 loops=1)
Planning time: 0.148 ms
Execution time: 8.627 ms

问题是，这两个查询之间的时间执行差异是否重大当在大型数据库上进行这些查询时？

The question is, if the difference in time execution between these two queries is significant when these queries are made on large database?

推荐答案

EXPLAIN ANALYZE //将第一个查询粘贴到此处

EXPLAIN ANALYZE // paste first query here

例如： EXPLAIN ANALYZE select * from employee;

您将获得有关以下内容的完整说明您的查询以及每个子查询花费的时间。

you will get full explanation about your query and time taken by each sub query in details.

这篇关于这些SQL查询的执行时间是否相同？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！