\timing on: Comparing two tables - Postgres
Say you have carefully tuned your database and wow! it is ten times faster -- but, before you push it to the production cluster, how do you know the answer of the query is correct? Or at least, same as before? :-) You may have same question if you want to upgrade or migrate your database, or, for us, we want to make sure the new great join algorithm actually produce correct answer. Well, let's just put the result before, and after, in two tables, and compare the two tables.
Wed Jun 17 18:02:16 2015 - permalink -
This is great, but turns out to be quite tricky. You need to take care of the cases that,
Either table may not have a primary key
Rows in the two tables may be physically stored in different orders.
Columns could contain nulls.
The two tables could be huge, so performance must be good.
Here are something you could do.