2

I'm using PostgreSQL 9.6 and have the following table:

CREATE TABLE eco.tracks ( 
    id                   bigserial  NOT NULL,
    time_track           timestamp  NOT NULL,
    track                bigint  ,
    distance_1             int  ,

    CONSTRAINT pk_tracks PRIMARY KEY ( id )
 );

And I manipulate data it contains with a very simple query:

  SELECT (track, distance_1, time_track) 
    FROM eco.tracks 
   WHERE time_track - (%s) <=  INTERVAL '10 seconds' 
     AND (%s)-time_track <=  INTERVAL '10 seconds' 
     AND distance_1 IS NOT NULL 
ORDER BY distance_1 asc LIMIT 1;

Some inputs:

INSERT INTO tracks (id, time_track, track, distance_1) VALUES (23467, '2018-05-18 20:43:06', 386, 17965);
INSERT INTO tracks (id, time_track, track, distance_1) VALUES (23468, '2018-05-18 20:43:07', 386, 18168);
INSERT INTO tracks (id, time_track, track, distance_1) VALUES (23469, '2018-05-18 20:43:08', 386, 18168);
INSERT INTO tracks (id, time_track, track, distance_1) VALUES (22974, '2018-05-18 20:40:07', 376, 8394);
INSERT INTO tracks (id, time_track, track, distance_1) VALUES (22968, '2018-05-18 20:40:00', 376, 9051);
INSERT INTO tracks (id, time_track, track, distance_1) VALUES (22837, '2018-05-18 20:39:17', 366, 13133);

This query is used to take the closest track number & other two params in range of setted time +- 10sec. But in my system, this one is a bottleneck, is it possible to improve it?

I've tried to play around with value work_mem, no success.

Actually, I use it for checking like if-else. And then make another query. Can I merge it?

    SELECT (track, distance_1, aircraft_time)
      FROM eco.tracks WHERE time_track - (%s) <=  INTERVAL '10 seconds' and (%s)-time_track <=  INTERVAL '10 seconds'  ORDER BY distance_1 asc LIMIT 1,
    CASE track WHEN NOT NULL THEN
    (
    INSERT INTO eco.noise (
    time_noise, track, distance, aircraft_time)
      VALUES (%s, track, distance_1, aircraft_time) ON CONFLICT DO NOTHING;
    )

    OR

 WITH check_1 AS
  (SELECT track, distance_1, aircraft_time FROM eco.tracks
 WHERE time_track - (%s) <=  INTERVAL '10 seconds' and (%s)-time_track <=  INTERVAL '10 seconds'  ORDER BY distance_1 asc LIMIT 1)
 INSERT INTO eco.noise (time_noise, track, distance, aircraft_time)
  VALUES (%s, check_1.track, check_1.distance_1, check_1.aircraft_time) 
 WHERE
 check_1.track IS NOT NULL ON CONFLICT DO NOTHING;
                )


Note: I use it in Python 3 by using psycopg2.

  • 1
    Unrelated, but: do not put the columns in the select list between parentheses. select (track, distance_1, time_track) returns a single column that is an anonymous record with multiple fields. select track, distance_1, time_track returns multiple columns – a_horse_with_no_name May 19 '18 at 21:55
  • 1
    Try to change time_track - (%s) <= INTERVAL '10 seconds' to time_track <= (%s) + INTERVAL '10 seconds' so indexes can be used. And similarly for the other conditiion. – ypercubeᵀᴹ May 20 '18 at 1:08
  • what's that bit at the end with "case" for... the syntax looks bad. – Jasen May 20 '18 at 5:28
2

assuming you have an index on time_track

move the offsets to the other side of the inequality so that the arithmetic is only done once and so that an index on time_track can be used.

SELECT (track, distance_1, aircraft_time)
  FROM eco.tracks 
  WHERE time_track >= (%s) + INTERVAL '10 seconds'
    and time_track <= (%s) - INTERVAL '10 seconds'
ORDER BY distance_1 asc LIMIT 1,

etc...

or you could use BETWEEN instead of and.

SELECT (track, distance_1, aircraft_time)
  FROM eco.tracks 
  WHERE time_track BETWEEN
        (%s) - INTERVAL '10 seconds'
    AND (%s) + INTERVAL '10 seconds'
ORDER BY distance_1 asc LIMIT 1,
0

You can determine the time interval in the application and use a query like:

SELECT (track, distance_1, time_track) 
FROM tracks 
WHERE time_track BETWEEN (%s) AND (%s)
  AND distance_1 IS NOT NULL 
ORDER BY distance_1 asc
LIMIT 1;

An index like:

CREATE INDEX ... (time_track, distance_1, track) ON tracks

will help

  • you can. so long as the application understands time the same as postgres. eg:timestamps are discontinuous at the ends of daylight savings – Jasen May 20 '18 at 5:23
  • i don't see a reason to index time_track twice in the same index, or how distance_1 would help, unless the timestamps are very clumpy. – Jasen May 20 '18 at 5:24
  • distance_1 is used in the order by clause, it may help to find the first row – Lennart May 20 '18 at 5:32
  • it wont help because you're taking a range of time_track. with two separate indexes it could do a bitmap thing if there are enough results, but there probably aren't. – Jasen May 20 '18 at 5:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.