Using PostgreSQL Arrays the Right Way

A lot of times its because you want want a rollup table but also need to answer cardinality questions. So instead of scanning millions of rows you can have a single rolled up array that you can perform set operations on other rolls (instead of a numeric aggregate that does not have the appropriate mathematical properties to do much with).

If my pg-fu is accurate, as the array grows, it would eventually get farmed out to a `pg_toast` table once the row no longer fits within a page (exact threshold might be lower). So it wouldn't be bloating any query path that doesn't use/depend on the array at the expense of forcing a toast lookup on every tuple that refers to the array.

That should not be construed as an endorsement. I would think the array append might get expensive.

Great question; I'm an engineer at Heap. This data model fit our needs at the time, however, you're absolutely right that these user rows do become quite cumbersome as they grow large.

> Well... generally this is solved via indices. An event log indexed by user ID would have the same effect. You get all the user info, and if you really need to grab historical or event data, it's there when necessary for parts of the app that actually need it.

Indeed! We're investigating a more traditional schema, as the topics of our more recent blog posts might suggest (http://blog.heapanalytics.com/speeding-up-postgresql-queries...).

IME, like the fact that many Oracle shops still heavily use Oracle's old non-standard join syntax, the main reason seems to be inertia. Subqueries have been supported in major databases a lot longer than CTEs, so there are a lot more books using them, people have a lot more experience with them, etc.

The thing is, CTEs are not named subqueries. They are supposed to execute just once, even if used on multiple places in the query.

This causes a number of challenges for the planner, because:

* some of the places may benefit from quickly producing the first row (e.g. in EXISTS) while other places may need all the rows (e.g. when you pass the rows to GROUP BY or something)

* the places may use additional clauses, but these can't be pushed down to the CTEs because that'd influence the other place

There may be databases with planner that handle this in a different way, but in PostgreSQL the CTEs act as optimization fences - each CTE is optimized on it's own, without considering the rest of the query (additional clauses etc.)

People who use CTEs as named subqueries are often surprised by the impact this may have on the plans performance-wise.

Consider for example this:

  CREATE TABLE foo (a INT UNIQUE, b FLOAT);
  INSERT INTO foo SELECT i, random() FROM generate_series(1,1000000) s(i);

  CREATE TABLE bar (a INT);
  INSERT INTO bar SELECT i FROM generate_series(1,1000000) s(i);

  WITH foo_filtered AS (SELECT * FROM foo WHERE b < 0.5)
  SELECT * FROM bar JOIN foo_filtered USING (a) LIMIT 10;

                                         QUERY PLAN                                       
  ----------------------------------------------------------------------------------------
   Limit  (cost=36986.19..38236.83 rows=10 width=12)
     CTE foo_filtered
       ->  Seq Scan on foo  (cost=0.00..17906.00 rows=510375 width=12)
             Filter: (b < '0.5'::double precision)
     ->  Hash Join  (cost=19080.19..63848915.94 rows=510375 width=12)
           Hash Cond: (bar.a = foo_filtered.a)
           ->  Seq Scan on bar  (cost=0.00..14425.00 rows=1000000 width=4)
           ->  Hash  (cost=10207.50..10207.50 rows=510375 width=12)
                 ->  CTE Scan on foo_filtered  (cost=0.00..10207.50 rows=510375 width=12)
  (9 rows)

and without the CTE:

  SELECT * FROM bar JOIN foo USING (a) WHERE b < 0.5 LIMIT 10;

                                      QUERY PLAN                                    
  ----------------------------------------------------------------------------------
   Limit  (cost=0.42..10.26 rows=10 width=12)
     ->  Nested Loop  (cost=0.42..502029.00 rows=510375 width=12)
           ->  Seq Scan on bar  (cost=0.00..14425.00 rows=1000000 width=4)
           ->  Index Scan using foo_a_key on foo  (cost=0.42..0.48 rows=1 width=12)
                 Index Cond: (a = bar.a)
                 Filter: (b < '0.5'::double precision)
  (6 rows)

All because the planner was unable to consider the join condition and the LIMIT 10, when planning the CTE.

Author here. I didn't know about first_value. Thanks for the tip!

I'll play with this later today and see if it performs any better. In any case, it makes the query easier to read, so we'll probably use this unless it somehow makes the function slower.

Thanks again!

That's some pretty good stuff, right there. Out of curiosity, any reason you're using HSTORE instead of JSON? I've been watching what contexts JSON gets used in PostgreSQL systems, and having another data point never hurts. :)