Postgresql – How to speed up simple query

performancepostgresqlpostgresql-9.6query-performance

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.}

Best Answer

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,

Related Solutions

MySQL optimization question — how to speed up a simple query

Here is your original query

SELECT t.price
FROM commoddb.tbcommodprices t
WHERE t.ticker='LAV05 Comdty'
AND t.whichprice='last'
t.date = '2005-09-01';

Since there is but one table in this query, all you need is a good index.

ALTER TABLE commoddb.tbcommodprices ADD INDEX (ticker,whichprice,date);

This index should now be a permanent part of the table until you decide to remove it, which I don't think you want to do. To see the table and the indexes, just do this:

SHOW CREATE TABLE commoddb.tbcommodprices\G

Mysql – How to speed up this simple query

This is as you say a very simple query, so the optimal index here is very clear. Thanks for showing the whole table structure, indexes and full query, not all people do that.

You indexes are in the good direction, but you must know that (in general, there are other query plans like index_merge) a table scan can only use one index. In this case, as you are filtering by two columns, you have to add the multi-column index:

ALTER TABLE data_dylos ADD INDEX (node_id, added_on);

You have to create it on that order (node_id, added_on) and not in reverse (added_on, node_id), as that would be a different index. The difference is that in a BTREE index, after a range join type is used, the rest of the index is useless.

That is the optimal index query-wise. If you continue to have performance problems you may want to review your configuration (specially innodb_buffer_pool_size).

There are additional techniques that may help you, like caching or partitioning, but those are more advanced, or may require more structure changes, so try this first.

Best Answer

Related Solutions

MySQL optimization question — how to speed up a simple query

Mysql – How to speed up this simple query

Related Question