Rails - Jul 2013 - ActiveRecord's pluck gives an un-ordered list?

Hello all!

Background: I have a table consisting of roughly 14k rows running on 
postgresql. I want to do this in a performance friendly way.

y TimedAsset.count
   (1.6ms)  SELECT COUNT(*) FROM "timed_assets"
--- 13982


y TimedAsset.pluck(:id).count
   (7.5ms)  SELECT "timed_assets"."id" FROM
"timed_assets"
--- 13982

This shows me that pluck is indeed going over all the rows.

y TimedAsset.first.id
  TimedAsset Load (0.7ms)  SELECT "timed_assets".* FROM
"timed_assets"
ORDER BY "timed_assets"."id" ASC LIMIT 1
--- 44

Shows me the first row contains an ID of 44

y TimedAsset.pluck(:id).first(5)
   (7.2ms)  SELECT "timed_assets"."id" FROM
"timed_assets"
---
- 5700
- 5701
- 5702
- 5703
- 5704

I would expect the first ID would be 44, not 5700, so I assume that pluck 
gives me an un-ordered list?

y TimedAsset.order("id").pluck(:id).last(10)
   (14.4ms)  SELECT "timed_assets"."id" FROM
"timed_assets" ORDER BY id
---
- 14047
- 14048
- 14049
- 14050
- 14051
- 14052
- 14053
- 14054
- 14055
- 14056

So instead, it seems I have to order the entire table first, then pluck, 
and then getting the last few rows. This seems extremely inefficient since 
I have to order all rows of it first, even though I am specifically using 
pluck over select for performance and memory reasons. Does anyone have any 
suggestions or ideas for a higher performance method of getting the a 
specific column of the last few rows of a table ordered by the primary key? 
I also extremely reccomend mentioning in the pluck API documentation that 
pluck returns an un-ordered list so others do not fall into the same 
mistake I did.


-- 
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+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To view this discussion on the web visit
https://groups.google.com/d/msgid/rubyonrails-talk/2dbaf2d2-9fdc-40ab-9513-c01e723709ad%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

On Saturday, July 27, 2013 4:22:26 AM UTC+1, marcin longlastname
wrote:
>
> So instead, it seems I have to order the entire table first, then pluck, 
> and then getting the last few rows. This seems extremely inefficient since 
> I have to order all rows of it first, even though I am specifically using 
> pluck over select for performance and memory reasons. Does anyone have any 
> suggestions or ideas for a higher performance method of getting the a 
> specific column of the last few rows of a table ordered by the primary key?
> I also extremely reccomend mentioning in the pluck API documentation that 
> pluck returns an un-ordered list so others do not fall into the same 
> mistake I did.
>
>  Timed.asset.order(''id desc'').limit(10).pluck(:id)
In general if you haven''t specified an order then no order is applied. 
First is an exception rather than the rule here.

Fred

-- 
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+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To view this discussion on the web visit
https://groups.google.com/d/msgid/rubyonrails-talk/73c5de19-3dc8-4d59-b628-050722f3656d%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

It took a while for this to show on on groups, resulting in me going to 
sleep before I could update this.

It seems that rails actually cache''s active record queries? Or
postgres?
Here is an example of what I am referring to.
http://i.imgur.com/d360MF6.png
If this is true, I might not even have to really worry that much about 
doing this that much!

Anyways, after doing some experimentation within the rails console (hoping 
to avoid possible rails caching) and using what Fredrick recommended, here 
are my results.

TimedAsset.order("id").pluck(:id).last(30)            <-- my old
method
 (62.5, 16.6, 18.5, 15.6, 43.6, 15.5, 17.5)

SELECT "timed_assets"."id" FROM "timed_assets"
ORDER BY id

TimedAsset.order("id desc").limit(30).pluck(:id)   <--
frederick''s method
(1.0, 1.6, 0.9, 0.9, 0.9, 12.4, 1.6)

SELECT "timed_assets"."id" FROM "timed_assets"
ORDER BY id desc LIMIT 200

 

TimedAsset.order("id").pluck(:id).last(200)           <-- my old
method
 (16.8, 18.5, 15.5, 15.6, 47.7, 17.7, 17.8)

SELECT "timed_assets"."id" FROM "timed_assets"
ORDER BY id

TimedAsset.order("id desc").limit(200).pluck(:id)  <--
frederick''s method
    (1.1, 1.7, 1.7, 1.0, 1.7, 1.9,  1.8)

SELECT "timed_assets"."id" FROM "timed_assets"
ORDER BY id desc LIMIT 200
   

 

Postgres documentation for LIMIT: 
http://www.postgresql.org/docs/9.2/static/queries-limit.html
 
