Hi Scott,
On 23/7/2015 8:12 PM, Scott Ribe wrote:
Because it was the count(distinct x) that was the problem :)
Doing only a count(*) is faster without the subquery... and is what we have
switched to.
So does the limit reduce it to less than 1 day's rows?
Yes, the idea of the limit (900k records) was to limit it to the records
within 1 day and then run the query on the smaller record set. However,
after your comments, when I was looking around, I resolved that the
slowness is because of the count (distinct x) and not because of the
indexes, etc.
For reference, on a 230million record table, the numbers were roughly
along these lines:
* SELECT count(*), count (distinct group_id) with a limit of 900k
records based on ID DESC, followed by the recorded_on part = 8.6 seconds
* SELECT count(*), count (distinct group_id) on the whole table using
only recorded_on in the WHERE = 14 seconds
* SELECT count(*) only with a limit of 900k records based on ID DESC,
followed by the recorded_on part = 700ms
* SELECT count(*) on the whole table using only recorded_on in the WHERE
= 350ms
--> Clearly, the culprit was the count (distinct group_id) - that
benefits a lot by using a subquery to limit the number of records it
considers
* SELECT count(*) from (select distinct group_id from data_store_v2
where recorded_on >= '') --> takes around 900ms
So, we are combining these in the final query now... this takes around
900ms to get both values (count and count distinct)
-- get the fields from 2 different subqueries
select * from
-- first field is got for the count(*)
(select count(*) AS all_count from data_store_v2 where recorded_on >=
(now() AT TIME ZONE 'Asia/Singapore' - '24 hr'::INTERVAL)) as t1,
-- and Joining in the second one for the count (distinct group_id)
(select count(*) from
-- this is yet another subquery
(select distinct drive_id from data_store_v2 where recorded_on >=
(now() AT TIME ZONE 'Asia/Singapore' - '24 hr'::INTERVAL)) td ) as t2;
But as I mentioned, this is now a PostrgreSQL question not an
ActiveRecord or Rails question :)
Thanks for digging with me!
Best Regards,
Mohit.
--
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/55B0E89C.8090501%40onghu.com.
For more options, visit https://groups.google.com/d/optout.
