While reviewing my notes on some handy Postgres tricks and nasty gotchas, to conclude an on-site training course, my “current me” again learned some tricks which an older version of “me” had luckily written down. So here are two simple tricks that hopefully even a lot of Postgres power-users find surprising.
Disabling JOIN re-ordering by the Postgres planner
99.9% of time Postgres does a perfectly good job at figuring out which execution plan it should use to resolve all the JOINs in your multi-table/subquery query. This means that the actual order of tables listed in your query does not matter at all – Postgres will use the optimal strategy, with the goal of avoiding too many intermediate rows, use indexes, leaving out CROSS JOINS when possible, etc. But what a lot of people don’t know about is that for those 0.1% of problem cases where Postgres really doesn’t choose the optimal path, you can actually force the planner to perform the JOINs in the order they appear in your query! And it’s as easy as setting the join_collapse_limit parameter to “1”. It is an user-level setting meaning even non-superusers can set it and it also works also with older 9.X versions of Postgres. One could maybe complain a bit about the non-descriptive parameter name but all in all, great trick.
Here is an example query to illustrate the usage – solving the problem of finding tables without any indexes at all:
EXPLAIN ANALYZE SELECT n.nspname||'.'||c.relname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_index i ON i.indrelid = c.oid WHERE i.indrelid IS NULL AND c.relkind = 'r'; SET join_collapse_limit = 1; EXPLAIN ANALYZE SELECT n.nspname||'.'||c.relname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_index i ON i.indrelid = c.oid WHERE i.indrelid IS NULL AND c.relkind = 'r';
Inspecting outputted plans (plan 1 here, plan 2 here) what will we see? Indeed, for the first query the join order is: (pg_class = pg_index) = pg_namespace, but after changing the join_collapse_limit=1 our “forced” JOIN ordering is as seen from the query text: (pg_class = pg_namespace) = pg_index.
In this sample use case of course Postgres is right, and the default plan was actually better as it executed faster.
Array of LIKEs
Some pun intended…but that aside, let’s think about Postgres LIKEs a.k.a. simple text search using wildcards. So let’s imagine a not too uncommon case, where we have some kind of information encoded into product codes (years, manufacturing location, …) and then we want to find products matching some of certain criteria. Normally you would then (given there is no separate column/index with that information and no Fulltext Search setup) do something like:
SELECT product_cost FROM t_test WHERE product_code LIKE '%-678-%';
Now you may ask “What is wrong with that?” Well on its own yes, nothing…. but what about when we need to search for 5 product conditions at once? The query will get just kind of ugly and unwieldy!
SELECT sum(product_cost) FROM t_test WHERE product_code LIKE '%123%' OR product_code LIKE '%234%' OR product_code LIKE '%345%' OR product_code LIKE '%456%' OR product_code LIKE '%567%';
This is not something I normally enjoy writing…so can we do better? Yes, we can! Say hello to our “array of LIKEs”:
SELECT sum(product_cost) FROM t_test WHERE product_code LIKE ANY( array['%123%', '%234%', '%345%', '%456%', '%567%']);
-- or the same using shorter Postgres array notation
SELECT sum(product_cost) FROM t_test WHERE product_code LIKE ANY(‘{%123%,%234%,%345%,%456%,%567%}’);
Much better, my eyes can rest now.
And though Christmas time is over, there’s an additional present with the latter notation – it is according to my tries ~10% faster! Win and win.
A side warning – such double-wildcard usage will always result in a full table scan (even when you have an ordinary index on that text column) and be potentially very slow unless you have declared a special trigram GiST index for example.
Do you know any other cool tricks? Post a comment! And continue enjoying Postgres in 2017.
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.