1

Considering this schema for a time series fights database named OpenSky:

SET search_path = public;
CREATE EXTENSION IF NOT EXISTS POSTGIS;


DROP TABLE IF EXISTS "FlightsSummary" CASCADE;
CREATE TABLE IF NOT EXISTS "FlightsSummary"
(
    "flight_number" SERIAL,
    "start_time" BIGINT,
    "end_time" BIGINT,
    "aircraft_id" INTEGER NOT NULL,
    PRIMARY KEY("flight_number"),
    UNIQUE("start_time", "end_time", "aircraft_id"),
    CHECK("end_time" > "start_time")
);

CREATE INDEX IF NOT EXISTS "flights_summary_composite_time_idx" ON "FlightsSummary" ("start_time", "end_time") INCLUDE("flight_number", "aircraft_id");


DROP TABLE IF EXISTS "FlightsRoutes" CASCADE;
CREATE TABLE IF NOT EXISTS "FlightsRoutes"
(
    "flight_number" BIGINT,
    "time" BIGINT,
    "position" GEOMETRY(PointZ, 4326),
    PRIMARY KEY("flight_number", "time"),
    CHECK("position" IS NOT NULL),
    CONSTRAINT flight_number_fk FOREIGN KEY ("flight_number") REFERENCES "FlightsSummary" ("flight_number")
) PARTITION BY RANGE("time");

CREATE UNIQUE INDEX IF NOT EXISTS "flights_routes_time_flight_number_idx" ON "FlightsRoutes" ("time" DESC, "flight_number") INCLUDE("position");


DROP TABLE IF EXISTS "AircraftsData" CASCADE;
CREATE TABLE IF NOT EXISTS "AircraftsData"
(
    "time" BIGINT,
    "aircraft_id" INTEGER,
    "flight_number" BIGINT,
    "position" GEOMETRY(PointZ, 4326),
    PRIMARY KEY("time", "aircraft_id"),
    CONSTRAINT flight_number_fk FOREIGN KEY ("flight_number") REFERENCES "FlightsSummary" ("flight_number")
) PARTITION BY RANGE("time");

CREATE INDEX IF NOT EXISTS "aircrafts_data_composite_time_idx" ON "AircraftsData" ("flight_number", "time" DESC);
CREATE INDEX IF NOT EXISTS "aircrafts_data_position_idx" ON "AircraftsData" USING GIST ("position");


DROP TABLE IF EXISTS "GlobalStatistics" CASCADE;
CREATE TABLE IF NOT EXISTS "GlobalStatistics"
(
    "max_flight_duration" INTEGER
);

DROP FUNCTION IF EXISTS get_max_flight_duration();
CREATE OR REPLACE FUNCTION get_max_flight_duration()
RETURNS INTEGER
AS $BODY$
    SELECT "max_flight_duration" FROM "GlobalStatistics" LIMIT 1;
$BODY$ LANGUAGE sql IMMUTABLE PARALLEL SAFE;

ERD:

enter image description here

And current data cardinality and distribution as:

OpenSky=# SELECT COUNT(*) FROM "FlightsSummary";
 count  
--------
 306422
(1 row)

OpenSky=# SELECT COUNT(*) FROM "FlightsRoutes";
  count   
----------
 46670451
(1 row)


OpenSky=# SELECT COUNT(*) FROM "AircraftsData";
  count   
----------
 46670451
(1 row)


OpenSky=# SELECT COUNT(DISTINCT "aircraft_id") FROM "AircraftsData";
 count 
-------
 59788
(1 row)

OpenSky=# SELECT to_timestamp(MIN("time"))::TIMESTAMP, to_timestamp(MAX("time"))::TIMESTAMP FROM "AircraftsData";
    to_timestamp     |    to_timestamp     
---------------------+---------------------
 2020-05-25 07:30:10 | 2021-11-01 06:29:50
(1 row)

OpenSky=# SELECT get_max_flight_duration();
get_max_flight_duration 
-------------------------
                6740
(1 row)

The business requirement is as follow which will be querying frequently:

Having time point T,
Maximum flight duration as TMAX,
Query A -> Find all online flights from FlightsSummary at time T (in time range [T - TMAX, T + TMAX]),
Query B -> Find the last status (relative to time) of each flight from AircraftsData
Final Query -> if the result from B satisfy given filters AND flight number is in A,
Select * from B and trajectory from FlightsRoutes for each record before time T

What has been done so far (T = 1607366101):

SELECT
    AD."aircraft_id" AS "aircraft_id",
    AD."time" AS "time",
    AD."position" AS "position",
    FR."trajectory" AS "trajectory"
