$ rake pg:query_time
total time (min) ▾ | average execution (ms) | query
--------------------+------------------------+------------------------------
2.0379 | 1153.5163 | SELECT COUNT(*) FROM "users" WHERE ("users"."username" ILIKE ?)
1.3497 | 809.8202 | INSERT INTO users (email, username, auth_token) VALUES (?, ?, ?), ...
0.0056 | 111.416 | SELECT COUNT(*) FROM "users" WHERE ("users"."avatar_file_name" ILIKE ?)
(3 rows)
SchemaSpy is a neat tool to produce visual diagrams for most relational databases.
Here's how to use it to generate schema relationship diagrams for PostgreSQL databases:
-
Download the jar file from here (the current version is schemaSpy_5.0.0.jar)
-
Get the PostgreSQL JDBC driver (either the JDBC3 or JDBC4 jar file is fine)
-
Run the command against an existing database. For most databases, the schema (-s option) we are interested in is the public one:
This file contains 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
CREATE OR REPLACE FUNCTION random_int_array(dim integer, min integer, max integer) RETURNS integer[] AS $BODY$ | |
begin | |
return (select array_agg(round(random() * (max - min)) + min) from generate_series (0, dim)); | |
end | |
$BODY$ LANGUAGE plpgsql; | |
-- usage example | |
select random_int_array(15, 6, 40); | |
-- return example |
This file contains 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
-- Create tables | |
CREATE TABLE groups (id serial NOT NULL, name character varying NOT NULL); | |
CREATE TABLE network_group_members (pid integer, cid integer NOT NULL); | |
-- Insert the groups | |
INSERT INTO groups (name) VALUES ('GROUP 1'),('GROUP 2'),('GROUP 3'),('GROUP 4'),('GROUP 5'),('GROUP 6'),('GROUP 7'),('GROUP 8'),('GROUP 9'),('GROUP 10'),('GROUP 11'),('GROUP 12'),('GROUP 13'); | |
-- Build the "Network" | |
INSERT INTO network_group_members(pid,cid) VALUES (1,2),(1,3),(1,4),(2,5),(5,6),(5,7),(7,8),(3,9),(4,10),(4,11),(11,12),(12,13); |
The standard names for indexes in PostgreSQL are:
{tablename}_{columnname(s)}_{suffix}
where the suffix is one of the following:
pkey
for a Primary Key constraint;key
for a Unique constraint;excl
for an Exclusion constraint;idx
for any other kind of index;