I should look at the postgres/rails documentation more to see how limit and 
pluck actually work! From what I see, my method orders all the rows within 
postgres, postgres gets the ID column, and then rails throws out everything 
except the last 200 rows. Frederick''s method does the same, but the
extra
rows are thrown out within postgres instead. I assume that the speed bump 
is because postgres handles the disposal of the extra rails?

Anyways, thank you a whole bunch for the help frederick!
 

-- 
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+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To view this discussion on the web visit
https://groups.google.com/d/msgid/rubyonrails-talk/45ec3375-c030-42a9-8e77-ad953daa0511%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

Woops, looks like I can''t edit my last post. The units are in
milliseconds.

So TimedAsset.order("id").pluck(:id).last(200)           <-- my old
method
     (16.8, 18.5, 15.5, 15.6, 47.7, 17.7, 17.8)
means that I did that query in rails console 7 times, and the duration for 
each query was 16.8 milliseconds, 18.5 milliseconds .... 17.8 milliseconds.

This is on a ubuntu server virtual machine (1 core) within vmware on an 
I5-3570k.

On Saturday, July 27, 2013 2:58:51 PM UTC-4, marcin longlastname
wrote:
>
> It took a while for this to show on on groups, resulting in me going to 
> sleep before I could update this.
>
> It seems that rails actually cache''s active record queries? Or
postgres?
> Here is an example of what I am referring to.
> http://i.imgur.com/d360MF6.png
> If this is true, I might not even have to really worry that much about 
> doing this that much!
>
> Anyways, after doing some experimentation within the rails console (hoping 
> to avoid possible rails caching) and using what Fredrick recommended, here 
> are my results.
>
> TimedAsset.order("id").pluck(:id).last(30)            <-- my
old method
>    (62.5, 16.6, 18.5, 15.6, 43.6, 15.5, 17.5)
>
> SELECT "timed_assets"."id" FROM
"timed_assets" ORDER BY id
>
> TimedAsset.order("id desc").limit(30).pluck(:id)   <--
frederick''s method
> (1.0, 1.6, 0.9, 0.9, 0.9, 12.4, 1.6)
>
> SELECT "timed_assets"."id" FROM
"timed_assets" ORDER BY id desc LIMIT 200
>
>  
>
> TimedAsset.order("id").pluck(:id).last(200)           <-- my
old method
>    (16.8, 18.5, 15.5, 15.6, 47.7, 17.7, 17.8)
>
> SELECT "timed_assets"."id" FROM
"timed_assets" ORDER BY id
>
> TimedAsset.order("id desc").limit(200).pluck(:id)  <--
frederick''s method
>     (1.1, 1.7, 1.7, 1.0, 1.7, 1.9,  1.8)
>
> SELECT "timed_assets"."id" FROM
"timed_assets" ORDER BY id desc LIMIT 200
>    
>
>  
>
> Postgres documentation for LIMIT: 
> http://www.postgresql.org/docs/9.2/static/queries-limit.html
>  
> I should look at the postgres/rails documentation more to see how limit 
> and pluck actually work! From what I see, my method orders all the rows 
> within postgres, postgres gets the ID column, and then rails throws out 
> everything except the last 200 rows. Frederick''s method does the
same, but
> the extra rows are thrown out within postgres instead. I assume that the 
> speed bump is because postgres handles the disposal of the extra rails?
>
> Anyways, thank you a whole bunch for the help frederick!
>  
>
-- 
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+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To view this discussion on the web visit
https://groups.google.com/d/msgid/rubyonrails-talk/46dc6f64-0024-4137-8868-3f670de68892%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

>
> Postgres documentation for LIMIT: 
> http://www.postgresql.org/docs/9.2/static/queries-limit.html
>  
> I should look at the postgres/rails documentation more to see how limit 
> and pluck actually work! From what I see, my method orders all the rows 
> within postgres, postgres gets the ID column, and then rails throws out 
> everything except the last 200 rows. Frederick''s method does the
same, but
> the extra rows are thrown out within postgres instead. I assume that the 
> speed bump is because postgres handles the disposal of the extra rails?
>
>
> Pluck is a rails thing. But yes the difference is that your code fetches 
14000 ids and then ruby picks the last 200 of those, whereas by using limit 
postgres only returns 200 rows. In addition because it''s sorting on an 
indexed column it''s very quick for it to find the last 200 rows - it 
doesn''t have to find all 14000 first.

Fred 

-- 
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+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To view this discussion on the web visit
https://groups.google.com/d/msgid/rubyonrails-talk/99a4f9e9-905e-4b76-8831-a39ff0189b05%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.