FROM
    (
        SELECT
            DISTINCT ON("flight_number") "flight_number",
            "time",
            "aircraft_id",
            "position"
        FROM
            "AircraftsData"
        WHERE
            "time" BETWEEN 1607366101 - get_max_flight_duration() AND 1607366101
            AND
            "flight_number" = ANY
            (
                ARRAY
                (
                    SELECT
                        "flight_number"
                    FROM
                        "FlightsSummary"
                    WHERE
                        "start_time" BETWEEN 1607366101 - get_max_flight_duration() AND 1607366101
                        AND
                        "end_time" BETWEEN 1607366101 AND 1607366101 + get_max_flight_duration()
                )
            )
        ORDER BY
            "flight_number", "time" DESC
    ) AD,
    (
        SELECT
            "flight_number",
            ARRAY_AGG("position" ORDER BY "time" DESC) AS trajectory
        FROM
            "FlightsRoutes"
        WHERE
            "time" BETWEEN 1607366101 - get_max_flight_duration() AND 1607366101
        GROUP BY
            "flight_number"
    ) FR
WHERE
    AD."flight_number" = FR."flight_number"
    AND
    ST_Contains(ST_MakeEnvelope(-1.1426, 63.3127, 86.8359, 11.8674, 4326), AD."position")

AND explain (analyze,COSTS,verbose,BUFFERS, FORMAT text):

