N+1 Query on Sinatra app

Question

So i'm getting these three queries on my console:

D, [2014-05-30T09:41:15.504655 #4629] DEBUG -- :    (0.2ms)  SELECT COUNT(*) FROM "posts"  WHERE ("posts"."published_on" IS NOT NULL)
D, [2014-05-30T09:41:15.506857 #4629] DEBUG -- :   Post Load (0.3ms)  SELECT  "posts".* FROM "posts"  WHERE ("posts"."published_on" IS NOT NULL)  ORDER BY published_on DESC LIMIT 1 OFFSET 0
D, [2014-05-30T09:41:15.513479 #4629] DEBUG -- :    (0.3ms)  SELECT COUNT(count_column) FROM (SELECT  1 AS count_column FROM "posts"  WHERE ("posts"."published_on" IS NOT NULL) LIMIT 1 OFFSET 0) subquery_for_count

I did google this and found this 'N+1 queries' and a whole lot but not real answers why; so i don't know if i should fix this or not, or how (im new to Ruby/sinatra)

On my code all i do is:

count = Post.where.not({published_on: nil}).count
....
@posts = Post.where.not({published_on: nil}).order("published_on DESC").limit(per_page).offset(offset)

In the middle i do some calculations with count to ge the offset, and after that a @post.length to know if i get any results.

My models are pretty simple, Post(w/has_many tags), Tag, and PostTags to associate both.

Any tip to change this, if i have to?

Thanks

Because i expect two queries instead of three. Also looking at similar questions that's what i found. I've no idea why the 3rd query occurs — Ron, May 30 '14 at 13:17

score 0 · Accepted Answer · edited May 23 '17 at 11:50

An N+1 problem is relevant to queries where in ORM you want to fetch the children of a item, and find that the ORM queries them one-by-one (see What is SELECT N+1?).

Your problem seems unrelated to this. I've tracked down your problem to this code(the hint was subquery_for_count in your logs):

def execute_simple_calculation(operation, column_name, distinct) #:nodoc:
  # Postgresql doesn't like ORDER BY when there are no GROUP BY
  relation = unscope(:order)
  ...
  if operation == "count" && (relation.limit_value || relation.offset_value)
    # Shortcut when limit is zero.
    return 0 if relation.limit_value == 0

    query_builder = build_count_subquery(relation, column_name, distinct)
    bind_values = query_builder.bind_values + relation.bind_values
  else
  ...

...

def build_count_subquery(relation, column_name, distinct)
  column_alias = Arel.sql('count_column')
  subquery_alias = Arel.sql('subquery_for_count')

This means that the relation calculations module will add the extra query if you mix count with limit or offset...

Thanks a lot, helped me out a lot to track my issue. – Ron May 30 '14 at 14:11 — Ron, May 30 '14 at 14:11

N+1 Query on Sinatra app

1 Answers1