postgresql-insights-i.post

   1 ;;;;;
   2 title: PostgreSQL insights I
   3 tags: postgresql
   4 date: 2015-04-25 13:47:23+01:00
   5 format: md
   6 ;;;;;
   7
   8 After working a lot more with PostgreSQL, I happened to stumble upon a few
   9 things, mostly related to query optimisation, that at least for me weren't
  10 quite readily apparent.  Please note that this is all with regards to
  11 PostgreSQL 9.3, but unless otherwise noted it should still be the case for 9.4
  12 as well.
  13
  14 ## Slow `DELETE`s
  15
  16 Obviously, indexes are key to good query performance.  If you're already using
  17 `EXPLAIN`, with, or without `ANALYZE`, chances are good you know how and why
  18 your queries perform like they do.  However I encountered a problem with a
  19 `DELETE` query, where the output from `EXPLAIN` was as expected, i.e. it was
  20 using the optimal plan, but still the performance was abysmal; a query to
  21 delete about 300 elements in bulk, like `DELETE FROM table WHERE id IN (...);`,
  22 was quite fast to remove the elements (as tested from a separately running
  23 `psql`), but still the query took about three minutes(!) to complete.
  24
  25 In this scenario the table in question was about a million rows long, had a
  26 primary index on the `id` column and was referenced from three other tables,
  27 which also had foreign key constraints set up; no other triggers were running
  28 on any of the tables involved.
  29
  30 The `postgres` server process in question wasn't doing anything interesting, it
  31 was basically taking up one core with `semop` calls as reported by `strace`, no
  32 other I/O was observed though.
  33
  34 At that point I finally turned to `#postgresql`.  Since the information I could
  35 share wasn't very helpful, there were no immediate replies, but one hint
  36 finally helped me to fix this problem.  It turns out that the (kinda obvious)
  37 solution was to check for missing indexes on foreign key constraints from
  38 *other tables*.  With two out of three indexes missing I resumed to `CREATE
  39 INDEX CONCURRENTLY...;` and about five minutes later the `DELETE` was now
  40 running in a few milliseconds.
  41
  42 The part where I was really frustrated here is that none of the available
  43 statistics guided me in the search; `EXPLAIN ANALYZE` apparently doesn't
  44 include the runtime for foreign key constraints and they don't show up in
  45 other places as well.  In hindsight this is something that I should've checked
  46 earlier (and from now on I will), but it's also a weakness of the analyze
  47 framework not to help the developer to see the slowdowns involved in a
  48 situation like this.
  49
  50 ## Common Table Expressions
  51
  52 Refactoring queries to reuse results with `WITH` queries is absolutely worth it
  53 and improved the runtime of rather large queries by a large factor.  This is
  54 something that can be seen from the query plan, so when you're using the same
  55 expressions twice, start looking into this and see if it helps both for
  56 readability (don't repeat yourself) and performance.
  57
  58 ## JSON result construction
  59
  60 We need nested JSON output in a few cases.  This means (in 9.3, there are some
  61 better functions available in 9.4) that a combination of
  62 `row_to_json(row(json_agg(...)))` was necessary to get proper nesting of
  63 aggregated sub-objects, as well as wrapping the end result in another object,
  64 because the output had to be formatted as a JSON object (with curly brackets)
  65 instead of a JSON array (with rectangular brackets).
  66
  67 Technicalities aside the JSON support is pretty good and since the initial code
  68 was written I've discovered that since we in many cases don't actually have
  69 multiple results (for `json_agg`), not using that method will again
  70 significantly improve performance.
  71
  72 That means instead of something like the following:
  73
  74     SELECT row_to_json(row(json_agg(...))) FROM ... JOIN ... GROUP BY id;
  75
  76 , where the input to `json_agg` is a *single* result from the `JOIN`, we can
  77 write the following instead:
  78
  79     SELECT row_to_json(row(ARRAY[...])) FROM ... JOIN ...;
  80
  81 , which, if you examine the output of `EXPLAIN`, means *no sorting* because of
  82 the `GROUP BY` clause.  The convenience of `json_agg` here doesn't really
  83 justify the significant slowdown caused by the aggregation function.
  84
  85 Note that the array constructed via `ARRAY[]` is properly converted to JSON, so
  86 the end result is again proper JSON.