1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
|
CREATE SCHEMA "PGQS";
CREATE EXTENSION pg_qualstats WITH SCHEMA "PGQS";
-- Make sure that installcheck won't find previous data
SELECT "PGQS".pg_qualstats_reset();
-- Make sure sure we'll see at least one qual
SET pg_qualstats.sample_rate = 1;
CREATE TABLE pgqs AS SELECT id, 'a'::text val FROM generate_series(1, 100) id;
SELECT COUNT(*) FROM pgqs WHERE id = 1;
SELECT lrelid::regclass::text, lattnum, occurences, execution_count,
nbfiltered, constvalue, eval_type
FROM "PGQS".pg_qualstats;
SELECT COUNT(*) > 0 FROM "PGQS".pg_qualstats;
SELECT COUNT(*) > 0 FROM "PGQS".pg_qualstats();
SELECT COUNT(*) > 0 FROM "PGQS".pg_qualstats_example_queries();
SELECT "PGQS".pg_qualstats_reset();
SELECT COUNT(*) FROM "PGQS".pg_qualstats();
-- OpExpr sanity checks
-- subquery_var operator const, shouldn't be tracked
SELECT * FROM (SELECT * FROM pgqs LIMIT 0) pgqs WHERE pgqs.id = 0;
SELECT COUNT(*) FROM "PGQS".pg_qualstats();
-- const non_commutable_operator var, should be tracked, var found on RHS
SELECT * FROM pgqs WHERE 'meh' ~ val;
SELECT lrelid::regclass, lattnum, rrelid::regclass, rattnum FROM "PGQS".pg_qualstats();
SELECT "PGQS".pg_qualstats_reset();
-- opexpr operator var and commuted, shouldn't be tracked
SELECT * FROM pgqs WHERE id % 2 = 3;
SELECT * FROM pgqs WHERE 3 = id % 2;
SELECT COUNT(*) FROM "PGQS".pg_qualstats();
-- same query with handled commuted qual, which should be found as identical
SELECT * FROM pgqs WHERE id = 0;
SELECT * FROM pgqs WHERE 0 = id;
SELECT lrelid::regclass, lattnum, rrelid::regclass, rattnum, sum(occurences)
FROM "PGQS".pg_qualstats()
GROUP by 1, 2, 3, 4;
SELECT COUNT(DISTINCT qualnodeid) FROM "PGQS".pg_qualstats();
-- (unique)qualid behavior
SELECT "PGQS".pg_qualstats_reset();
-- There should be one group of 2 AND-ed quals, and 1 qual alone
SELECT COUNT(*) FROM pgqs WHERE (id = 1) OR (id > 10 AND id < 20);
SELECT CASE WHEN qualid IS NULL THEN 'OR-ed' ELSE 'AND-ed' END kind, COUNT(*)
FROM "PGQS".pg_qualstats() GROUP BY 1 ORDER BY 2 DESC;
----------------
-- index advisor
----------------
-- check that empty arrays are returned rather than NULL values
SELECT "PGQS".pg_qualstats_reset();
SELECT * FROM "PGQS".pg_qualstats_index_advisor(50);
-- Test some naive scenario
CREATE TABLE adv (id1 integer, id2 integer, id3 integer, val text);
INSERT INTO adv SELECT i, i, i, 'line ' || i from generate_series(1, 1000) i;
SELECT "PGQS".pg_qualstats_reset();
SELECT * FROM adv WHERE id1 < 0;
SELECT count(*) FROM adv WHERE id1 < 500;
SELECT * FROM adv WHERE val = 'meh';
SELECT * FROM adv WHERE id1 = 0 and val = 'meh';
SELECT * FROM adv WHERE id1 = 1 and val = 'meh';
SELECT * FROM adv WHERE id1 = 1 and id2 = 2 AND val = 'meh';
SELECT * FROM adv WHERE id1 = 6 and id2 = 6 AND id3 = 6 AND val = 'meh';
SELECT COUNT(*) FROM pgqs WHERE id = 1;
-- non optimisable statements
SELECT * FROM adv WHERE val ILIKE 'moh';
SELECT count(*) FROM adv WHERE val ILIKE 'moh';
SELECT * FROM adv WHERE val LIKE 'moh';
-- check the results
SELECT v->'ddl' AS v
FROM json_array_elements(
"PGQS".pg_qualstats_index_advisor(50)->'indexes') v
ORDER BY v::text COLLATE "C";
SELECT v->'qual' AS v
FROM json_array_elements(
"PGQS".pg_qualstats_index_advisor(50)->'unoptimised') v
ORDER BY v::text COLLATE "C";
-- check quals on removed table
DROP TABLE pgqs;
SELECT v->'ddl' AS v
FROM json_array_elements(
"PGQS".pg_qualstats_index_advisor(50)->'indexes') v
ORDER BY v::text COLLATE "C";
|
