Forem: Michiel Hendriks

Hands of the privates

Michiel Hendriks — Tue, 12 May 2020 10:01:24 +0000

I am not a fan of private visibility. It prevents extensibility, but more importantly, it prevents unit testing.

You shouldn't unit test private methods. I hear you say. But why not?

It is time for a bad analogy. Lets say I have I have this FoodFactory and I have a great hamburger recipe. So I would have the method

public Hamburger createHambuger() {
    // ...
}

We probably all agree that this method should not contain every single step in creating a hamburger, but you would probably do something like this.

public Hamburger createHambuger() {
    Stack parts = new Stack();
    parts.add(createBottomBun());
    parts.add(getLettuce());
    parts.add(getTomatoSlice(2));
    parts.add(createPatty());
    // ...
    parts.add(createTopBun());
    return assembleHamburger(parts);
}

At some point in time we start getting complaints that the top bun does not have sesame seeds on top. Which is weird, because it is part of the top bun. After some extensive debugging and eating a lot of hamburgers we find out that sometimes createTopBun() returns the bun upside down.

This would be an easy fix. We just create a unit test to assert createTopBun() returns the bun right side up, and ... wait! Our awesome hamburger is a top-secret recipe, so all our steps should be private methods. The only way to test if the top bun is right side up I need to create a full hamburger and then dissect the result. Which would also mean my test setup needs to also take care of having the inventory to create the patty, etc. All I wanted was to verify the top bun.

With the exception for security related parts, why should I make things private instead of protected?
And why should I not test private methods?

New Programming Jargon

Michiel Hendriks — Fri, 10 Apr 2020 06:43:57 +0000

Besides coming up with new solutions to old and new problems, we programmers sometimes also come up with new terms.

About ten years ago a question was posted on StackOverflow asking people about new terms they coined. The result was quite a good list of terms I am still using to date. Terms like Yoda Conditions, or Stringly Typed. Jeff Atwood wrote an article about most of the good terms. Sadly the original StackOverflow question has been deleted.

Today while reading Ben Halpern's post Have you rekindled your love of coding after losing the spark at any point in your career? I saw Jason C. McDonald's comment

I only start losing my love of coding when I start getting burned out and codeblind on one project. The cure is usually to take a break and pick up something completely different!

Where de defined codeblind as a term for that phenomenon when you've been working on the same code for so long, you can no longer see the obvious.

As it has been a while since the StackOverflow quest, I this it's due time to ask it again.

What new programming jargon have you coined, or picked up somewhere?

Post your term and definition in the comments below. 👇👇👇

hCaptcha instead of Google's reCaptcha

Michiel Hendriks — Thu, 09 Apr 2020 13:49:23 +0000

CAPTCHA means Completely Automated Public Turing-test to tell Computers and Humans Apart. It is a vital feature to keep spam bots away from your site.

Years ago everybody wrote their own system, usual a picture containing an obscured word or number. But technology of the spammers also improved and these methods would no longer stop them. Some smart people invented reCAPTCHA, which was based on solving problems computers were not really good at. As with most useful and popular technology this was bought by a large tech company, in this case Google.

We all know Google and their primary business: selling advertisements. One of their ad-platform's major selling point is targeted advertisements based on information they harvested about the website visitors.

If you want to run a privacy minded website you obvious want as little technology on which invades on your visitors privacy. There are many alternatives for various Google products.

On one of my websites I removed Google Ads, because I did not earn much from it anyway. I removed Google Analytics, because I was not looking at it anyway. But, I was still using reCAPTCHA to fend of spammers.

Yesterday I saw a blogpost from Cloudflare on how they moved from reCAPTCHA to hCaptcha. So there is also an alternative for the last Google product I had on my site. Today I started all the work to move to hCaptcha.

hCaptcha is basically the same as reCAPTCHA, with some major differences:

They respect the privacy of your visitors.
The problems visitors solve are not for the benefit of a single company.
You can earn money, or donate it, by visitors solving these problems.

So now the difficult part: moving away from reCAPTCHA to hCaptcha. All the steps you need to perform to convert are detailed in the documentation. It basically required me to perform three replace-all actions, and updating two settings to covert my existing reCAPTCHA implementation to hCaptcha. I think it cost me almost 30 minutes to execute and verify! Yes, it took me more time to write this article.

Dear Technology, ...

Michiel Hendriks — Wed, 18 Sep 2019 17:46:26 +0000

I saw this tweet during the recent #DevDicuss on Twitter.

// Detect dark theme var iframe = document.getElementById('tweet-1174135015485186053-749'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1174135015485186053&theme=dark" }

I like the format of writing open letters to technology. Obviously technology cannot respond or even defend itself. But it's good to write away your annoyances.

What is your Dear Technology, ... letter?

This space available for rent

Michiel Hendriks — Sun, 25 Aug 2019 20:39:23 +0000

Last week the maintainer of a JavaScript package decided to monetize installation of its package through NPM by showing ads after the install.

The response to this was surprisingly quite mixed. I did not expect so many people supporting this idea. Most of the supporters justified this as a valid means to earn money of maintaining the open source project.

The above incident was not the first case. A few months ago an other developer decided to beg for a job in a similar way. And there are some more.

