RAILS SQL Subquery optimization

Question

I have a query which looks like this:

@inventory =  Pack.find_by_sql("SELECT Packs.id, "+
" (SELECT COUNT(*) FROM Stocks WHERE (Stocks.pack_id = Packs.id AND Stocks.status = 'online'      AND Stocks.user_id = #{current_user.id})) AS online,"+
" (SELECT COUNT(*) FROM Stocks WHERE (Stocks.pack_id = Packs.id AND Stocks.status = 'offline'     AND Stocks.user_id = #{current_user.id})) AS offline,"+
" (SELECT COUNT(*) FROM Stocks WHERE (Stocks.pack_id = Packs.id AND Stocks.status = 'depositing'  AND Stocks.user_id = #{current_user.id})) AS depositing,"+
" (SELECT COUNT(*) FROM Stocks WHERE (Stocks.pack_id = Packs.id AND Stocks.status = 'withdrawing' AND Stocks.user_id = #{current_user.id})) AS withdrawing,"+
" (SELECT COUNT(*) FROM Stocks WHERE (Stocks.pack_id = Packs.id AND Stocks.status = 'selling'     AND Stocks.user_id = #{current_user.id})) AS selling,"+
" (SELECT COUNT(*) FROM Transactions WHERE (Transactions.pack_id = Packs.id AND Transactions.status = 'buying' AND Transactions.buyer_id = #{current_user.id})) AS buying"+
" FROM Packs WHERE disabled = false")

I am thinking there's a way to make a new sub-query so that instead of

SELECT FROM Stocks

the query selects from a stored table

