ActiveRecord's pluck gives an un-ordered list?

Rails
1

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.

2

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.

Fred

3

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:

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!

4

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

5

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.