Merge Join  (cost=84459.60..191612.77 rows=32 width=112) (actual time=897.588..1521.124 rows=596 loops=1)                                                                                                                                                 |
  Output: ad.aircraft_id, ad."time", ad."position", (array_agg("FlightsRoutes"."position" ORDER BY "FlightsRoutes"."time" DESC))                                                                                                                          |
  Inner Unique: true                                                                                                                                                                                                                                      |
  Merge Cond: (ad.flight_number = "FlightsRoutes".flight_number)                                                                                                                                                                                          |
  Buffers: shared hit=798707 read=32129, temp read=22349 written=22409                                                                                                                                                                                    |
  ->  Subquery Scan on ad  (cost=3796.62..30262.49 rows=1 width=88) (actual time=547.769..657.828 rows=596 loops=1)                                                                                                                                       |
        Output: ad.flight_number, ad."time", ad.aircraft_id, ad."position"                                                                                                                                                                                |
        Filter: st_contains('0103000020E61000000100000005000000B98D06F01648F2BFC7BAB88D06A84F40B98D06F01648F2BFCE1951DA1BBC27407DAEB6627FB55540CE1951DA1BBC27407DAEB6627FB55540C7BAB88D06A84F40B98D06F01648F2BFC7BAB88D06A84F40'::geometry, ad."position")|
        Rows Removed by Filter: 3605                                                                                                                                                                                                                      |
        Buffers: shared hit=9835 read=20946, temp read=9953 written=9977                                                                                                                                                                                  |
        ->  Unique  (cost=3796.62..3801.91 rows=1058 width=88) (actual time=547.748..656.058 rows=4201 loops=1)                                                                                                                                           |
              Output: "AircraftsData".flight_number, "AircraftsData"."time", "AircraftsData".aircraft_id, "AircraftsData"."position"                                                                                                                      |
              Buffers: shared hit=9835 read=20946, temp read=9953 written=9977                                                                                                                                                                            |
              InitPlan 1 (returns $0)                                                                                                                                                                                                                     |
                ->  Index Only Scan using flights_summary_composite_time_idx on public."FlightsSummary"  (cost=0.42..202.41 rows=119 width=4) (actual time=0.153..0.852 rows=4201 loops=1)                                                                |
                      Output: "FlightsSummary".flight_number                                                                                                                                                                                              |
                      Index Cond: (("FlightsSummary".start_time >= 1607359361) AND ("FlightsSummary".start_time <= 1607366101) AND ("FlightsSummary".end_time >= 1607366101) AND ("FlightsSummary".end_time <= 1607372841))                               |
                      Heap Fetches: 0                                                                                                                                                                                                                     |
                      Buffers: shared hit=1 read=38                                                                                                                                                                                                       |
              ->  Sort  (cost=3594.21..3596.86 rows=1058 width=88) (actual time=547.746..623.683 rows=590045 loops=1)                                                                                                                                     |
                    Output: "AircraftsData".flight_number, "AircraftsData"."time", "AircraftsData".aircraft_id, "AircraftsData"."position"                                                                                                                |
                    Sort Key: "AircraftsData".flight_number, "AircraftsData"."time" DESC                                                                                                                                                                  |
                    Sort Method: external merge  Disk: 54936kB                                                                                                                                                                                            |
                    Buffers: shared hit=9835 read=20946, temp read=9953 written=9977                                                                                                                                                                      |
                    ->  Bitmap Heap Scan on public."AircraftsData_1607310010" "AircraftsData"  (cost=57.79..3541.06 rows=1058 width=88) (actual time=72.583..198.109 rows=590045 loops=1)                                                                 |
                          Output: "AircraftsData".flight_number, "AircraftsData"."time", "AircraftsData".aircraft_id, "AircraftsData"."position"                                                                                                          |
                          Recheck Cond: (("AircraftsData".flight_number = ANY ($0)) AND ("AircraftsData"."time" >= 1607359361) AND ("AircraftsData"."time" <= 1607366101))                                                                                |
                          Heap Blocks: exact=15836                                                                                                                                                                                                        |
                          Buffers: shared hit=9835 read=20946                                                                                                                                                                                             |
                          ->  Bitmap Index Scan on "AircraftsData_1607310010_flight_number_time_idx"  (cost=0.00..57.52 rows=1058 width=0) (actual time=70.125..70.125 rows=590045 loops=1)                                                               |
                                Index Cond: (("AircraftsData".flight_number = ANY ($0)) AND ("AircraftsData"."time" >= 1607359361) AND ("AircraftsData"."time" <= 1607366101))                                                                            |
                                Buffers: shared hit=9835 read=5110                                                                                                                                                                                        |
  ->  GroupAggregate  (cost=80662.98..160924.12 rows=34067 width=40) (actual time=349.811..861.688 rows=7115 loops=1)                                                                                                                                     |
        Output: "FlightsRoutes".flight_number, array_agg("FlightsRoutes"."position" ORDER BY "FlightsRoutes"."time" DESC)                                                                                                                                 |
        Group Key: "FlightsRoutes".flight_number                                                                                                                                                                                                          |
        Buffers: shared hit=788872 read=11183, temp read=12396 written=12432                                                                                                                                                                              |
        ->  Gather Merge  (cost=80662.98..157211.97 rows=657262 width=80) (actual time=349.676..535.205 rows=882564 loops=1)                                                                                                                              |
              Output: "FlightsRoutes".flight_number, "FlightsRoutes"."position", "FlightsRoutes"."time"                                                                                                                                                   |
              Workers Planned: 2                                                                                                                                                                                                                          |
              Workers Launched: 1                                                                                                                                                                                                                         |
              Buffers: shared hit=788872 read=11183, temp read=12396 written=12432                                                                                                                                                                        |
              ->  Sort  (cost=79662.95..80347.60 rows=273859 width=80) (actual time=299.324..366.000 rows=441384 loops=2)                                                                                                                                 |
                    Output: "FlightsRoutes".flight_number, "FlightsRoutes"."position", "FlightsRoutes"."time"                                                                                                                                             |
                    Sort Key: "FlightsRoutes".flight_number                                                                                                                                                                                               |
                    Sort Method: external merge  Disk: 45928kB                                                                                                                                                                                            |
                    Buffers: shared hit=788872 read=11183, temp read=12396 written=12432                                                                                                                                                                  |
                    Worker 0:  actual time=249.201..303.597 rows=361820 loops=1                                                                                                                                                                           |
                      Sort Method: external merge  Disk: 31888kB                                                                                                                                                                                          |
                      JIT:                                                                                                                                                                                                                                |
                        Functions: 4                                                                                                                                                                                                                      |
                        Options: Inlining false, Optimization false, Expressions true, Deforming true                                                                                                                                                     |
                        Timing: Generation 0.457 ms, Inlining 0.000 ms, Optimization 0.154 ms, Emission 2.878 ms, Total 3.489 ms                                                                                                                          |
                      Buffers: shared hit=323699 read=4582, temp read=4526 written=4541                                                                                                                                                                   |
                    ->  Parallel Index Only Scan using flights_routes_time_flight_number_idx on public."FlightsRoutes"  (cost=0.56..42759.78 rows=273859 width=80) (actual time=1.612..152.233 rows=441500 loops=2)                                       |
                          Output: "FlightsRoutes".flight_number, "FlightsRoutes"."position", "FlightsRoutes"."time"                                                                                                                                       |
                          Index Cond: (("FlightsRoutes"."time" >= 1607359361) AND ("FlightsRoutes"."time" <= 1607366101))                                                                                                                                 |
                          Heap Fetches: 0                                                                                                                                                                                                                 |
                          Buffers: shared hit=788866 read=11181                                                                                                                                                                                           |
                          Worker 0:  actual time=3.189..131.177 rows=361820 loops=1                                                                                                                                                                       |
                            Buffers: shared hit=323693 read=4580                                                                                                                                                                                          |
Planning:                                                                                                                                                                                                                                                 |
  Buffers: shared hit=4 read=4                                                                                                                                                                                                                            |