SELECT FROM (Stocks WHERE (Stocks.pack_id = Packs.id AND Stocks.user_id = #{current_user.id}))

which would only be queried once. Then the WHERE Stocks.status = ? stuff would be applied to that stored table.

Any help guys?

9.1 at the moment. I will probably be updating to 9.4 at some point. — Aron Greenspan, Nov 28 '14 at 15:11
[So do you have your answer?](http://meta.stackexchange.com/a/5235/169168) — Erwin Brandstetter, Jul 24 '15 at 03:19

score 2 · Answer 1 · edited May 23 '17 at 12:30

The best query depends on data distribution and other details.

This is very efficient as long as most pack_id from the subqueries are actually used in the join to packs (most packs are NOT disabled):

SELECT p.id
     , s.online, s.offline, s.depositing, s.withdrawing, s.selling, t.buying
FROM   packs p
LEFT   JOIN (
   SELECT pack_id 
        , count(status = 'online'      OR NULL) AS online
        , count(status = 'offline'     OR NULL) AS offline
        , count(status = 'depositing'  OR NULL) AS depositing
        , count(status = 'withdrawing' OR NULL) AS withdrawing
        , count(status = 'selling'     OR NULL) AS selling
   FROM   stocks
   WHERE  user_id = #{current_user.id}
   AND    status = ANY('{online,offline,depositing,withdrawing,selling}'::text[])
   GROUP  BY 1
   ) s ON s.pack_id = p.id 
LEFT   JOIN (
   SELECT pack_id, count(*) AS buying
   FROM   transactions
   WHERE  status = 'buying'
   AND    buyer_id = #{current_user.id}
   ) t ON  t.pack_id = p.id
WHERE  NOT p.disabled;

In pg 9.4 you can use the aggregate FILTER clause:

SELECT pack_id 
     , count(*) FILTER (WHERE status = 'online')      AS online
     , count(*) FILTER (WHERE status = 'offline')     AS offline
     , count(*) FILTER (WHERE status = 'depositing')  AS depositing
     , count(*) FILTER (WHERE status = 'withdrawing') AS withdrawing
     , count(*) FILTER (WHERE status = 'selling')     AS selling
FROM   stocks
WHERE  ...

Details:

How can I simplify this game statistics query?

Use crosstab() for the pivot table to make that faster, yet:

SELECT p.id
     , s.online, s.offline, s.depositing, s.withdrawing, s.selling, t.buying
FROM   packs p
LEFT   JOIN  crosstab(
   $$
   SELECT pack_id, status, count(*)::int AS ct
   FROM   stocks
   WHERE  user_id = $$ || #{current_user.id} || $$
   AND    status = ANY('{online,offline,depositing,withdrawing,selling}'::text[])
   GROUP  BY 1, 2
   ORDER  BY 1, 2
   $$
  ,$$SELECT unnest('{online,offline,depositing,withdrawing,selling}'::text[])$$
    ) s (pack_id int
      , online int
      , offline int
      , depositing int
      , withdrawing int
      , selling int
       ) USING (pack_id)
LEFT   JOIN (
   SELECT pack_id, count(*) AS buying
   FROM   transactions
   WHERE  status = 'buying'
   AND    buyer_id = #{current_user.id}
   ) t ON  t.pack_id = p.id
WHERE  NOT p.disabled;

Details here:

PostgreSQL Crosstab Query

If most packs are disabled, LATERAL joins will be faster (requires pg 9.3 or later):

SELECT p.id
     , s.online, s.offline, s.depositing, s.withdrawing, s.selling, t.buying
FROM   packs p
LEFT   JOIN LATERAL (
   SELECT pack_id 
        , count(status = 'online'      OR NULL) AS online
        , count(status = 'offline'     OR NULL) AS offline
        , count(status = 'depositing'  OR NULL) AS depositing
        , count(status = 'withdrawing' OR NULL) AS withdrawing
        , count(status = 'selling'     OR NULL) AS selling
   FROM   stocks
   WHERE  user_id = #{current_user.id}
   AND    status = ANY('{online,offline,depositing,withdrawing,selling}'::text[])
   AND   pack_id = p.id
   GROUP  BY 1
   ) s ON TRUE
LEFT   JOIN LATERAL (
   SELECT pack_id, count(*) AS buying
   FROM   transactions
   WHERE  status = 'buying'
   AND    buyer_id = #{current_user.id}
   AND   pack_id = p.id
   ) t ON TRUE
WHERE  NOT p.disabled;

Why LATERAL? And are there alternatives in pg 9.1?

Record returned from function has columns concatenated

This code works, and is what I'm generally trying to do, but is about 15 times slower. I am not using enough test data though, so I will have to add more and compare. — Aron Greenspan, Nov 28 '14 at 15:23
@user1694853: For performance optimization we'd need more data. Especially table definitions, cardinalities and value frequencies (what percentage of `pack` is `disabled`, how many different values in `stocks.status`? Freuquecies? Etc. I added some more options. — Erwin Brandstetter, Nov 28 '14 at 16:35
Holy crap you have an insane knowledge of sql. Im gonna have to do some reading to understand whats going on here, lol. Thanks for the help. I will get back to you when I've tested against a larger object pool. — Aron Greenspan, Nov 28 '14 at 18:10
I think I should upgrade to pg 9.4. Is 9.5 out yet or just their documentation? — Aron Greenspan, Nov 28 '14 at 18:27
@AronGreenspan: 9.5 is the new devel version. [9.4 is rc now.](http://www.postgresql.org/about/news/1555/) Wait with the upgrade until it's released. — Erwin Brandstetter, Nov 28 '14 at 18:30

khampson · Answer 2 · 2014-11-28T04:30:56.023

If what you're after is a count of the various types, something like the following would be much less code and easier to read/maintain, IMO...

You could split them up into the different tables, so, for stocks, something like this:

@inventory = Pack.find_by_sql("SELECT status, count(*)
                               FROM stocks
                               WHERE user_id = ?
                               GROUP BY status
                               ORDER BY status", current_user.id)

Note the importance of using ? to prevent SQL injection. Also, Ruby supports multiline strings, so there's no need to quote and concatenate every line.

You can do something similar for the other tables.

RAILS SQL Subquery optimization

2 Answers2