I was rather supersized that NPM allows arbitrary code execution during package installation. Seems like a major security issue, which can clearly also be abused in other ways.

So what is your take on this? Is your console output available for others to rent out?

Healthy Breakfast Coding

Michiel Hendriks — Fri, 19 Jul 2019 06:18:12 +0000

It's the fruit loop

['apple', 'pear', 'grape', 'kiwi'].forEach(consume)

Inspired by Ben's post about loops.

Which types of loops are most popular in the programming languages you use?

Ben Halpern ・ Jul 18 '19

#discuss

Actual 10x Engineers

Michiel Hendriks — Wed, 17 Jul 2019 17:00:40 +0000

Earlier this month Fabrice Bellard released QuickJS. Which is a Javascript runtime, which is almost ES2019 complete, that you can embed in your application. This is no small feat.

In the past week or so there has been a lot of discussion about 10x engineers. While the concept of 10x engineers is flawed in numerous ways, there are software developers who have a truly amazing track record of work.

Bellard's QuickJS release started a discussion on hackernews about great developers who have done amazing work.

Fabrice Bellard

So let's start with Fabrice. His website lists the great things he created/started. His work is not just in a single domain.

He created FFmpeg, which is the basis for a lot of audio video software out there. There is now obviously a whole project team which keeps improving this.

Bellard also created the QEMU, a generic machine emulator and virtualizer. Which has been a major player in virtual machine hosts.

And then there are a whole bunch of other projects like a LTE/NR Base Station, a PC emulator in JavaScript, ASN1 compiler, etc.

Dan J. Bernstein

Also known as djb. Not only did he sue the US government about the export laws around cryptography, and won, he also create a lot of great software.

His main field is cryptography. Creating algorithms/ciphers like Sals20, ChaCha20, Curve25519. And a whole bunch of research.

On his software resume is a lot of really secure software, which is often still used despite not having had any updates for years. It includes: qmail, a SMTP server; djbdns, a DNS server; ezmlm, a mailing list server.

qmail is probably the most notable. It has not received significant updates for a really long time. Only a few bugs have been found, none of them deemed as a security bug.

Linus Torvalds

Probably the most well known person of this list. He started the Linux kernel, and is still actively working on it and managing the project.

But he also created Git because he needed a decentralized version control system that suited his needs.

Richard Stallman

Probably mostly known for his activism. But when he original started the GNU project there was not a lot of software he could use within his idea of Free Software.

He had to write this own compiler (which became GCC), his own debugger (GDB), a standard C library (glibc), and a whole bunch of other parts in the GNU userland. He also created GNU Emacs.

Ludvig Strigeus

One of the younger guys on this list.

He is one of the original developers of ScummVM, a re-implementation of the LucasArts Scumm game engine. These days ScummVM has grown to a generic VM for various older graphical adventure games, not just Scumm based games.

Ludvig also started the OpenTTD project, which is a reverse engineered implementation of the Chris Sawyer video game Transport Tycoon Deluxe. The community continued his work, and OpenTTD is now available as a fully standalone video game.

One of the most popular BitTorrent clients on MS Windows is probably uTorrent. This client was also written by Ludvig.

And then there is this thing called Spotify. Ludvig was there from the start to build the technology.

Others?

Who else has a track record of creating amazing pieces of software which baffles the most? Leave your suggestions in the comments.

PS. This article is not intended to imply that 10x engineers are a normal or even real thing.

Happy birthday to Stack

Michiel Hendriks — Mon, 15 Jul 2019 16:32:43 +0000

The best joke about recursion

Michiel Hendriks — Sun, 07 Jul 2019 15:00:43 +0000

... can be found here:

The best joke about recursion

Michiel Hendriks ・ Jul 7 '19

#jokes

Is High Quality Software Worth the Cost?

Michiel Hendriks — Tue, 04 Jun 2019 19:23:30 +0000

Most developers agree that quality of software is important, and not an afterthought. However, quality is usually one of the first things to get axed when budgets (time, money, ...) start to run out.

Many developers probably have tried to explain to management why you should not skim on investing is quality, but have failed to convince them.

Instead of cutting features from the next release, quality investments are reduced. Investments like:

unit tests
refactoring code
design documentation
etc.

I could also try to explain why you should invest in creating high quality software. But I will probably not do a good job either. At least, not better than Martin Fowler already did.

In his recent article Is High Quality Software Worth the Cost? Martin Fowler properly explains why you should invest in creating high quality software. He also discusses that the cost of creating high quality software is actually negative. It saves you time and money in the end.

It is an article management should also be able to understand. Next time you need to explain to management why you need to spend time on quality improvements. Hand them this article, or in fact, do it right now. If they are not convinced, you should consider that a red flag.

Improving PostgreSQL queries

Michiel Hendriks — Tue, 30 Apr 2019 22:23:34 +0000

A while ago I commented that my big win was improving a query which took over an hour to execute to just 30 seconds. In fact, for the client this query went from close to 2 hours to about 2 minutes, which included transferring the millions of rows. I optimized quite a few other queries in the past months.

This article will be mostly about reporting or business intelligence (BI) queries. These queries are often large/complex, reason about a lot of data, and generally take a long time to execute. With long I mean tens of seconds if not minutes or hours.

