_edited_edited.png)
World of PostgreSQL - Miscellaneous Mystical Luminesce
In this blog, we will see some miscellaneous concepts of Postgresql.
Topics covered include:
Materialized Views.
Updatable views with CASCADED CHECK OPTION.
Create Partition on a table and insert rows to check data is rightly partitioned.
Working with JSON data.
Create a Parametric Cursor.
Listing psql utilities along with returning the query every N seconds.
Create user defined -domain datatype for address, postal code, email, enum.
Create a custom index method.
Using GRANT/REVOKE for security using roles and users.
Write a custom / user-defined aggregate function.
Display hierarchy of employees under a given manager (using recursive view).
Using Explain Analyze to Optimize the query.
Materialized Views:
Creating Materialized View with no data, to display number of male and female patients:
A Material View (MatView) allows to store the result of a query physically and update the data periodically.
A MatView caches the result of a complex expensive query and then allows you to refresh this result periodically.
A MatView executes the query once and then holds onto those results for your viewing pleasure until you refresh the materialized view again in different ways.
This is opposed to a straight-up view, which then re-execute the query every time that you access the data in it. The data in a view is FRESH, but you're paying for the freshness in execution time, as interaction with server, connection to server, and running the query again happen. The main question in MatViews versus views is FRESHNESS of data versus PERFORMANCE time.
MatView with “WITH DATA” option → when we want to load data into MatViews at the creation time.
MatView with “WITH NO DATA” option → when we want to create an empty materialized view with no data.
MatView can also be used like a regular table, for example, you can add indexes or primary key on it, it supports VACUUM and ANALYZE commands (useful when you refresh it often).
MatView helps to easily refresh (CONCURRENTLY) / query without locking everyone else out of it. Also helps in reducing tedious tasks like updating a table without locking.
Quick tip: You can mess up your source table, your end-user won’t notice it before the refresh, as they access the MatView and not the actual table!
MatViews are generally used to cache results of a heavy query.
When we need to store data that has been manipulated from its basic normalized state, and that manipulation is expensive or slow, and you don’t mind that your data is stale. That manipulation can be a complicated join or union, filtering criteria, or protracted calculations etc.
WITH NO DATA → the view is flagged as unreadable. It means we cannot query data from view until we load data into it, using REFRESH MATERIALIZED VIEW mv_name. QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS gender_count_mview AS SELECT "Gender", COUNT("Patient_ID") FROM "Patients" JOIN "Gender" USING ("Gender_ID") GROUP BY "Gender" WITH NO DATA; SELECT * FROM gender_count_mview; – returns ERROR
EXPLANATION: — Refresh gender_count_mview to get data: REFRESH MATERIALIZED VIEW gender_count_mview; – -- runs materailized view again, first time we can use this. With this entire MatView is locked and we cannot query.
–OR below REFRESH … CONCURRENTLY helps data to be accessed while it’s updating - recommended every time –after first time, to keep data as many times and as accessible as possible. We can query on MatView, while it is updated. For this, MatView must have a UNIQUE index. – REFRESH MATERIALIZED VIEW CONCURRENTLY gender_count_mview; Note: Unless REFRESH command is executed, MatView data does not change) -- then run select command
SELECT * FROM gender_count_mview;
Checking if a MatView is populated or not SELECT relispopulated FROM pg_class WHERE relname = ‘gender_count_mview’;
Updatable Views with CASCADED CHECK OPTION:
A regular view does not store data physically and always gives updated data. An updatable view allows you to update data on underlying data. Rules are:
Query (inside view) must have one FROM entry which can be either a table or another updatable view
Query cannot contain DISTINCT, GROUP BY, WITH, LIMIT, OFFSET, UNION, INTERSECT, EXCEPT, HAVING at top level.
Selection list in query inside view cannot have any window function, any set-returning function, any aggregate function such as SUM, COUNT, AVG, MIN, MAX. We are only doing update on view and not selecting columns on an updatable view.
Use following operations to update the data: INSERT, UPDATE, DELETE along with a WHERE clause.
When you perform update operations, user must have corresponding privilege on the view, but don't need to have privilege on underlying table. Helps in securing database. QUERY: -- create a table patients_country CREATE TABLE patients_country (patient_id SERIAL, country_code varchar(4), city_name varchar(50)); INSERT INTO patients_country (country_code, city_name) VALUES ('US', 'Boston'),('US','Chicago'),('US','California'); -- create view on patients_country, where city_name like 'C%' CREATE OR REPLACE VIEW v_patients_c AS SELECT patient_id, country_code, city_name FROM patients_country WHERE city_name LIKE 'C%'; SELECT * FROM v_patients_c; -- create view where all cities LIKE 'C%' and country_code = 'US' CREATE OR REPLACE VIEW v_patients_c_us AS
SELECT patient_id, country_code, city_name FROM v_patients_c WHERE country_code = 'US' WITH CASCADED CHECK OPTION; SELECT * FROM v_patients_c_us;
-- invalid data => NOT inserted in v_patients_c_us view and NOT inserted in patient_country table.
INSERT INTO v_patients_c_us (country_code, city_name) VALUES ('US','Seattle');
-- valid data => entered into patient_country table, and shown in v_patients_c_us
INSERT INTO v_patients_c_us (country_code, city_name) VALUES ('US','Connecticut');
SELECT * FROM v_patients_c_us;
SELECT * FROM patients_country;
Create Partition on a table and insert rows to check data is rightly partitioned.
Partitioning a very large table on frequently accessed columns, helps improve the performance of query to a very very large extent.
-- create table with partition range type
-- (Partition by List, Partition by Hash are other Parition types)
CREATE TABLE patients_range(
id bigserial,
birth_date DATE NOT NULL,
country_code VARCHAR(2) NOT NULL
) PARTITION BY RANGE(birth_date);
SELECT * FROM patients_range;
-- create partition tables
CREATE TABLE patients_range_y2000 PARTITION OF patients_range
FOR VALUES FROM ('2000-01-01') TO ('2001-01-01');
CREATE TABLE patients_range_y2001 PARTITION OF patients_range
FOR VALUES FROM ('2001-01-01') TO ('2002-01-01');
-- insert data into main table
INSERT INTO patients_range(birth_date, country_code) VALUES
('2000-01-01','US'), ('2000-01-02','US'), ('2000-12-31','US'), ('2001-01-01','US');
SELECT * FROM patients_range; -- returns all 4 records
SELECT * FROM ONLY patients_range; -- returns no records
SELECT * FROM ONLY patients_range_y2000; -- returns 1 row
SELECT * FROM ONLY patients_range_y2001; -- returns 3 rows
-- Update operations
UPDATE patients_range SET birth_date = '2001-10-10' WHERE id = 1;
SELECT * FROM patients_range; -- will have 4 records with id1 data changed
SELECT * FROM ONLY patients_range_y2000; -- returns 2 rows
SELECT * FROM ONLY patients_range_y2001; -- returns 2 rows
--Explain analyze query tool
SELECT * FROM patients_range;
SELECT * FROM patients_range WHERE birth_date = '2000-01-02'; -- on partitioned column
-- does not scan all table, but scans just the partition patients_range_y2000,
-- as can be seen from explain analyze tool
---------------------------------------------------------------------------------------------------
Working with JSON data
-- JSON data format helps in moving data between APIs -- convert selected columns from one or more tables into json format SELECT row_to_json(t) FROM ( SELECT "Patient_ID", "Firstname", "Lastname", "Age", "BMI", "Diabetes_Duration" FROM "Patients" ) t;
-- aggregate data SELECT *, ( SELECT json_agg(x) as all_patients FROM ( SELECT "Firstname" FROM "Patients" WHERE "Group_ID" = "Group"."Group_ID" ) as x ) FROM "Group"; -- building an array using json_build_array SELECT json_build_array(1,2,3,'Hi',5); -- json_build_object -- even number of elements so it will have key value pairs SELECT json_build_object(1,2,3,'Hi',5, 'W0rld'); -- having keys and values separately SELECT json_object('{"name","email"}', '{"Tim", "a@bc.om"}'); -- creating a document from data CREATE TABLE patients_docs ( id SERIAL PRIMARY KEY, body JSONB); -- get all Link_Reference details for each patient in jsonb, a faster processing datatype -- and insert into patients_docs INSERT INTO patients_docs(body) SELECT row_to_json(a)::jsonb FROM ( SELECT "Patient_ID", "Firstname", "Lastname", "Age", "BMI", ( SELECT json_agg(x) AS all_link_references FROM ( SELECT "Lab_names"
FROM "Link_Reference" JOIN "Lab_Visit" USING ("Lab_visit_ID") WHERE "Link_Reference_ID" = "Patients"."Link_Reference_ID" ) x ) FROM "Patients" ) AS a; -- list table patients_docs SELECT * FROM patients_docs; -- find all firstname eqaul to 'Sue' using Existence operator ? SELECT * FROM patients_docs WHERE body->'Lastname' ? 'Sue'::TEXT -- using LIKE on jsonb object SELECT * FROM patients_docs WHERE body->>'Lastname' LIKE 'S%' -- get data of patients whose age is > 75 SELECT * FROM patients_docs WHERE (body->>'Age')::integer > 75; -- IN (1,2,3)
-- containment operator @> SELECT * FROM patient_docs WHERE body @> '{"Lastname":"Sue"}'; -- [{}] to search within array SELECT * FROM patients_docs WHERE body->'all_link_references' @> '[{"Lab_names":"The Brain Institute"}]';
Create a parametric cursor
Cursors help in row by row operations. Cursor helps to retrieve (select) or update or delete a single row at a time. Cursor is like a pointer that point or locate a specific table row. Cursor is a database query stored on the DBMS server - not a SELECT statement, but the RESULT SET retrieved by that statement. Cursors help to retrieve selected rows from a table, then we can check content of result sets and perform operations on content. SQL cannot perform a series of operations by itself. Procedural language helps in this. Examples of procedural languages: FORTRAN, C, etc In non-procedural languages (application or functional languages), the user has to specify “what to do” and “how to do”. It involves development of functions from other functions to construct more complex functions. Eg. SQL, LISP, etc Cursors can retrieve and then feed the contents/result sets to a procedural language for further processing. Cursor → result set → procedural language → operations (add/edit/delete) Sequence of steps to create cursor: DECLARE, OPEN (SELECT statement), FETCH, CLOSE(closing cursor is mandatory)
All cursors must be declared in a function. Cursor names must be unique in the current module or compilation unit. OPEN statement: For Unbound cursors, we give query, not for bound cursors. FETCH→ Direction: NEXT, LAST, PRIOR, FIRST, ABSOLUTE count, RELATIVE count; for SCROLL: FORWARD, BACKWARD MOVE: with direction FROM or IN, (relative -1), cursor_variable UPDATE, DELETE: WHERE CURRENT OF CLOSE cursor_name → releases resources; can reopen cursor after closing. QUERY: CREATE OR REPLACE FUNCTION fn_get_patient_names_by_visit_year (custom_year integer) RETURNS TEXT LANGUAGE PLPGSQL AS $$ DECLARE patient_names TEXT DEFAULT ''; rec_patient record; cur_all_patients_by_visit_year CURSOR(custom_year integer) FOR SELECT CONCAT_WS(' ', "Firstname", "Lastname") AS patient_name, EXTRACT('YEAR' FROM "Visit_Date") AS visit_year FROM "Patients" WHERE EXTRACT('YEAR' FROM "Visit_Date") = custom_year; BEGIN OPEN cur_all_patients_by_visit_year(custom_year); LOOP FETCH cur_all_patients_by_visit_year INTO rec_patient;
EXIT WHEN NOT FOUND; IF rec_patient.patient_name ILIKE '%Sue%' THEN patient_names := patient_names || ',' || rec_patient.patient_name || ',' || rec_patient.visit_year; END IF; END LOOP; CLOSE cur_all_patients_by_visit_year; RETURN patient_names; END; $$; SELECT fn_get_patient_names_by_visit_year(2006);
Listing psql utilities along with returning the query every N seconds.
PostgreSQL psql Terminal utilities -- a. psql -h 192.168.21.2 -d diabetes -U postgres -- -h host -d for database, -U for username -- b. \dt -- list tables after connecting to database -- c. \l -- at command prompt, not in database, to list all databases -- d. \db -- list all table space -- e. \dn -- list available schemas -- f. \di -- list all indexes in the database -- schema, index name, type, owner, table fields -- g. \ds -- list all sequences -- name of sequence: tablename_columnname_seq -- h. \dg -- list all roles -- i. \dT -- list all datatypes -- j. \dD -- list all domain data types -- k. \dv -- list all views -- l. \d tablename; -- describe table -- m. \d tablename+; -- additional information about table -- n. \e -- launch editor, insert --> esc+i, enter command, save --> esc , :wq -- o. SELECT version(); -- current version of PostgreSQL -- p. \g -- type previous commands -- q. \s -- command history -- r. \i filename -- run commands from a file - after connected to db (at commandprompt: head psql.txt) -- for housekeeping -- s. \h -- full help on all SQL commands -- t. \h create table -- to see syntax to say "create table" -- u. \pset null (null) -- null values displayed as null to distinguish from '' values -- \pset null (same as above) -- \pset null '' -- treats null and '' same in display -- v. \pset linestyle unicode -- display select tables in different format
--\pset border 2 --\pset linestyle ascii -- back to normal style --\pset border -- back to normal border -- w. Repeatedly execute a query. SELECT * FROM "Patients" LIMIT 5; \watch N -- runs previous command again and again every N seconds -- x. \timing -- turn on/off query execution time -- y. \c dbname username -- switch between databases -- z. \q -- quit database
Create user defined - domain datatype for address, postal code, email, enum.
-- CREATE DOMAIN statement can have a range, optional DEFAULT, NOT NULL and CHECK constraints -- They have to be unique within schema scope. -- helps to standardize database datatypes in one place. -- can resuse in multiple columns. share across tables. -- NULL is default -- composite type: only single value return -- create address domain
-- CREATE DOMAIN addr VARCHAR(250) NOT NULL; -- create composite TYPE datatype CREATE TYPE addr AS (city VARCHAR(50), country VARCHAR(30)); -- create us_postal_code domain CREATE DOMAIN us_postal_code AS TEXT CHECK ( VALUE ~'^\d{5}$' OR VALUE ~'^D{5}-\d{4}$'); -- DROP DOMAIN addr CASCADE; -- deletes columns in referred tables with this datatype -- create email domain datatype CREATE DOMAIN proper_email VARCHAR(200) CHECK (VALUE ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$'); -- create enum DOMAIN datatype CREATE DOMAIN valid_group VARCHAR(10) CHECK(VALUE IN ('DM', 'CONTROL')); -- list domain datatypes SELECT typname FROM pg_catalog.pg_type JOIN pg_catalog.pg_namespace ON pg_namespace.oid = pg_type.typnamespace WHERE typtype = 'd' and nspname = 'public'; -- d for domain datatypes, nspname for schema name
-- usage of DOMAIN / user-defined datatypes CREATE TABLE patient_detail_domain_types( address addr, postal_code us_postal_code, email_address proper_email, group_name valid_group ); INSERT INTO patient_detail_domain_types(address, postal_code, email_address, group_name) VALUES (ROW('London','UK'), '02481', 'email@email.com', 'DM'); SELECT * FROM patient_detail_domain_types;
Create a custom index method.
-- SSN : 111-22-nnnn, custom index on last 4 digits of SSN -- Steps: create a new class operator, use it with (eg)B-tree index -- create sample SSN table CREATE TABLE ssn (ssn TEXT); -- insert data INSERT INTO ssn (ssn) VALUES ('111-11-0110'),('222-22-0220'), ('333-33-0330'),('444-44-0440'); -- function to change order like 011011111, remove non-numerics CREATE OR REPLACE FUNCTION fn_fix_ssn(text) RETURNS text AS $$ BEGIN RETURN substring($1,8) || replace(substring($1,1,7),'-',''); END; $$ LANGUAGE plpgsql IMMUTABLE; -- usage SELECT fn_fix_ssn(ssn) FROM ssn; -- ssn compare function CREATE OR REPLACE FUNCTION fn_ssn_compare(text, text) RETURNS integer AS $$
BEGIN IF fn_fix_ssn($1) < fn_fix_ssn($2) THEN RETURN -1; ELSIF fn_fix_ssn($1) > fn_fix_ssn($2) THEN RETURN 1; ELSE RETURN 0; END IF; END; $$ LANGUAGE plpgsql IMMUTABLE; -- create custom operator class CREATE OPERATOR CLASS op_class_ssn_ops1 FOR TYPE text USING btree AS OPERATOR 1 <, OPERATOR 2 <=, OPERATOR 3 =, OPERATOR 4 >=, OPERATOR 5 >, FUNCTION 1 fn_ssn_compare(text, text); -- create custom index using btree CREATE INDEX idx_ssn ON ssn(ssn op_class_ssn_ops1); -- lets see custom index operator set in pgadmin -- set seqscan to off SHOW enable_seqscan;
SET enable_seqscan = 'off'; -- query on index EXPLAIN SELECT * FROM ssn WHERE ssn = '333-33-0330'; -- '033033333'; -- housekeeping SET enable_seqscan = 'on';
Using GRANT / REVOKE for Security using roles and users.
-- We define roles with security permissions to eiter groups or individual users. -- Levels --> highest - lowest: Instance level --> database level --> schema level --> table level --> column level, row level (more complex) -- create user and role CREATE ROLE tester NOSUPERUSER NOCREATEDB NOCREATEROLE LOGIN PASSWORD 'password123'; CREATE USER linda NOSUPERUSER NOCREATEDB NOCREATEROLE LOGIN PASSWORD 'password123'; -- users and roles are similar in PostgreSQL -- revoke access of login from tester REVOKE ALL ON DATABASE hr FROM public; -- database level REVOKE ALL ON SCHEMA public FROM public; -- schema level -- add users to roles GRANT tester TO linda; -- instance level GRANT CONNECT ON DATABASE diabetes TO linda; -- database level GRANT CREATE ON SCHEMA public TO tester; -- schema level GRANT SELECT ON ALL TABLES IN SCHEMA public TO tester; -- table level GRANT INSERT ON TABLE "Patients" TO tester; -- table level GRANT SELECT("Patient_ID","Firstname") ON "Patients" TO tester; -- column level -- Row Level Security (RLS) must be enabled at table level ALTER TABLE "Patients" ENABLE ROW LEVEL SECURITY; -- Row level (DISABLE after DROP POLICY) -- create a row level policy CREATE POLICY p_patients_firstname_c ON "Patients" AS RESTRICTIVE -- OR SELECT AND FOR SELECT TO tester
USING ("Firstname" LIKE 'C%'); -- USING (username=current_user); -- drop policy DROP POLICY IF EXISTS p_patients_firstname_c ON "Patients"; SELECT current_user; -- create a policy that uses session variable (application level) CREATE POLICY rls_t_patients_by_username_session ON "Patients" FOR ALL TO public USING (username=current_setting('rls.username'); -- setting policy at runtime SET rls.username = 'tester'; -- psql: inspect permissions \z "Patients"
Write a custom / user-defined aggregate function.
-- scenario: calculate total price of taxi ride, -- INITCOST - $5, per every mile: $2.50 to price -- calculate price per ride. CREATE TABLE t_taxi(id_trip int, miles numeric); INSERT INTO t_taxi(id_trip, miles) VALUES (1,3.5),(1,4.2),(1,1.2),(1,3.5), -- 1st trip (2,1.8),(2,3.2),(2,4.4); -- 2nd trip SELECT * FROM t_taxi; -- create functoin CREATE OR REPLACE FUNCTION taxi_all_rows(numeric, numeric, numeric) RETURNS numeric AS $$ SELECT $1 + $2 *$3; $$ LANGUAGE 'sql' STRICT; -- create final func for each group, 2 groups here => 2 rows CREATE OR REPLACE FUNCTION taxi_trip_sum(numeric)
RETURNS numeric AS $$ SELECT ROUND($1,2); $$ LANGUAGE 'sql' STRICT; -- create aggregate function CREATE AGGREGATE agg_taxi (numeric, numeric) --(#miles per trip, price per km) ( INITCOND = 5.0, STYPE = numeric,-- datatype of result SFUNC = taxi_all_rows, -- name of state transition function to be called for each input row FINALFUNC = taxi_trip_sum -- to aggregate results of group after rows have been traversed ); -- query to get results using custom aggregate function SELECT id_trip, agg_taxi(miles, 2.5), 5+SUM(miles)*2.5 AS manual FROM t_taxi GROUP BY id_trip;
Create a trigger to raise notice and prevent the deletion of a record from a view
-- create table and insert data into "test_delete_cancel" table and see that it is not deleted. CREATE TABLE "test_delete_cancel" (id INT, test VARCHAR(30)); INSERT INTO "test_delete_cancel" VALUES(1,'Indian'); INSERT INTO "test_delete_cancel" VALUES(2,'Female'); SELECT * FROM "test_delete_cancel"; -- create view on test_delete_cancel table CREATE VIEW test_view AS SELECT * FROM "test_delete_cancel"; SELECT * FROM test_view; -- cancel function to use in trigger CREATE OR REPLACE FUNCTION fn_generic_cancel_op() RETURNS TRIGGER LANGUAGE PLPGSQL AS $$ BEGIN IF TG_WHEN = 'AFTER' THEN
RAISE EXCEPTION 'YOU ARE NOT ALLOWED TO % ROWS IN %.%', TG_OP, TG_TABLE_SCHEMA, TG_TABLE_NAME; END IF; -- FOR BEFORE, INSTEAD OF , NOT AFTER TRIGGERS RAISE NOTICE '% ON ROWS IN %.% WONT HAPPEN', TG_OP, TG_TABLE_SCHEMA, TG_TABLE_NAME; RETURN NULL; -- CANCELS OPERATION OF TRIGGER END $$ -- create trigger to not allow deleting on test_view with INSTEAD OF trigger on view for DML operations CREATE TRIGGER trg_disallow_delete INSTEAD OF DELETE ON test_view FOR EACH ROW EXECUTE PROCEDURE fn_generic_cancel_op(); -- try to delete the row in view DELETE FROM test_view WHERE id = 1; – we can see it throws exception without deleting the row SELECT * FROM test_view; -- we can see data is not deleted in view.
Display hierarchy of employees under a given manager. (using recursive view)
-- create a employees table CREATE TABLE employees ( employee_id serial PRIMARY KEY, full_name VARCHAR NOT NULL, manager_id INT );
-- insert into employees table INSERT INTO employees ( employee_id, full_name, manager_id ) VALUES (1, 'Michael North', NULL), (2, 'Megan Berry', 1), (3, 'Sarah Berry', 1), (4, 'Zoe Black', 1), (5, 'Tim James', 1), (6, 'Bella Tucker', 2), (7, 'Ryan Metcalfe', 2), (8, 'Max Mills', 2), (9, 'Benjamin Glover', 2), (10, 'Carolyn Henderson', 3), (11, 'Nicola Kelly', 3), (12, 'Alexandra Climo', 3), (13, 'Dominic King', 3),
(14, 'Leonard Gray', 4), (15, 'Eric Rampling', 4), (16, 'Piers Paige', 7), (17, 'Ryan Henderson', 7), (18, 'Frank Tucker', 8), (19, 'Nathan Ferguson', 8), (20, 'Kevin Rampling', 8); -- create recursive view CREATE RECURSIVE VIEW reporting_line (employee_id, subordinates) AS SELECT employee_id, full_name AS subordinates FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.employee_id, ( rl.subordinates || ' > ' || e.full_name ) AS subordinates FROM employees e INNER JOIN reporting_line rl ON e.manager_id = rl.employee_id; -- execute select query on recursive view SELECT subordinates FROM reporting_line
WHERE employee_id = 20;
Using Explain Analyze to Optimize the query.
EXPLAIN ANALYZE
SELECT EXTRACT(YEAR FROM "Lab_Visit_Date"), COUNT(*) AS total_visits
FROM "Lab_Visit"
JOIN "Link_Reference" USING ("Lab_visit_ID")
JOIN "Patients" USING ("Link_Reference_ID")
WHERE "Lab_names" ='Cultivate Lab'
GROUP BY EXTRACT(YEAR FROM "Lab_Visit_Date")
HAVING COUNT(*) > 1 ORDER BY total_visits DESC
From above, we can see, SELECT clause is executed as Seq Scan on lines 10, 15, 18. We read the plan from bottom to top. First Lab_names = ‘Cultivate Lab’ is executed. When the query is changed to optimize using alias name in GROUP BY clause, we can see execution time difference. In GROUP BY clause, again the EXTRACT function is not called, thus saving time. SELECT EXTRACT(YEAR FROM "Lab_Visit_Date") AS visit_year, COUNT(*) AS total_visits FROM "Lab_Visit" JOIN "Link_Reference" USING ("Lab_visit_ID") JOIN "Patients" USING ("Link_Reference_ID") WHERE "Lab_names" ='Cultivate Lab' GROUP BY visit_year
HAVING COUNT(*) > 1 ORDER BY total_visits DESC;
From above, we can see saving times for different stages of execution.
From below, in statistics, we can see improvement in “Time spent” and “% of query” column values.
Also, on larger data sets, modifying HAVING COUNT(*) > 100 vs HAVING COUNT(*)> 10, we can see when >100, filter executed first thus reducing rows. For >10, sort is executed first.
We can see the difference in cost associated with each step will reduce, on optimizing queries to not fetch many rows, but limiting the count of rows to fetch.
Hope you enjoyed learning various concepts provided by PostgreSQL.
Happy Learning!!!