Planning Time: 1.127 ms                                                                                                                                                                                                                                   |
JIT:                                                                                                                                                                                                                                                      |
  Functions: 24                                                                                                                                                                                                                                           |
  Options: Inlining false, Optimization false, Expressions true, Deforming true                                                                                                                                                                           |
  Timing: Generation 4.188 ms, Inlining 0.000 ms, Optimization 1.042 ms, Emission 16.005 ms, Total 21.235 ms                                                                                                                                              |
Execution Time: 1538.879 ms                                                                                                                                                                                                                               |
System setup:
  • PostgreSQL 14.1 (Debian 14.1-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

Can we have a query rewrite with a much lower cost and execution time? Or a better data model in general?

5
  • 2
    There is a lot here to digest in a free consultation. But it immediately strikes me that your row estimates are off, and your work_mem is too low for this type of work. Freshly VACUUM ANALYZE all involved tables, increase work_mem to at least 200MB (just for this one session, if that is too high to adopt in general) and add a new plan for that. Also, set max_parallel_workers_per_gather=0, again just for this one session if you don't want to do that globally. Non-parallel plans are easier to think about, even if in the end you do want to use a parallel one.
    – jjanes
    Dec 10, 2021 at 19:10
  • Yes row numbers are far away from correct estimates, BTW this is a plan after a full vacuum analyze (via vacuumdb utility) on database!, and about the work_mem, my intention was to first run query without touching the server configuration at the early stage,but will try it ASAP
    – IMAN4K
    Dec 11, 2021 at 5:22
  • @jjanes. And what do you think about data model OR database design? Could it be improved further?
    – IMAN4K
    Dec 11, 2021 at 5:23
  • 1
    Is it just an amazing coincidence that your two large tables have the exact same number of rows? That doesn't seem to be enforced by your model, but it is too unlikely to be an accident. If they really are in one to one correspondence, the why have them as separate tables? Also, the whole kerfuffle with get_max_flight_duration seems to be an attempt to make btree indexes efficient at range overlap queries. But GiST indexes are better for that, without the artificial hoops to jump through.
    – jjanes
    Dec 11, 2021 at 6:44
  • The main point of that separate table(which is bundled with a covering index) is for trajectory calculation without touching the main table for a second time. need to get detail about GiST in my use case
    – IMAN4K
    Dec 11, 2021 at 7:05

1 Answer 1

0

It's unclear exactly how your schema fits together: why does AircraftsData and FlightsRoutes have the same amount of rows, and appear to contain the same data?

Either way: there is no reason to use = ANY (ARRAY (... on the FlightSummary semi-join. You can use a normal IN subquery.

You can move the PostGIS condition to the inside of the subquery.

You should also use proper JOIN syntax, rather than , joins and WHERE (this was deprecated 30 years ago).

SELECT
    AD."aircraft_id" AS "aircraft_id",
    AD."time" AS "time",
    AD."position" AS "position",
    FR."trajectory" AS "trajectory"
FROM
    (
        SELECT
            DISTINCT ON("flight_number") "flight_number",
            "time",
            "aircraft_id",
            "position"
        FROM
            "AircraftsData"
        WHERE
            "time" BETWEEN 1607366101 - get_max_flight_duration() AND 1607366101
            AND
            "flight_number" IN
            (
                    SELECT
                        "flight_number"
                    FROM
                        "FlightsSummary"
                    WHERE
                        "start_time" BETWEEN 1607366101 - get_max_flight_duration() AND 1607366101
                        AND
                        "end_time" BETWEEN 1607366101 AND 1607366101 + get_max_flight_duration()
            )
            AND
            ST_Contains(ST_MakeEnvelope(-1.1426, 63.3127, 86.8359, 11.8674, 4326), AD."position")
        ORDER BY
            "flight_number", "time" DESC
    ) AD
JOIN
    (
        SELECT
            "flight_number",
            ARRAY_AGG("position" ORDER BY "time" DESC) AS trajectory
        FROM
            "FlightsRoutes"
        WHERE
            "time" BETWEEN 1607366101 - get_max_flight_duration() AND 1607366101
        GROUP BY
            "flight_number"
    ) FR
  ON AD."flight_number" = FR."flight_number"; 

With the correct indexing, hopefully these changes should improve the query planner's ability to get a good plan

1
  • AircraftsData is the main table(which may have other fields such as callsign as well), and FlightsRoutes is the snapshot table which gets filled by a trigger from the main table, and its use case is for trajectory calculation without touching the main table twice(FlightsRoutes has a covering index).and we need to only filter the last position before T so we can't move PostGIS filter into the subquery which in turn filter all positions
    – IMAN4K
    Dec 12, 2021 at 18:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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