Some of the things I will explain in this article could also apply to more standard application queries, like search queries. But I cannot be sure about it. This brings me to the most important step.

Explain the query plan

Any serious DBMS has an option to get information about how the DBMS is going to execute the query. This is the query plan. Every time you change anything you should request a query plan to see how it was affected. Changes include changes to the PostgreSQL server settings, and also upgrades of the server. It could very well happen that your optimized query will now perform worse.

In PostgreSQL the query plan can be retrieved using the EXPLAIN command. You simply but this in front of your query and it will produce a whole bunch of output.

There are various options to the EXPLAIN command. I generally use EXPLAIN (VERBOSE) to get the most important information. When you also include the ANALYZE option PostgreSQL will also execute the query and return actual execution information, and not just the plan. However, this is not really helpful if you query takes half an hour to execute. With ANALYZE you might also want to include the BUFFERS option to get information about shared buffer usage.

An other option which you might need is the FORMAT option. Which format to select depends on the tool you use for visualization/analysis of the query plan. The default output is quite human readable. But when the query is really complex it will become difficult to keep track of the nesting.

EXPLAIN SELECT sum(i) FROM foo WHERE i < 10;

                             QUERY PLAN
---------------------------------------------------------------------
 Aggregate  (cost=23.93..23.93 rows=1 width=4)
   ->  Index Scan using fi on foo  (cost=0.00..23.92 rows=6 width=4)
         Index Cond: (i < 10)
(3 rows)

Output formats include JSON, XML, YAML, and the default TEXT.

Query plan visualization

There are quite some tools out there which can render the query plan into something easier to use.

Note: despite that these visualization are really helpful, this article will contain the plain text output as most details are hidden behind interactive usage.

pgAdmin

The standard administration tool of PostgreSQL pgAdmin has visualization build in. Simple execute the query using the explain action. And you will get a visual representation.

The lines between the various query parts become thicker depending on the cost of that step. (Note: set the analyze options to include costs.) So it will be easy to find where the biggest cost comes from. But note that the thickness of the line is not relative to the total cost of the query. So a part with a cost of 1,000,000 would have the same thickness as the part with a cost of 10,000.

Hovering over the various nodes will show some additional details.

depesz' explain tool

Depesz has an online tool where you can paste the TEXT output of explain and it renders it in a more easy to navigate HTML table.

It will highlight the most expensive parts of the query. Once you start optimizing the query you can keep adding new query plans to compare them.

Postgres EXPLAIN Visualizer (pev)

Pev is another online tool to visualize the query plan. This one uses the JSON output format, and prefers it with the ANALYZE option.

It produces quite a nice looking output with hints on which parts of the query are most problematic.

When you have really complex queries the visualization might become more troublesome to navigate.

Be sure to read the blog post for some more explanation about the tool.

I love it when a plan comes together

Once you have the query plan it is time to inspect it for possible improvement points.

Your first point of attention should go to the lowest level node with the highest cost. This is the place where you can probably gain the most. Each entry in the plan contains a cost range: cost=0.00..23.92. The first value can be considered the optimistic cost and the second one the pessimistic cost, or minimum and maximum cost. These numbers have no sensible unit, just regard them as low number is good, high number is not. When optimizing a query, get the initial cost, and then try to get both values down.

Generally the most costly parts are sequential table scans of large tables (Seq Scan in the query plan). The goal is to try to get rid of these. But do note that PostgreSQL will happily choose to perform sequential table scans for small tables instead of using an index. The cost of these is usually really low, so do not simply try to get rid of all sequential table scans.

As mentioned earlier, every time you make a change you must generate a new query plan. The cost might shift to a different part in the plan, making effectively no difference. Or the new plan might perform even worse.

A way more extensive explanation on the output can be found in the documentation.

Rules of Optimization

Rule #1: Don't

Rule #2: Don't Yet

-- Michael A. Jackson

Before optimizing a query, first try it. Then try it with different parameters. It might not even need optimization yet. Only when the performance is not acceptable, try to improve it until it is acceptable.

Be sure to test the query with different ranges so see how it scales. I generally test with N, 10N, and 100N. Where N is expected nominal range, and 10N would be the worst case. And I always include a 100N which is the range you do not expect, but will happen sooner than you ever hoped. It is just there to get a sense of scaling for the query.

Settings of Interest

PostgreSQL has a lot of settings you can play around with which can make a lot of difference. Especially the resource consumption and query planning settings are of interest.

Out of the box PostgreSQL is configured really defensively, as if it is running on a machine with only 1GiB of RAM.

I will highlight some interesting settings here, which are not too dangerous to play around with. But please do not solely rely on this text. Always read the official documentation, and make sure you understand it before playing around with the settings.

Resource Consumption

Shared Buffers

One of the first settings you want to adjust is shared_buffers. This is the amount of memory the different PostgreSQL connections can use to share data. Data like indexes which were read from the dist. The recommendation is to set it to 25% to 40% of the system RAM. A PostgreSQL expert told me that with the 9.x series have more that 8GiB assigned to shared buffers generally had no significant effect.

