On Jul 21, 2015, at 8:30 AM, Mohit Sindhwani <mo_m...@onghu.com> wrote: > > select count(*), count(distinct group_id) from > (select group_id, recorded_on from data_store_v2 order by id DESC limit > 900000) td > where recorded_on >= (now() - '24 hr'::INTERVAL);
Some suggestions in a slightly different direction: 1) rewrite without that completely unnecessary subquery, and see if the query time improves ;-) 2) check that appropriate indexes exist and are being used by the optimizer 3) really analyze the query execution plan, and look for more advanced opportunities, for instance, order by recorded_on instead of id, since, presumably, the query will already access the rows by an index on ordered_on; consider dropping the limit & order altogether; take that query to a PostgreSQL list... 4) put some effort into learning SQL better; OK, we all make mistakes sometimes and maybe this is just that; but it sure looks to me like someone who doesn't really understand SQL struggling at throwing together various clauses until the correct answer pops out (note that in addition to the issue I pointed out in 1, the subquery is selecting a column which is completely unused--probably doesn't affect anything, but just another sign that the person writing the query did not understand it). -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ https://www.linkedin.com/in/scottribe/ (303) 722-0567 voice -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscr...@googlegroups.com. To post to this group, send email to rubyonrails-talk@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/ACD46065-302A-421B-986F-A1A94E0641DC%40elevated-dev.com. For more options, visit https://groups.google.com/d/optout.
