Forum: Ruby on Rails ActiveRecord's pluck gives an un-ordered list?

99f6e2a6278fd50a73d8620f16aeba21?d=identicon&s=25 marcin longlastname (Guest)
on 2013-07-27 15:24
(Received via mailing 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.

  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
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
I have to order all rows of it first, even though I am specifically
pluck over select for performance and memory reasons. Does anyone have
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
I also extremely reccomend mentioning in the pluck API documentation
pluck returns an un-ordered list so others do not fall into the same
mistake I did.
81b61875e41eaa58887543635d556fca?d=identicon&s=25 Frederick Cheung (Guest)
on 2013-07-27 15:28
(Received via mailing list)
On Saturday, July 27, 2013 4:22:26 AM UTC+1, marcin longlastname wrote:
>  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.

99f6e2a6278fd50a73d8620f16aeba21?d=identicon&s=25 marcin longlastname (Guest)
on 2013-07-27 21:00
(Received via mailing list)
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.
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
to avoid possible rails caching) and using what Fredrick recommended,
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
(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

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
    (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

Postgres documentation for LIMIT:

I should look at the postgres/rails documentation more to see how limit
pluck actually work! From what I see, my method orders all the rows
postgres, postgres gets the ID column, and then rails throws out
except the last 200 rows. Frederick's method does the same, but the
rows are thrown out within postgres instead. I assume that the speed
is because postgres handles the disposal of the extra rails?

Anyways, thank you a whole bunch for the help frederick!
99f6e2a6278fd50a73d8620f16aeba21?d=identicon&s=25 marcin longlastname (Guest)
on 2013-07-28 02:50
(Received via mailing list)
Woops, looks like I can't edit my last post. The units are in

So TimedAsset.order("id").pluck(:id).last(200)           <-- my old
     (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
each query was 16.8 milliseconds, 18.5 milliseconds .... 17.8

This is on a ubuntu server virtual machine (1 core) within vmware on an
81b61875e41eaa58887543635d556fca?d=identicon&s=25 Frederick Cheung (Guest)
on 2013-07-28 12:43
(Received via mailing list)
> 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
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.

Please log in before posting. Registration is free and takes only a minute.
Existing account

NEW: Do you have a Google/GoogleMail, Yahoo or Facebook account? No registration required!
Log in with Google account | Log in with Yahoo account | Log in with Facebook account
No account? Register here.