Last active
December 23, 2019 15:59
-
-
Save tuwukee/fbe22f3ddd80eb87d0d27595b3a9e7a8 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DO $$ | |
| DECLARE | |
| v_ts TIMESTAMP; | |
| v_repeat CONSTANT INT := 10000; | |
| rec RECORD; | |
| BEGIN | |
| -- Repeat the whole benchmark several times to avoid warmup penalty | |
| FOR i IN 1..5 LOOP | |
| v_ts := clock_timestamp(); | |
| FOR i IN 1..v_repeat LOOP | |
| FOR rec IN ( | |
| SELECT DISTINCT result.request_json->>'target' as target, | |
| result.request_json->>'return_to' as return_to | |
| FROM ( | |
| SELECT "request_params"::json as request_json | |
| FROM "histories" | |
| WHERE (created_at > '2019-12-05 00:00:00') | |
| ) as result | |
| WHERE ( | |
| result.request_json->>'target' IS NOT NULL | |
| OR result.request_json->>'return_to' IS NOT NULL | |
| ) | |
| ) LOOP | |
| NULL; | |
| END LOOP; | |
| END LOOP; | |
| RAISE INFO 'Run %, Statement 1: %', i, (clock_timestamp() - v_ts); | |
| v_ts := clock_timestamp(); | |
| FOR i IN 1..v_repeat LOOP | |
| FOR rec IN ( | |
| SELECT DISTINCT regexp_matches("request_params", '(?<="(target|return_to)":")([^"]+)') | |
| FROM "histories" | |
| WHERE (created_at > '2019-12-05 00:00:00') AND | |
| (("request_params" ILIKE '%"target":%') OR ("request_params" ILIKE '%"return_to":%')) | |
| ) LOOP | |
| NULL; | |
| END LOOP; | |
| END LOOP; | |
| RAISE INFO 'Run %, Statement 2: %', i, (clock_timestamp() - v_ts); | |
| v_ts := clock_timestamp(); | |
| FOR i IN 1..v_repeat LOOP | |
| FOR rec IN ( | |
| SELECT "request_params" | |
| FROM "histories" | |
| WHERE (created_at > '2019-12-05 00:00:00') AND | |
| (("request_params" ILIKE '%"target":%') OR ("request_params" ILIKE '%"return_to":%')) | |
| ) LOOP | |
| NULL; | |
| END LOOP; | |
| END LOOP; | |
| RAISE INFO 'Run %, Statement 3: %', i, (clock_timestamp() - v_ts); | |
| v_ts := clock_timestamp(); | |
| FOR i IN 1..v_repeat LOOP | |
| FOR rec IN ( | |
| SELECT DISTINCT result.request_json->>'target' as target, | |
| result.request_json->>'return_to' as return_to | |
| FROM ( | |
| SELECT "request_params"::json as request_json | |
| FROM "histories" | |
| WHERE (created_at > '2019-12-05 00:00:00') AND | |
| (("request_params" ILIKE '%"target":%') OR ("request_params" ILIKE '%"return_to":%')) | |
| ) as result | |
| ) LOOP | |
| NULL; | |
| END LOOP; | |
| END LOOP; | |
| RAISE INFO 'Run %, Statement 4: %', i, (clock_timestamp() - v_ts); | |
| END LOOP; | |
| END$$; | |
| INFO: Run 1, Statement 1: 00:00:00.429307 | |
| INFO: Run 1, Statement 2: 00:00:01.010551 | |
| INFO: Run 1, Statement 3: 00:00:00.199064 | |
| INFO: Run 1, Statement 4: 00:00:00.404594 | |
| INFO: Run 2, Statement 1: 00:00:00.448713 | |
| INFO: Run 2, Statement 2: 00:00:01.0151 | |
| INFO: Run 2, Statement 3: 00:00:00.200406 | |
| INFO: Run 2, Statement 4: 00:00:00.398814 | |
| INFO: Run 3, Statement 1: 00:00:00.427719 | |
| INFO: Run 3, Statement 2: 00:00:00.982301 | |
| INFO: Run 3, Statement 3: 00:00:00.195787 | |
| INFO: Run 3, Statement 4: 00:00:00.393451 | |
| INFO: Run 4, Statement 1: 00:00:00.427113 | |
| INFO: Run 4, Statement 2: 00:00:00.980525 | |
| INFO: Run 4, Statement 3: 00:00:00.195361 | |
| INFO: Run 4, Statement 4: 00:00:00.393792 | |
| INFO: Run 5, Statement 1: 00:00:00.433818 | |
| INFO: Run 5, Statement 2: 00:00:00.988859 | |
| INFO: Run 5, Statement 3: 00:00:00.197858 | |
| INFO: Run 5, Statement 4: 00:00:00.394347 | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment