EXPLAIN EXTENDED

How to create fast database queries

Archive for the ‘SQL’ tag

Happy New Year!

with 3 comments

Last year, my good friend and colleague Matt Ward challenged me to implement a quantum computer emulator in SQL.

Challenge accepted!

This year we will be building an SQL query which will emulate a quantum computer. This query will process quantum assembly, build the circuit, run the emulation and make the measurements.

First things first, a little bit of theory. I won't go deep into quantum mechanics now (primarily because I don't understand it well enough to talk about it in public). What we really need to know about emulating a quantum computer, is that it's all about matrix multiplication. Quantum computers run on physical effects which are hard to wrap one's head around, but relatively easy to express using quite simple math. This math is something you can work with, even if you don't understand the physics behind it on an intuitive level.

Theory

For this article, I will assume that you are familiar with the mathematics of matrix multiplication. If you're not, you'll need to read up a little bit on linear algebra. This is not a particularly hard topic, and it's being used heavily in many areas of programming: image processing, sound processing, quantitative finance analysis and many others. It is very rewarding to be familiar with it.

Qubits

So, quantum computers have registers (tiny blocks of memory), in pretty much the same way as the CPU in your laptop or phone does. The data stored in your CPU registers tells it what to do next, and these registers are being constantly updated as your CPU runs code.

Classic registers have bits, which store zeros and ones. These are exclusive: if the bit is on, it's not off, and if it's off, it's not on.

Quantum registers have qubits, which also store zeros and ones. But these zeros and ones are not exclusive. A qubit may be on, may be off, and may be somewhere in between. It's not like an on-off switch, but more like a computer trackball with a permanent marker dot on it. You can turn any way you like, and the dot position reflects the state of the qubit. The closer the dot is to the top (or to the bottom), the more "zero" or "one" the qubit is. The marked dot on the trackball can also turn about the vertical axis, which is also something that the qubit can store.

Read the rest of this entry »

Written by Quassnoi

December 31st, 2021 at 11:00 pm

Happy New Year!

with one comment

In my New Year posts I usually try to recap and summarize the past year. It won't take long this time:

Fuck you, coronavirus!

Now that I've gotten that off my chest, I have to think of something to write about in this New Year's post.

So I was thinking, why not put a face to the name we all hate so much?

Let's use SQL to do some ray tracing and draw a 3D picture of the dreaded virus.

By now, I believe we are all familiar with the picture of the virus. It looks like a ball covered with spikes. The spikes look something like the solar corona, which is what gave the virus its name. They have this distinct triangular shape.

We'll create a sphere covered with several dozens of spikes.

Every spike will be a small pyramid, with an equilateral triangle as a base and isosceles triangles as lateral faces. This means it will be a right pyramid.

The pyramids will be "standing" on their apexes, upside-down. The height of every pyramid will be perpendicular to the sphere surface and continue the sphere's radius at the apex.

Then we will implement the pinhole camera model and use ray tracing algorithms to calculate the lighting of the sphere and the spikes.

Types

3D modeling heavily uses vector algebra. Of course pure SQL offers enough math functions to get around. But functions and routines are not first class citizens in SQL, which means we would have to copy-paste the bulky vector manipulation formulas every time we will need them, which would make our query unwieldy really fast.

This is a good chance to get familiar with PostgreSQL's rich system of custom types and custom operators. It lets users define their own types, create functions to work with them and even overload the operators.
Read the rest of this entry »

Written by Quassnoi

December 31st, 2020 at 11:00 pm