hw3-queries.sql and the hw3-solution.sql. (Q&A)

Document Content and Description Below

hw3-solution.sql -- hw3-queries.sql -- homework 3 sample solution (queries and indexes) -- 1. -- Qa. For each origin city, find the destination city (or cities) that is farthest away (i.e., lo ... ngest direct flight). Show the name of the origin city, destination city, and the flight time between them. -- There are at least 3 ways to write this query. This query is an example of "finding the witness". See end of lecture 07. -- Qb. Find all origin cities that only serve flights shorter than 3 hours. List each city only once in the result. -- Qc. For each origin city, find the percentage of departing flights shorter than 3 hours. -- There are different options to display 0 instead of NULL for cities without any flights < 3 hours. -- You could pick either NULL or zero. select P.origin_city, COALESCE(1.0* T.total_short / P.total,0) from on T.origin_city = P.origin_city order by 1.0* T.total_short / P.total; -- Qd. List all cities that cannot be reached from MN though a direct flight but can be reached with one stop. -- 2. -- For the question on indexes, we will accept any answer that is soundly analyzed -- a) origin_city -- all of the queries ask for a specific value of origin_city, so an index will make that lookup faster (there are many cities, so a small percentage of records will be accessed) create index on Flights (origin_city) -- we can also list the indexes \di -- time the query again after the index is made explain analyze SELECT DISTINCT carrier_id FROM Flights WHERE origin_city = 'Cedar Rapids/Iowa City IA' AND actual_time <= 180; -- chosen physical plan without index -- QUERY PLAN -- ------------------------------------------------------------------------------------------------------------------------ -- HashAggregate (cost=38699.76..38699.78 rows=1 width=3) -- -> Seq Scan on flights (cost=0.00..38698.12 rows=656 width=3) -- Filter: ((actual_time <= 180::double precision) AND ((origin_city)::text = 'Cedar Rapids/Iowa City IA'::text)) -- chosen physical plan with index -- QUERY PLAN -- ----------------------------------------------------------------------------------------------- -- HashAggregate (cost=2833.10..2833.11 rows=1 width=3) -- -> Bitmap Heap Scan on flights (cost=22.85..2831.46 rows=656 width=3) -- Recheck Cond: ((origin_city)::text = 'Cedar Rapids/Iowa City IA'::text) -- Filter: (actual_time <= 180::double precision) -- -> Bitmap Index Scan on flights_origin_city_idx (cost=0.00..22.69 rows=834 width=0) -- Index Cond: ((origin_city)::text = 'Cedar Rapids/Iowa City IA'::text) -- Index on actual_time can accelerate Q1 through Q3. It is helpful for Q1 but not really for the others because it is non-clustered and we do a pretty big range selection. -- d) index on dest_city: PostgreSQL picked a different join algorithm that utilizes the index on dest_city. The estimated cost (EXPLAIN) was lower, but the running time (EXPLAIN ANALYZE) was similar. -- e) index on dest_city: does not have an effect on Qa-Qc because there is no selection on. Qd does refer to dest_city, but the query, which uses negation, is already very expensive. [Show More]

Last updated: 3 years ago

Preview 1 out of 3 pages