ActiveRecord's pluck gives an un-ordered list?

[marcin longlastname]

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.

[Frederick Cheung]

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

[marcin longlastname]

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!

 

[marcin longlastname]

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.

[Frederick Cheung]

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