How to reduce the cost of choosing sql on order?

Question

I am doing a lot of sql queries as follows. Imagine that we have a database with flights, where each flight can have an outgoing and incoming airport, for example, the date of departure, the number of stops between travel and destination (on long flights) and, of course, the price.

Now I want to choose a specific route and choose the one that has the lowest number of stops and, of course, the best of them.

CREATE TABLE flights(
    id integer
    outbound character varying,
    inbound character varying,
    date timestamp,
    stops integer
    price numeric
);
CREATE INDEX my_idx ON flights (outbound, inbound, date, stops, price);

select * from flights where outbound = 'SFO' and inbound = 'SYD' and date = '2015-10-10' and stops < 2 order by stops asc, price asc.

Problem: The costs of using are explain-analyzequite high:

Sort  (cost=9.78..9.79 rows=1 width=129) (actual time=0.055..0.055 rows=4 loops=1)
  Sort Key: stops, price
  Sort Method: quicksort  Memory: 26kB
  ->  Index Scan using my_idx  (cost=0.42..9.77 rows=1 width=129) (actual time=0.039..0.041 rows=4 loops=1)
        Index Cond: ((date = '2015-10-10'::date) AND ((outbound)::text = 'SFO'::text) AND (stops < 2) AND ((inbound)::text = 'SYD'::text))
Total runtime: 0.079 ms

If I just sort by price non-stop, the costs will be fine (0.42). But sorting by stops somehow increases the cost significantly.

How can I cut costs?

postgresql 9.3.2

Answer 1

, ( " " ) , . 0.079 ms 0.42 (?).

, .

. price . : time=0.055..0.055, .

, . , ( ), .

, stops < 2 ( 0 1 ), , ( ) .

, , id, < > (Postgres 9.2+, Postgres Wiki ):

CREATE INDEX my_idx ON flights (outbound, inbound, date, stops, price, id);

SELECT id, outbound, inbound, date, stops, price
FROM ...

Answer 2

:

select * 
from flights
where outbound = 'SFO' and inbound = 'SYD' and date = '2015-10-10' and stops < 2
order by stops asc, price asc.

: flights(outbound, inbound, date, stops). where. , order by, where, , .

Answer 3

- .

, - , , .

Your query is great for quick sorting. These are just four lines, and it completes the entire request in 0.079 ms.