PostgreSQL - How make simple query faster 2

Question

How to improve this query? There already is this index:

CREATE INDEX idx_sensor_date 
    ON temperatures USING btree (sensor_id, audit_date)

Date format in PHP

$date2 = date("Y-m-d H:i:s");

Query:

select 
    s.sensor_id,
    audit_date::timestamp(0) as audit_date,
    t.val,
    s.comment
from 
    temperatures t, 
    sensors s
where 
    t.audit_date >= timestamp '$date1'
    and t.audit_date <= timestamp '$date2'
    and t.sensor_id in (select sensor_id from users_sensors us, users u 
                        where us.user_id = u.user_id and u.login = '$login')
    and t.sensor_id = s.sensor_id
order by 2;

Execution plan:

Sort  (cost=3124.99..3165.35 rows=16143 width=23) (actual time=176.460..194.994 rows=79702 loops=1)
  Sort Key: ((t.audit_date)::timestamp(0) without time zone)
  Sort Method: external merge  Disk: 2960kB
  Buffers: shared hit=1608, temp read=372 written=372
  ->  Nested Loop  (cost=460.22..1996.71 rows=16143 width=23) (actual time=9.842..97.750 rows=79702 loops=1)
        Buffers: shared hit=1606
        ->  Nested Loop  (cost=2.12..3.24 rows=1 width=15) (actual time=0.047..0.081 rows=3 loops=1)
              Join Filter: (s.sensor_id = us.sensor_id)
              Buffers: shared hit=5
              ->  HashAggregate  (cost=2.12..2.13 rows=1 width=4) (actual time=0.034..0.037 rows=3 loops=1)
                    Buffers: shared hit=2
                    ->  Nested Loop  (cost=0.00..2.12 rows=1 width=4) (actual time=0.027..0.030 rows=3 loops=1)
                          Join Filter: (us.user_id = u.user_id)
                          Buffers: shared hit=2
                          ->  Seq Scan on users u  (cost=0.00..1.05 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=1)
                                Filter: (login = 'Wojtek'::text)
                                Buffers: shared hit=1
                          ->  Seq Scan on users_sensors us  (cost=0.00..1.03 rows=3 width=8) (actual time=0.005..0.008 rows=8 loops=1)
                                Buffers: shared hit=1
              ->  Seq Scan on sensors s  (cost=0.00..1.05 rows=5 width=11) (actual time=0.004..0.006 rows=5 loops=3)
                    Buffers: shared hit=3
        ->  Bitmap Heap Scan on temperatures t  (cost=458.10..1710.96 rows=16143 width=16) (actual time=9.315..14.846 rows=26567 loops=3)
              Recheck Cond: ((sensor_id = s.sensor_id) AND (audit_date >= to_date('2019-11-15 13:00:00'::text, 'YYYY-MM-DD HH24:mi:ss'::text)) AND (audit_date < to_date('2019-11-22'::text, 'YYYY-MM-DD'::text)))
              Buffers: shared hit=1601
              ->  Bitmap Index Scan on idx_sensor_date  (cost=0.00..454.07 rows=16143 width=0) (actual time=9.236..9.236 rows=26567 loops=3)
                    Index Cond: ((sensor_id = s.sensor_id) AND (audit_date >= to_date('2019-11-15 13:00:00'::text, 'YYYY-MM-DD HH24:mi:ss'::text)) AND (audit_date < to_date('2019-11-22'::text, 'YYYY-MM-DD'::text)))
                    Buffers: shared hit=318
Total runtime: 199.598 ms

JOIN! JOIN! JOIN! That won't affect performance but your query should be using modern SQL syntax. — Gordon Linoff
– Gordon Linoff, Commented Nov 22, 2019 at 13:10
Is sensors#sensor_id a primary key column? Have you checked the execution plan to see what is going on? — Tim Biegeleisen
– Tim Biegeleisen, Commented Nov 22, 2019 at 13:12
would you consider making a fiddle? also please let us know how our proposed solutions work compared to your original query — xception
– xception, Commented Nov 22, 2019 at 13:56

score 1 · Accepted Answer · 2019-11-22 13:41:49Z

1

Without seeing the execution plan (generated using explain (analyze, buffers)) it's hard to tell, but very often an EXISTS condition is faster than an IN.

select 
    s.sensor_id,
    audit_date::timestamp(0) as audit_date,
    t.val,
    s.comment
from temperatures t 
 join sensors s ON t.sensor_id = s.sensor_id
where t.audit_date >= timestamp '$date1'
  and t.audit_date <= timestamp '$date2'
  and exists (select *
              from user_sensors us
                join users on us.user_id = u.user_id
              where us.sensor_id = s.sensor_id 
              and u.login = '$login')
order by 2;

The sorting is done on disk ("external merge Disk: 2960kB") because work_mem is configured too small.

You can run set work_mem='16MB' before running the query and see if that helps. If the sorting is still done on disk, increase it even further.

edited Nov 22, 2019 at 13:41

answered Nov 22, 2019 at 13:14

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

WuBe Over a year ago

I need to have lists of sensors for gived login, because logins have sensors connected with them.

user330315 Over a year ago

@WuBe: this query will return the same as your query.

xception · Accepted Answer · 2019-11-22 13:53:41Z

Maybe this helps, it's hard to tell without an actual data structure, index information and so on.

with q as (
    select sensor_id
        from user_sensors
        join users on user_sensors.user_id = users.user_id
        where user.login = '$login'
) select 
        s.sensor_id,
        audit_date::timestamp(0) as audit_date,
        t.val,
        s.comment
    from
        temperatures as t,
        join q on q.sensor_id = s.sensor_id
        join sensors as s on s.sensor_id = t.sensor_id
    where
        t.audit_date >= timestamp '$date1'
        and t.audit_date <= timestamp '$date2';

it can probably be further improved by getting the sensors.comment field with a join in the initial query and dropping the join of sensors below.

with q as (
    select s.sensor_id, s.comment
        from user_sensors as us
        join users on us.user_id = users.user_id
        join sensors as s on s.sensor_id = us.sensor_id
        where user.login = '$login'
) select 
        q.sensor_id,
        audit_date::timestamp(0) as audit_date,
        t.val,
        q.comment
    from
        temperatures as t,
        join q on q.sensor_id = t.sensor_id
    where
        t.audit_date >= timestamp '$date1'
        and t.audit_date <= timestamp '$date2';

Collectives™ on Stack Overflow

PostgreSQL - How make simple query faster 2

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related