Unlike various other database systems PostgreSQL relies a lot on OS functionality like disk caching in memory. So if you have a server with 64GiB of RAM, and shared buffers set to "only" 8GiB a lot of indexes might not be in the shared cache of PostgreSQL, but they are in the disk cache, and thus quickly accessible.

Temp Buffers

The temp_buffers setting controls how much memory can be used for temporary tables before flushing to the disk. This setting can be changed at runtime, but can only be changed at the start of a session.

Work Mem

An other interesting setting is work_mem. It affects how much memory can be used for sorting and processing of hash tables before flushing data to the disk. Increasing it has a lot of positive effect on complex queries with a lot of sorting and joining of sub-results. For BI-like queries it is well worth increasing the size.

The work memory allocation is per operation, which could be multiple in a single session. (Especially in the newer PostgreSQL versions where queries can be executed concurrently). Which means you should be careful not to make it too big, or else you can run into out-of-memory issues.

This is a setting which can also be changed at runtime for the current session.

Query Planning Settings

These settings affect the query planning. It is quite easily to destroy the performance of your server by messing up these settings. So in most cases you need to be really careful.

Effective Cache Size

The effective_cache_size tells PostgreSQL how much data could be cached in the system memory. This includes cached managed by PostgreSQL, but also caches kept by the OS (like the disk cache).

This setting has no effect on memory allocation. It just gives an indication to the query planner on how likely data might be in memory.

On a Linux system you can get an idea on how much data is cached by the system if you have system monitoring which keeps track of amount of memory used for caches during a certain period. /proc/meminfo would tell you how much is currently used, but you really want to know this for a time frame of a week/month.

Sequential / Random Page Cost

The seq_page_cost and random_page_cost settings determine how costly it is to read data from the disk. The default settings assume spinning disks where random seek times are much higher than sequential access.

If you have an all-flash storage the random seek times are much closed to sequential access. In that case it would make sense to bring the random cost closer to the sequential cost. To some degree this also applies to SAN storage on spinning disks.

Indexes

Improving performance of queries is all about indexes. Without indexes the only thing the database can do is sequential table scans. But indexes are not free. They cost storage, and time to maintain. Changing data in the table means that affected indexes also need to be updated. More complex/advanced indexes obviously take more time.

The worst index is an index which is not used. So only create indexes when you need to. And remove indexes which are not used.

Reminder: as stated earlier, for small tables the query planner might choose to skip using an index.

Multi Column and Sorted Indexes

An index which covers multiple columns is generally better than creating multiple indexes for a single column. PostgreSQL will consider using a multi-column index even if some of the columns in the index are not even used in the query.

For example if we had the following index:

create index order_dates on orders(creation_date, completion_date);

It can be used for queries using any combination of the creation_date and completion_date column. The benefit of having fewer indexes is that they might live in the shared memory, and caches, for a longer time.

The default index storage type in PostgreSQL is a B-Tree. For a B-Tree is does not matter if you look up the highest or lowest value, it has the same complexity. But if most of your queries sort in a descending order on an indexed column there will be an extra step in the query execution to sort the results.

This step can be eliminated by adding an order to the index. There are two importing ordering parts:

data ordering
null values

You can control the order for each independently. By default each column is ASC NULLS LAST, meaning ascending with null values at the end.

If it was important to know which orders have not be completed yet it might be best to put the null values first.

create index order_dates on orders(creation_date, completion_date nulls first);

If you would also be interesting in the most recently completed orders it is probably best to also make the completion_date descending.

create index order_dates on orders(creation_date, completion_date desc);
-- is equivalent to
create index order_dates on orders(creation_date, completion_date desc nulls first);

Just to reiterate, ordering of the index only affects performance when the query contains an order by clause on those columns. It does not make the index search faster.

Partial Indexes

Indexes also take up space. A large index is not good for caching. Creating a small index can have a massive performance improvement. Creating a smaller index can be achieved with partial indexed.

Take the previous example of the order_dates. If we often need to query orders which have not been completed yet, and it is common for orders to be eventually completed. Then it is possible to create a small index on the completion date being null.

create index open_orders on orders(completion_date) where completion_date is null;

This index would be really small compared to an full index on the completion_date column. Because it is small, and probably often used, it will be cached quite a lot. When when it is not, only a few expensive disk read operations are needed.

Index on Expressions

Quite often queries contain expressions in a where clause. For example a case insensitive search:

select orders where lower(city) = 'devtown';

Creating an normal index on the city column has no benefit. As this index would contain Devtown, devtown, etc. as separate entries. To make this query faster you can create an index on an expression:

create index lc_city on orders(lower(city));

Now the above query could use this index to quickly find the appropriate rows.

Index-Only Scans

An Index-Only Scan is where the index contains all the data which was required. Normally the index produces a pointer to the actual data row, which then needs to be fetched.

But, if you create an index which contains all the data which is selected in the query then the database does not have to fetch the row. You could consider this as a really fast view on a single table.

You do not have to make the extra fields parts of the indexed data. You can include them in the index as additional data.

create unique index product_ean on product(ean) include(name)

This creates a unique index on the product's ID and it includes the name column in the index. The following query would be able to perform an index-only scan:

select product.ean, product.name, order_line.quantity
from order_line
join product on product.ean = order_line.product_ean

