sql - Prevent full table scan for query with multiple where clauses -


Some time ago I posted a message about customizing a query in MySQL. I have ported the data and queryed PostgreSQL, but now there is only one problem in PostgreSQL. PostGrace SQL does not offer any such options to emphasize the Optimizer to optimize the solution in MySQL to optimize STREIGHT_JOIN.

Modified updates

I have separated the question resolving the problem ( d.month_ref_id = 1 ): < / P> 

  select d. * Daily D to MID = d.month_ref_id on y_id = m.year_ref_id add to year_ref y m.category_id = '001' and d.month_ref_id = 1

However, I can not reference a month of reference to 1 , which generates a full table scan: 

  Select D. * Join daily D to MID = d.month_ref_id at year_ref_id y_id = m.year_ref_id y c.category_id = '001'

index daily.month_ref_id Is: 

  Daily_month_ref_idx on BitTree using the Climate Index (month_ref_id);

Why is the query to scan a full table and what can be done to avoid it?

Thank you!

  1. Even though it can not make a performance difference, I'm in tables rather than cross johns To join and where the clause
  2. Where you are saying a segment in which the system will cause a table scan. It will not make any difference to what database you use, it is coming true.
  3. Why join the Left City? Do you know for a fact that the given ID will be present (in this case 10663? If so, then you should use internal interiors.
  4. About the way you prepare the query The compiler can give the signal using brackets (I'm not sure Postgres will respect them).
Select average (d.mount) AS amount, from y.year ( Join Station S Left City - You have to cross over to join the city. A wants to join an inner c.id = 10,663 and 6371.009 * SQRT (PADA (RADIANS (c.latitude_decimal) - 2) + (store controller (RADIANS .latitude_decimal) / 2) * join the RADIANS (c.longitude_decimal - s.longitude_decimal), 2))) & lt; = 50) join the station_district sd.id = s.station_district_id at year_ref y at y. Join daily d at station_district_id = sd.id j oin month_ref meter m.year_ref_id = y.id at d.month_ref_id = m.id where between 0 and 2000 and y.year between s.elevation 1980 and 2000 and m.month = 12 And m.category_id = 'between 001' and d.daily_flag_id & lt; & Gt; 'Em group' y.year

Since you are not using stations, station_district nor national tables in results, you are able to transfer those people to an existing statement Can be: 

  choose the average (d.amount) sum, join y.year y month_ref meter from year_ref, join daily d on m.year_ref_id = y.id d. Month_ref_id = m.id where y.year between 1980 and 2000 and mmth = 12 and m. Series_ID = '001' and DDRII flag_ID & lt; & Gt; Select 'M' and Existence (S1 c1.id = 10,663 where 6371.009 * SQRT (select the city c1 from c1.latitude_decimal at station (1 - s1.latitude_decimal), 2) + + (RADIANS (c1 .latitude_decimal + s1.latitude_decimal) / 2) * POW (RADIANS (c1.longitude_decimal - s1.longitude_decimal), 2))) lt; = 50 and S1.station_district_id = y.station_district_id) depending on the group y.year < / Code>

Comments

Post a Comment