0

I'm currently trying to optimize the following query for a school assignment:

SELECT
    f.aircraft_code,
    s.seat_no,
    SUM(tf.amount) total_amount
FROM seats s
JOIN flights f ON s.aircraft_code = f.aircraft_code
JOIN ticket_flights tf ON tf.flight_id = f.flight_id
WHERE
    DATE(f.scheduled_departure) BETWEEN '2017-01-26' AND '2017-02-26'
GROUP BY f.aircraft_code, s.seat_no;

Without creating any indexes or auxiliary structures, the scheduler gives out the following execution plan for it

|QUERY PLAN                                                                                                                                       |
|-------------------------------------------------------------------------------------------------------------------------------------------------|
|GroupAggregate  (cost=1458346.16..1531458.23 rows=3688 width=39)                                                                                 |
|  Group Key: f.aircraft_code, s.seat_no                                                                                                          |
|  ->  Merge Join  (cost=1458346.16..1507072.50 rows=3245283 width=13)                                                                            |
|        Merge Cond: (s.aircraft_code = f.aircraft_code)                                                                                          |
|        ->  Index Only Scan using seats_pkey on seats s  (cost=0.28..44.36 rows=1339 width=7)                                                    |
|        ->  Sort  (cost=1458345.88..1458450.74 rows=41946 width=10)                                                                              |
|              Sort Key: f.aircraft_code                                                                                                          |
|              ->  Merge Join  (cost=1412749.50..1455125.21 rows=41946 width=10)                                                                  |
|                    Merge Cond: (f.flight_id = tf.flight_id)                                                                                     |
|                    ->  Sort  (cost=6975.41..6978.09 rows=1074 width=8)                                                                          |
|                          Sort Key: f.flight_id                                                                                                  |
|                          ->  Seq Scan on flights f  (cost=0.00..6921.34 rows=1074 width=8)                                                      |
|                                Filter: ((date(scheduled_departure) >= '2017-01-26'::date) AND (date(scheduled_departure) <= '2017-02-26'::date))|
|                    ->  Sort  (cost=1405769.91..1426749.54 rows=8391852 width=10)                                                                |
|                          Sort Key: tf.flight_id                                                                                                 |
|                          ->  Seq Scan on ticket_flights tf  (cost=0.00..153851.52 rows=8391852 width=10)                                        |

For my current solution, I've created the following indexes

CREATE INDEX idx_flights_ac_fid_sd ON flights USING btree(aircraft_code, flight_id, scheduled_departure);
CREATE INDEX idx_ticket_flights_fid ON ticket_flights USING hash(flight_id);

The essential logic is that the manager can then execute the first Join operation iterating over the B-Tree index over flights and accessing the rows it needs from ticket_flights using the hash, generating a result set that is already sorted by aircraft_code and eliminating the need to re-sort the intermediate result to fulfill the last JOIN and GROUP BY statements.

With these indexes I get the following execution plan:

|QUERY PLAN                                                                                                                                 |
|-------------------------------------------------------------------------------------------------------------------------------------------|
|GroupAggregate  (cost=0.70..336154.55 rows=3688 width=39)                                                                                  |
|  Group Key: f.aircraft_code, s.seat_no                                                                                                    |
|  ->  Merge Join  (cost=0.70..311768.83 rows=3245283 width=13)                                                                             |
|        Merge Cond: (s.aircraft_code = f.aircraft_code)                                                                                    |
|        ->  Index Only Scan using seats_pkey on seats s  (cost=0.28..44.36 rows=1339 width=7)                                              |
|        ->  Materialize  (cost=0.42..263147.06 rows=41946 width=10)                                                                        |
|              ->  Nested Loop  (cost=0.42..263042.20 rows=41946 width=10)                                                                  |
|                    ->  Index Only Scan using idx_flights_ac_fid_sd on flights f  (cost=0.42..8688.09 rows=1074 width=8)                   |
|                          Filter: ((date(scheduled_departure) >= '2017-01-26'::date) AND (date(scheduled_departure) <= '2017-02-26'::date))|
|                    ->  Index Scan using idx_ticket_flights_fid on ticket_flights tf  (cost=0.00..235.80 rows=103 width=10)                |
|                          Index Cond: (flight_id = f.flight_id)                                                                            |

The materialize node is my main problem, as it is really slowing things down when I try to fetch more rows (for 200 rows the query takes about a second, for 2000 it starts taking around 17), and I really have no idea why Postgres is feeling the need to Materialize that specific intermediate result as the same tactic has worked for other similar queries without Materialization needed.

I currently have enable_material set to OFF and have also tried increasing work_mem and effective_cache_size to something bigger like 4GB, but the Materialize node keeps tormenting me. Why is PostgreSQL so adamant about Materializing and how can I work around this problem?

Note: I feel it's pertinent to say that I don't want the answer to my homework in case my solution is completely off-mark, I just want to get an explanation for this specific Materialization issue.

New contributor
sotnififu is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
2
  • 1
    Without EXPLAIN (ANALYZE, BUFFERS, SETTINGS) output, we can only guess. I guess that the "Materialize" node is not your problem. The duration is caused by the lots of data you have to process and the bad WHERE condition that you should rewrite so that it can be indexed. Commented Jul 9 at 7:11
  • Are you saying it keeps using materialize even when enable_material is set to off? I would need to see some evidence for that.
    – jjanes
    Commented Jul 10 at 20:47

0

Your Answer

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