An obvious benefit of using INCLUDE(...) is that you can add additional columns to a unique index without making it part of the uniqueness constraint. But be aware that it will consume storage.

Tuning Tricks

When you start tuning queries it is strongly advised to vacuum and analyze or at least analyze the tables you are going to plan around with.

Both actions can be done without locking tables. But obviously they will affect performance of running operations.

As the query optimizer is affected by the gathered statistics it is important that they are relatively fresh. PostgreSQL will auto-vacuum and auto-analyze tables at certain moments in time. It could very well happen that this happens during your tuning process. Then it might look like you improved the query, but this was due to a different query plan with the newer statistics.

You can check how recently tables where analyzed or vacuumed by checking the pg_stat_all_tables view.

But now some tricks I applied recently to improve some queries.

Too Big to Join

This first trick will seem weird. You would expect the query planner to do this by default. But apparently this was not the case on the server where I applied it. Maybe it has changed in newer versions, but for PostgreSQL this trick turned a query which took hours to execute, into a query which takes only a few seconds.

The goal of this query was to retrieve a bunch of details on orders which were changed during a a specific period. The database structure is roughly as follows.

These tables contain a lot of rows of data:

Table	Rows	Size
orders	31,889,100	15 GiB
order_line	73,699,400	70 GiB
order_line_detail	2,320,400,000	265 GiB

Attempt #1: Simple Select

The most straightforward query to get all the details of the orders which were changed in the last 6 hours would be like this:

select d.* 
from orders o
join order_line l on l.order_id = o.id
join order_line_detail d on d.line_id = l.id
where o.last_update > now() - interval '6 hours';

This query takes hours to execute. The query plan shows why. Note: because it takes a really long time, the query plans are executed without actual timings.

Hash Join  (cost=10717890.78..77356068.23 rows=823481 width=28)
  Hash Cond: (d.line_id = l.id)
  ->  Seq Scan on order_line_detail d  (cost=0.00..57928447.92 rows=2320398592 width=28)
  ->  Hash  (cost=10717563.84..10717563.84 rows=26155 width=4)
        ->  Hash Join  (cost=42202.77..10717563.84 rows=26155 width=4)
              Hash Cond: (l.order_systemid = o.id)
              ->  Seq Scan on order_line l  (cost=0.00..9938105.76 rows=73699376 width=8)
              ->  Hash  (cost=42061.31..42061.31 rows=11317 width=4)
                    ->  Index Scan using order_last_update_idx on orders o  (cost=0.57..42061.31 rows=11317 width=4)
                          Index Cond: (last_update > (now() - '06:00:00'::interval))

It selects the orders based on an index. But after that the two biggest tables receive a sequential scan. You might think indexes on the foreign keys are missing. But this is not the case.

Attempt #2: Sub-selects

So lets try something else, using sub-selects.

select d.*
from order_line_detail d
where d.line_id in (
    select l.id
    from order_line l
    where l.order_id in (
        select o.id
        from orders o
        where o.last_update > now() - interval '6 hours'
    )
);

The plan changed, looks more complex, but there are still the two really expensive sequential table scans.

Hash Join  (cost=11221517.77..94488998.34 rows=2320398592 width=28)
  Hash Cond: (d.line_id = l.id)
  ->  Seq Scan on order_line_detail d  (cost=0.00..57928447.92 rows=2320398592 width=28)
  ->  Hash  (cost=11136752.37..11136752.37 rows=6781232 width=4)
        ->  Unique  (cost=11102846.21..11136752.37 rows=6781232 width=4)
              ->  Sort  (cost=11102846.21..11119799.29 rows=6781232 width=4)
                    Sort Key: l.id
                    ->  Hash Semi Join  (cost=42195.43..10333409.79 rows=6781232 width=4)
                          Hash Cond: (l.order_systemid = o.id)
                          ->  Seq Scan on order_line l  (cost=0.00..9938105.76 rows=73699376 width=8)
                          ->  Hash  (cost=42053.99..42053.99 rows=11315 width=4)
                                ->  Index Scan using order_last_update_idx on orders o  (cost=0.57..42053.99 rows=11315 width=4)
                                      Index Cond: (last_update > (now() - '06:00:00'::interval))

Still not really usable.

Attempt #3: Common Table Expressions

PostgreSQL has support for so called Common Table Expressions (CTE). While a simple query like this has no benefit for the typical use-case for CTEs, it would not hurt to try.

The new query became:

with cte_order as (
    select o.id 
    from orders o
    where o.last_update > now() - interval '6 hours'
),
cte_line as (
    select l.id
    from order_line l
    where l.order_id in (
        select * from cte_order
    )
)
select d.*
from order_line_details d
where d.id in (select * from cte_line)

Looks quite complicated for what it is supposed to do. Basically each sub-select was moved to a CTE.

