· 6 years ago · Jul 19, 2019, 08:36 AM
1
2CREATE EXTENSION IF NOT EXISTS pg_stat_statements WITH SCHEMA public;
3
4
5SELECT * FROM pg_stat_statements
6where queryid = '709931593'; --2401 -- SELECT DISTINCT "ownerId" FROM trades."getAvailableSwaps"($1, $2, $3)
7
8SELECT * FROM pg_stat_statements
9where queryid = '3415975819'; -- 2401 -- SELECT "ownerId", "albumId", "clubId", "collectibleId", "cardId", "slotId", label, "wishlistMatches" FROM trades."getAvailableSwaps"($1, $2, $3)
10
11SELECT calls, * FROM pg_stat_statements
12where queryid = '888269665';
13
14SELECT calls, * FROM pg_stat_statements
15where queryid = '888269665';
16
17SELECT calls, * FROM pg_stat_statements
18where queryid = '2019038358'; -- 4802 -- Taking an example of Bob, who has Aguero on his wishlist and Sane is available to swap
19SELECT col2.ownerid as "ownerId", col2.albumid as "albumId", col2.clubid as "clubId",
20 col2.collectibleid as "collectibleId", col2.cardid as "cardId", col2.slotid as "slotId", col2.label,
21 array_agg(json_build_object($4, col3.slotid, $5, col3.cardid)::jsonb) as "wishlistMatches"
22FROM trades.connections con
23 INNER JOIN trades.collectibles col1 on con.profileid = col1.ownerid -- Bob's collectibles
24 INNER JOIN trades.collectibles_extended col2 on con.connectedto = col2.ownerid -- Bob's connections who have Aguero to swap
25 INNER JOIN trades.collectibles col3 on con.connectedto = col3.ownerid -- Bob's connections who want Sane from Bob
26WHERE col1.ownerid = $1
27 AND
28 -- Bob's connections who have Aguero to swap
29 con.profileid = $1 AND ($2::TEXT IS NULL OR col2.slotid = $2) AND
30 ($3::TEXT IS NULL OR col2.cardid = $3) AND col2.status = $6
31 AND
32 -- Bob's connections who want Sane from Bob
33 col1.slotid = col3.slotid AND col1.cardid = col3.cardid AND col1.status = $7
34 AND col3.status = $8
35GROUP BY col2.ownerid, col2.albumid, col2.clubid, col2.collectibleid, col2.cardid, col2.slotid, col2.label
36
37SELECT profiles.profiles.*, (SELECT COUNT(*) FROM profiles.connection_requests
38 WHERE ("receiverProfileId" = CAST(profiles.id as TEXT) OR "senderProfileId" = CAST(profiles.id as TEXT))
39 AND state = ?) AS connectioncount FROM profiles.profiles
40WHERE profiles.id NOT IN(
41 SELECT "receiverProfileId" FROM profiles.connection_requests WHERE "senderProfileId" = ? OR "receiverProfileId" = ? OR profiles.id = ?)
42 AND "dateLastActive" > NOW() - INTERVAL ?
43ORDER BY connectioncount
44LIMIT ?
45
46
47SELECT * FROM pg_stat_statements_reset();
48
49-- SELECT substring(query, 1, 1000) AS short_query,
50-- round(total_time::numeric, 2) AS total_time,
51-- rows AS total_rows_returned,
52-- calls,
53-- round((rows / calls::numeric)::numeric, 2) AS average_rows_per_call,
54-- round(max_time::numeric, 2) AS max,
55-- round(mean_time::numeric, 2) AS mean,
56-- round(min_time::numeric, 2) AS min,
57-- round((100 * total_time / sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu
58-- FROM pg_stat_statements where query like '%dateLastActive%'
59
60-- Total time, calls, mean and % CPU
61SELECT pg_database.datname,
62 queryid,
63 substring(query, 1, 1000) AS short_query,
64 round(total_time::numeric, 2) AS total_time,
65 rows AS total_rows_returned,
66 calls,
67 round((rows / calls::numeric)::numeric, 2) AS average_rows_per_call,
68 round((mean_time + (2 * stddev_time))::numeric, 2) AS ninety_fifth_percentile,
69 round(max_time::numeric, 2) AS max,
70 round(mean_time::numeric, 2) AS mean,
71 round(stddev_time::numeric, 2) AS stddev_time,
72 round(min_time::numeric, 2) AS min,
73 round((100 * total_time / sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu
74FROM pg_stat_statements
75 JOIN pg_database ON pg_database.oid = pg_stat_statements.dbid
76where query not in ('SELECT NOW()', '<insufficient privilege>')
77ORDER BY max DESC
78LIMIT 15;
79
80SELECT calls, substring(query, 1, 200) AS short_query
81FROM pg_stat_statements
82ORDER BY calls DESC
83LIMIT 30;
84
85SELECT *
86FROM pg_stat_statements where queryid = '1961329451';
87
88-- Number of connections by DB, with Client IP
89-- Note - "pg_stat_activity is not an auditing facility. It only shows one query per connected session. Either the query which is currently executing, or if the connection is idle than the immediately prior query."
90select client_addr, usename, datname, count(*) from pg_stat_activity group by 2,1,3 order by 4 desc;
91
92select count(*)*100/(select current_setting('max_connections')::int) from pg_stat_activity;
93
94-- Current running queries longer than 2 seconds
95SELECT now() - query_start as "runtime", usename, datname, state, query
96FROM pg_stat_activity
97WHERE now() - query_start > '2 seconds'::interval
98ORDER BY runtime DESC;
99
100
101-- Data Integrity - Anomalies.
102-- c_commit_ratio should be > 95%
103-- c_rollback_ratio should be < 5%
104-- deadlocks should be close to 0
105-- conflicts should be close to 0
106-- temp_files and temp_bytes watch out for them
107select datname,
108 (xact_commit*100)/(xact_commit+xact_rollback + 1) as c_commit_ratio,
109 (xact_rollback*100)/(xact_commit+xact_rollback + 1) as c_rollback_ratio,
110 deadlocks, conflicts, temp_files, pg_size_pretty(temp_bytes)
111from pg_stat_database;
112
113
114SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, n_live_tup rows_in_table
115FROM pg_stat_user_tables
116ORDER BY n_live_tup DESC;
117
118select * from pg_stat_all_indexes
119
120
121
122create view profiles."profilesExtended" as
123 SELECT profiles.id,
124 profiles.username,
125 profiles.email,
126 profiles."firstName",
127 profiles.country,
128 profiles.dob,
129 profiles."albumIds",
130 profiles."dateLastActive",
131 profiles."profileEstablished",
132 profiles."miniBio",
133 profiles."usernameCheck",
134 profiles."lastName",
135 profiles.avatar,
136 profiles."clubLoyalty",
137 profiles."emailPreference",
138 profiles."invitationId",
139 profiles.verified,
140 profiles.connected,
141 profiles."favouritePlayerId",
142 profiles."favouriteClubId",
143 profiles."marketingOptIn",
144 profiles."dateModified",
145 CASE
146 WHEN ((profiles.id IS NOT NULL) AND (profiles.username IS NOT NULL) AND (profiles.email IS NOT NULL) AND
147 (profiles."firstName" IS NOT NULL) AND (profiles."lastName" IS NOT NULL) AND
148 (profiles.country IS NOT NULL) AND (profiles.dob IS NOT NULL) AND (profiles."albumIds" IS NOT NULL) AND
149 (profiles."dateLastActive" IS NOT NULL) AND (profiles."profileEstablished" IS NOT NULL) AND
150 (profiles."usernameCheck" IS NOT NULL) AND (profiles.avatar IS NOT NULL) AND
151 (profiles."clubLoyalty" IS NOT NULL) AND (profiles."emailPreference" IS NOT NULL) AND
152 (profiles.verified IS NOT NULL) AND (profiles."favouriteClubId" IS NOT NULL) AND
153 (profiles."favouritePlayerId" IS NOT NULL) AND (profiles."marketingOptIn" IS NOT NULL) AND
154 (profiles."dateModified" IS NOT NULL)) THEN true
155 ELSE false END AS "isComplete",
156 "favouritePlayers"."playerName" AS "favouritePlayerName",
157 "favouriteClubs"."clubName" AS "favouriteClubName"
158 FROM ((profiles.profiles
159 LEFT JOIN profiles."favouritePlayers" ON ((profiles."favouritePlayerId" = "favouritePlayers".id)))
160 LEFT JOIN profiles."favouriteClubs" ON ((profiles."favouriteClubId" = "favouriteClubs".id)))
161;
162
163alter table profiles."profilesExtended" owner to flyway
164;
165
166
167-- delete from pg_stat_statements where queryid = '412252226'
168select * from pg_stat_statements where queryid = '412252226'
169
170select * from pg_stat_activity order by client_addr;
171
172SELECT bl.pid AS blocked_pid,
173 a.usename AS blocked_user,
174 ka.query AS blocking_statement,
175 now() - ka.query_start AS blocking_duration,
176 kl.pid AS blocking_pid,
177 ka.usename AS blocking_user,
178 a.query AS blocked_statement,
179 now() - a.query_start AS blocked_duration
180FROM pg_catalog.pg_locks bl
181 JOIN pg_catalog.pg_stat_activity a ON a.pid = bl.pid
182 JOIN pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
183 JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
184WHERE NOT bl.GRANTED;