Two simple Postgres tips to kick-start year 2017

While reviewing my notes on some handy Postgres tricks and nasty gotchas to conclude in on-site training course my “current me” again learned some tricks which an older version of “me” had luckily wrote down. So here are two simple tricks that hopefully even a lot of Postgres power-users find surprising.

Disabling JOIN re-ordering by Postgres planner

99.9% of time Postgres does a perfectly good job on 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 most optimal strategy with the goal not to create 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.

And 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 LIKE-s a.k.a. simple text search using wildcards. So let’s imagine a not too uncommon case where we have somekind 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) 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%';

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.

Kaarel Moppel
I’ve been interested with databases for the last 9 years, working last 5 years exclusively with PostgreSQL. And still I’m constantly surprised by it’s powerful set of features and the fast pace of development by the globally friendly community. On my spare time I enjoy playing soccer and travelling.