So lets take a loot at the query. (Note: execute with actual times, because it's so fast)

Nested Loop  (cost=1225650.89..19184158.78 rows=1160199296 width=28) (actual time=471.251..1171.805 rows=1500960 loops=1)
  CTE cte_order
    ->  Index Scan using co_last_update_idx on orders o  (cost=0.57..42009.62 rows=11303 width=4) (actual time=0.262..51.518 rows=8777 loops=1)
          Index Cond: (last_update > (now() - '06:00:00'::interval))
  CTE cte_line
    ->  Nested Loop  (cost=254.88..354522.71 rows=36849688 width=4) (actual time=56.620..443.167 rows=35141 loops=1)
          ->  HashAggregate  (cost=254.32..256.32 rows=200 width=4) (actual time=56.567..61.080 rows=8777 loops=1)
                ->  CTE Scan on cte_order  (cost=0.00..226.06 rows=11303 width=4) (actual time=0.265..53.986 rows=8777 loops=1)
          ->  Index Scan using order_line_order_id_idx on order_line l  (cost=0.57..1765.34 rows=599 width=8) (actual time=0.020..0.042 rows=4 loops=8777)
                Index Cond: (order_id = cte_order.id)
  ->  HashAggregate  (cost=829117.98..829119.98 rows=200 width=4) (actual time=471.222..489.199 rows=35141 loops=1)
        ->  CTE Scan on cte_line  (cost=0.00..736993.76 rows=36849688 width=4) (actual time=56.623..454.029 rows=35141 loops=1)
  ->  Index Scan using order_line_detail_entity_id_idx on order_line_detail d  (cost=0.58..89389.59 rows=40294 width=28) (actual time=0.006..0.014 rows=43 loops=35141)
        Index Cond: (line_id = cte_line.id)

There you have it. No sequential table scans, only index usage. The query took a little over 1 second to find 1,500,960 rows. The cost estimate is seriously off on this one. This is why you should not rely on just the cost estimations, but also try EXPLAIN with the ANALYZE option.

Despite that all three queries would produce the exact same output, feeding a different construction to the query planner can give quite a different result.

Pivot Point

This next trick cost me a lot of time to execute. First of all I had to properly format this massive 350 line long reporting query into something that was readable.

Part of the query sort of looked like this.

with order_price_transport as (
    select orders.id, price_parts.code, price_parts.status, sum(price_parts.amount)
    from orders
    join price_parts on price_parts.order_id = orders.id
    where price_parts.code in ('FUEL', FREIGHT)
    group by orders.id, price_parts.code, price_parts.status
),
-- ...
order_price_other as (
    select orders.id, price_parts.code, price_parts.status, sum(price_parts.amount)
    from orders
    join price_parts on price_parts.order_id = orders.id
    where price_parts.code not in ('FUEL', 'FREIGHT', ...)
    group by orders.id, price_parts.code, price_parts.status
),
-- ... a whole bunch bunch of other CTEs
select
    orders.id,
    -- ...
    est_trans_price.amount,
    act_trans_price.amount,
    est_other_price.amount,
    act_other_price.amount,
    -- ...
from orders
join order_price_transport as est_trans_price on est_trans_price.id = orders.id and est_trans_price.status = 'estimated'
join order_price_transport as act_trans_price on act_trans_price.id = orders.id and act_trans_price.status = 'actual'
-- ...
join order_price_other as est_other_price on est_other_price.id = orders.id and est_other_price.status = 'estimated'
join order_price_other as act_other_price on act_trans_price.id = orders.id and act_other_price.status = 'actual'
-- ...
where orders.creation_date between $P{START_DATE} and $P{END_DATE}
and orders.organization_id in ($P!{ORGANIZATION_LIST})
and orders.status_id in (123, 456)

The places where I put -- ... contained even more stuff. Simple to say, this report did not run well. It contained a lot of similar common table expressions (CTEs). Some of them were even joined multiple times.

The results of CTEs, and views for that matter, are not cached. They are executed every time they are used. So just from the query which is shown above the price_parts table is queried 4 times already.

You will note that the main query contains some where clauses on the orders table. These were not included in the CTEs which basically used the same order rows. Copying those where clauses to each CTE already makes quite the improvement. But the price_parts and orders tables are still consulted much more than needed.

The price_parts table can contain multiple rows of price parts of an order. Each row has a code, amount, and status (if it was an estimation, or the actual price). Part of this report was to produce an overview of certain groups of prices for a selection of orders. Basically, it was meant to pivot the rows of the price_parts table to columns in the resulting report.

There is no need to pivot this data at the last possible moment. You can do that in a CTE too. So that's what I did. I combined all the CTEs reasoning on the same tables where the different was mostly the content of the where clauses. This transformed the above query to this (maybe even more complex looking query)

with order_prices as (
    select
        id,
        sum(case when status = 'estimated' and code = 'trans' then amount else 0 end) as est_trans_price,
        sum(case when status = 'actual' and code = 'trans' then amount else 0 end) as act_trans_price,
        -- ...
        sum(case when status = 'estimated' and code = 'other' then amount else 0 end) as est_other_price,
        sum(case when status = 'actual' and code = 'other' then amount else 0 end) as act_other_price,
    from (
        select
            orders.id,
            case 
                when price_part.code in ('FUEL', FREIGHT) then 'trans'
                -- ...
                else 'other'
            end as code,
            price_part.status,
            sum (price_part.amount) as amount
        from orders
        join price_part on price_part.order_id = orders.id
        where orders.creation_date between $P{START_DATE} and $P{END_DATE}
            and orders.organization_id in ($P!{ORGANIZATION_LIST})
            and orders.status_id in (123, 456)
        group by orders.id, price_parts.code, price_parts.status
    ) as data
    group by id
)
-- ...
select 
    orders.id
    -- ...
    op.est_trans_price,
    op.act_trans_price,
    op.est_other_price,
    op.act_other_price
from orders
join order_prices op on op.id = orders.id
-- ...
where orders.creation_date between $P{START_DATE} and $P{END_DATE}
and orders.organization_id in ($P!{ORGANIZATION_LIST})
and orders.status_id in (123, 456)

In the most inner select of the order_prices CTE all the relevant data is gathered from the price_part table, duplicated the filters on the applicable orders rows. The rows are are grouped on the order ID, price part code, and status, and a summation is done of the amounts. Instead of returning the actual code of the price_part row, it is translated into a new group which will be used during the pivot.

This select query is then used as a table for the outer select of the order_prices CTE. This is where the pivot takes place. Everything of the data is selected, and grouped on just the id. For each code and status a column is made using a conditional sum aggregation.

While the resulting query was not much smaller, as the new CTEs take almost the same amount of lines (only 50 lines less). It did however reduce 21 joins of CTEs to only 5, as there were now only 5 CTEs. Not only are the number of joins less, the side of the data to carry over in each part of the query is also less, and thus less memory is needed to execute the query.

With these changes the report execution time dropped from 30 minutes, to just 1 minute.

Limitless

So this is a case where the query ran well, until the point where we changed the server configuration to make better use of the available system memory.

The query is quite simple:

select *
from orders 
where organization_id in (7000)
and status_id in (2, 28)
and order_type_id in (146630533)
and system_entry_date < '2019-03-24 23:30:03.524'
order by system_entry_date asc
limit 25

This is the query plan (no actual times, because it takes too long).

Limit  (cost=0.56..23639.89 rows=25 width=2972)
  ->  Index Scan using idx_order_entry_date on orders  (cost=0.56..10885436.12 rows=11512 width=2972)
        Index Cond: (system_entry_date < '2019-03-24 23:30:03.524'::timestamp without time zone)
        Filter: ((status_id = ANY ('{2,28}'::integer[])) AND (organization_id = 7000) AND (order_type_id = 146630533))

On first sight it looks ok. Except that it is using the index order_entry_date_idx which is not the best index to use. There is a more specific index which also includes some other columns. With this query plan it is basically executing a sequential table scan. The index still selects the majority of the rows, it's the subsequent filtering which reduces the number of rows.

If I drop the limit 25 the query plan changes drastically:

Sort  (cost=36349.54..36378.32 rows=11512 width=2972) (actual time=0.163..0.163 rows=0 loops=1)
  Sort Key: system_entry_date
  Sort Method: quicksort  Memory: 25kB
  ->  Index Scan using idx_order_statustype on orders  (cost=0.56..35573.01 rows=11512 width=2972) (actual time=0.136..0.136 rows=0 loops=1)
        Index Cond: ((status_id = ANY ('{2,28}'::integer[])) AND (order_type_id = 146630533) AND (system_entry_date < '2019-03-24 23:30:03.524'::timestamp without time zone))
        Filter: (organization_id = 7000)

This time the correct index is being used. Even though the estimated costs are higher, the actual time is much better.

If I drop the order by in the query instead, it also uses the correct index. Note that due to the nature of the b-tree index the results would actually be returned in ascending order to being with.

Limit  (cost=0.56..77.82 rows=25 width=2972) (actual time=0.142..0.142 rows=0 loops=1)
  ->  Index Scan using idx_order_statustype on orders  (cost=0.56..35573.01 rows=11512 width=2972) (actual time=0.135..0.135 rows=0 loops=1)
        Index Cond: ((status_id = ANY ('{2,28}'::integer[])) AND (order_type_id = 146630533) AND (system_entry_date < '2019-03-24 23:30:03.524'::timestamp without time zone))
        Filter: (organization_id = 7000)

If I change the in (2, 28) into in (2) again the correct index is used. It does not matter if you do foo in (2, 28) or foo = 2 or foo = 28.

Limit  (cost=0.56..63.64 rows=19 width=2972) (actual time=0.105..0.105 rows=0 loops=1)
  ->  Index Scan using idx_order_statustype on orders  (cost=0.56..63.64 rows=19 width=2972) (actual time=0.103..0.103 rows=0 loops=1)
        Index Cond: ((status_id = 2) AND (order_type_id = 146630533) AND (system_entry_date < '2019-03-24 23:30:03.524'::timestamp without time zone))
        Filter: (organization_id = 7000)

Even increasing the limit to 40 produces a better result

Limit  (cost=35936.90..35937.00 rows=40 width=2972) (actual time=3.078..3.078 rows=0 loops=1)
  ->  Sort  (cost=35936.90..35965.68 rows=11512 width=2972) (actual time=3.076..3.076 rows=0 loops=1)
        Sort Key: system_entry_date
        Sort Method: quicksort  Memory: 25kB
        ->  Index Scan using idx_order_statustype on orders  (cost=0.56..35573.01 rows=11512 width=2972) (actual time=3.066..3.066 rows=0 loops=1)
              Index Cond: ((status_id = ANY ('{2,28}'::integer[])) AND (order_type_id = 146630533) AND (system_entry_date < '2019-03-24 23:30:03.524'::timestamp without time zone))
              Filter: (organization_id = 7000)

The query planner is clearly way too optimistic in using the idx_order_entry_date index. If you look at the first query plan you see that the pessimistic cost of using the wrong index is actually quite high. But the limit reduces the cost a lot. It goes from cost=0.56..10885436.12 to cost=0.56..23639.89.

After some searching this appears to be a rather complex bug in PostgreSQL.

In this particular case there were two suitable options available to improve the query:

a higher limit; it only needed to be slightly higher, which did not impact the rest of the process.
no ordering; this was not vital anyway, and the returned order was good enough already.

In some database system you would solve this problem by using index hinting. But this is not something PostgreSQL developers consider introducing.

The trick here is that sometimes relaxing some constraints could result in a better performance. It might seem like a workaround of a bug, which is also is, but sometimes you need these tricks. This problem was observed in 9.5, it could very well not be there anymore in a newer version.

Indexes go wild

B-Tree indexes (the default type) can also be used in wildcard searches when the wildcard is on the end: col LIKE 'foo%'.

There is a small gotcha though. If the used collation is not 'C' then it does not work out of the box.

create index city_name_idx on postal_location(city_name);

explain (analyze) select * from postal_location 
where city_name like 'LONDON%';

Seq Scan on postal_location  (cost=0.00..69965.75 rows=4392 width=75) (actual time=114.905..324.757 rows=4346 loops=1)
  Filter: ((city_name)::text ~~ 'LONDON%'::text)
  Rows Removed by Filter: 1974114

You need to explicitly define the index's operator class to use a pattern variant.

create index city_name_idx on postal_location(city_name varchar_pattern_ops);

explain (analyze) select * from postal_location 
where city_name like 'LONDON%';

Bitmap Heap Scan on postal_location  (cost=111.74..12683.84 rows=4392 width=75) (actual time=0.288..0.812 rows=4346 loops=1)
  Filter: ((city_name)::text ~~ 'LONDON%'::text)
  ->  Bitmap Index Scan on city_name_idx  (cost=0.00..110.64 rows=4221 width=0) (actual time=0.277..0.277 rows=4346 loops=1)
        Index Cond: (((city_name)::text ~>=~ 'LONDON'::text) AND ((city_name)::text ~<~ 'LONDOO'::text))

Note that you should also create an index with the default operator class if you want queries involving ordinary <, <=, >, or >= comparisons to use an index. Such queries cannot use the xxx_pattern_ops operator classes.

Leading Wildcards

If the wildcard is at the beginning, this will not work and you are back to a sequential scan of the table.

explain (analyze) select * from postal_location 
where city_name like '%LONDON';

Seq Scan on postal_location  (cost=0.00..69965.75 rows=4392 width=75) (actual time=118.001..361.855 rows=4316 loops=1)
  Filter: ((city_name)::text ~~ '%LONDON'::text)
  Rows Removed by Filter: 1974144

However you can create an index with an expression to index the reversed value.

create index city_name_rev_idx on postal_location(reverse(city_name) varchar_pattern_ops);

explain (analyze) select * from postal_location 
where reverse(city_name) like reverse('%LONDON');

Bitmap Heap Scan on postal_location  (cost=257.83..24199.06 rows=9892 width=75) (actual time=0.365..1.133 rows=4316 loops=1)
  Filter: (reverse((city_name)::text) ~~ 'NODNOL%'::text)
  ->  Bitmap Index Scan on city_name_rev_idx  (cost=0.00..255.35 rows=9892 width=0) (actual time=0.351..0.351 rows=4316 loops=1)
        Index Cond: ((reverse((city_name)::text) ~>=~ 'NODNOL'::text) AND (reverse((city_name)::text) ~<~ 'NODNOM'::text))

The cost estimation is much higher than of the normal index, but still much lower than the sequential table scan. And more importantly, the actual time is almost comparable to normal index, and significantly better than the sequential table scan.

The reverse wildcard index is especially useful for places where people generally enter the last few characters instead of the first few. For example in case of order numbers.

Readable Code

The last trick should be a no-brainer. But as I mentioned earlier, I had to reformat a query before I could even start optimizing it.

I handled an other huge reporting query, which was really badly formatted. While formatting the query so that I could try to figure out what it was actually doing I saw something which might be a typo. In one of the sub-selects a wrong table alias was used. When I corrected the alias to the one I though was correct the query was really fast, and apparently the output was correct too.

This typo would probably have been easily spotted by the original author if they formatted their SQL properly.

To this day, I do not know what this query was actually supposed to do. But the output was correct, the query was fast, and I was done dealing with this issue within 30 minutes.

Terrible Interview Questions

Michiel Hendriks — Mon, 29 Apr 2019 11:27:24 +0000

Lets have some fun, and come up with the worst job interview question you can think of.

We have all heard about the job interview questions which are simply bad. Questions which test no practical skill, like asking people to write a quick sort implementation on a whiteboard. Or simply weird questions, which you have no idea about what they could imply, like "Are you a dog person or cat person?".

So let me hear the worst interview questions you can come up with. Which are so bad, that they become funny. (Bonus points if this was an actual question.)