pgFouine: PostgreSQL log analysis report

Normalized reports are marked with a "(N)".

Generated on 2013-11-12 09:27
Parsed /var/log/postgresql/postgresql-9.1-main.log.bak (1,434 lines) in 1s
Log from 2013-11-12 09:25:12 to 2013-11-12 09:25:47

Overall statistics ^

Number of unique normalized queries: 92
Number of queries: 361 (identified: 360)
Total query duration: 1.9s (identified: 1.9s)
First query: 2013-11-12 09:25:12
Last query: 2013-11-12 09:25:47
Query peak: 20 queries/s at 2013-11-12 09:25:39

Queries by type ^

Type	Count	Percentage
SELECT	152	42.2
INSERT	2	0.6

Queries that took up the most time (N) ^

Rank

Total duration

Times executed

Av. duration (s)

Query

0.5s

0.51

SELECT substring(image FROM 1 FOR 26780) FROM ref.tag_image WHERE pk = 5;

0.2s

0.24

SELECT DISTINCT ON (pk_identity) * FROM ( SELECT * FROM (( SELECT vbp.*, 'last name'::text AS match_type FROM dem.v_basic_person vbp, dem.names n WHERE vbp.pk_identity = n.id_identity AND lower(n.lastnames) ~* lower('^kirk') ) UNION ALL ( SELECT vbp.*, 'first name'::text AS match_type FROM dem.v_basic_person vbp, dem.names n WHERE vbp.pk_identity = n.id_identity AND lower(n.firstnames) ~* lower('^kirk') ) UNION ALL ( SELECT vbp.*, 'any name part'::text AS match_type FROM dem.v_basic_person vbp, dem.names n WHERE vbp.pk_identity = n.id_identity AND lower(n.firstnames || ' ' || n.lastnames || ' ' || coalesce(n.preferred, '')) ~* lower('kirk') )) AS super_list ORDER BY lastnames, firstnames, dob ) AS sorted_list;

0.2s

0.01

SELECT * FROM clin.v_pat_encounters c_vpe WHERE c_vpe.pk_patient = 0 ORDER BY started;

0.2s

0.17

SELECT pk_vaccination, l10n_indication, indication_count FROM clin.v_pat_last_vacc4indication WHERE pk_patient = 12;

0.1s

0.09

SELECT *, _(role) AS l10n_role FROM dem.v_staff WHERE TRUE ORDER BY can_login DESC, is_active DESC, short_alias ASC;

0.1s

0.08

SELECT * FROM dem.v_pat_addresses WHERE pk_identity = 12;

0.1s

0.07

SELECT * FROM clin.get_hints_for_patient(12);

0.1s

0.00

SELECT * FROM clin.v_pat_episodes WHERE pk_episode=0;

0.1s

0.00

SELECT DISTINCT fk_encounter FROM clin.clin_root_item WHERE fk_episode IN (...);

0.0s

0.02

SELECT * FROM dem.v_message_inbox WHERE pk_patient = 0 AND is_overdue IS TRUE ORDER BY due_date, importance DESC, received_when DESC;

0.0s

0.03

SELECT clin.remove_old_empty_encounters(12::integer, '1 week'::interval);

0.0s

135

0.00

ROLLBACK;

0.0s

0.02

SELECT *, COALESCE ( (SELECT array_agg(seq_idx) FROM blobs.doc_obj b_do WHERE b_do.fk_doc = b_vdm.pk_doc), ARRAY[]::integer[] ) AS seq_idx_list FROM blobs.v_doc_med b_vdm WHERE pk_patient = 12 AND pk_doc IN (SELECT b_vo.pk_doc FROM blobs.v_obj4doc_no_data b_vo WHERE b_vo.pk_patient = 12 AND b_vo.reviewed IS TRUE) ORDER BY clin_when DESC;

0.0s

0.00

SELECT oid, typname FROM pg_type;

0.0s

0.00

SELECT *, False AS is_potential_problem FROM clin.v_problem_list WHERE pk_episode = 0 AND pk_patient = 0 AND pk_health_issue IS NULL;

0.0s

0.02

SELECT * FROM dem.v_message_inbox WHERE pk_patient = 12 ORDER BY due_date NULLS LAST, importance DESC, received_when DESC;

0.0s

0.01

SELECT * FROM clin.v_pat_episodes WHERE pk_episode IN ( SELECT DISTINCT fk_episode FROM clin.clin_root_item WHERE fk_encounter = 0 UNION SELECT DISTINCT fk_episode FROM blobs.doc_med WHERE fk_encounter = 0 );

0.0s

0.01

SELECT * FROM clin.v_pat_encounters WHERE pk_patient = 0 ORDER BY started DESC LIMIT 0;

0.0s

0.01

INSERT INTO clin.allergy_state ( fk_encounter, has_allergy ) SELECT 0, NULL WHERE NOT EXISTS ( SELECT 0 FROM clin.v_pat_allergy_state WHERE pk_patient = ( SELECT fk_patient FROM clin.encounter WHERE pk = 0 ) );

0.0s

0.01

SELECT *, COALESCE ( (SELECT array_agg(seq_idx) FROM blobs.doc_obj b_do WHERE b_do.fk_doc = b_vdm.pk_doc), ARRAY[]::integer[] ) AS seq_idx_list FROM blobs.v_doc_med b_vdm WHERE pk_doc IN ( SELECT DISTINCT ON (b_vo.pk_doc) b_vo.pk_doc FROM blobs.v_obj4doc_no_data b_vo WHERE pk_patient = 12 AND reviewed IS FALSE ) ORDER BY clin_when DESC;

Slowest queries ^

Rank	Duration (s)	Query
1	0.51	SELECT substring(image FROM 1 FOR 26780) FROM ref.tag_image WHERE pk = 5;
2	0.24	SELECT DISTINCT ON (pk_identity) * FROM ( SELECT * FROM (( SELECT vbp., 'last name'::text AS match_type FROM dem.v_basic_person vbp, dem.names n WHERE vbp.pk_identity = n.id_identity AND lower(n.lastnames) ~ lower('^kirk') ) UNION ALL ( SELECT vbp., 'first name'::text AS match_type FROM dem.v_basic_person vbp, dem.names n WHERE vbp.pk_identity = n.id_identity AND lower(n.firstnames) ~ lower('^kirk') ) UNION ALL ( SELECT vbp., 'any name part'::text AS match_type FROM dem.v_basic_person vbp, dem.names n WHERE vbp.pk_identity = n.id_identity AND lower(n.firstnames \|\| ' ' \|\| n.lastnames \|\| ' ' \|\| coalesce(n.preferred, '')) ~ lower('kirk') )) AS super_list ORDER BY lastnames, firstnames, dob ) AS sorted_list;
3	0.17	SELECT pk_vaccination, l10n_indication, indication_count FROM clin.v_pat_last_vacc4indication WHERE pk_patient = 12;
4	0.09	SELECT *, _(role) AS l10n_role FROM dem.v_staff WHERE TRUE ORDER BY can_login DESC, is_active DESC, short_alias ASC;
5	0.08	SELECT * FROM dem.v_pat_addresses WHERE pk_identity = 12;
6	0.07	SELECT * FROM clin.get_hints_for_patient(12);
7	0.03	SELECT clin.remove_old_empty_encounters(12::integer, '1 week'::interval);
8	0.02	SELECT *, COALESCE ( (SELECT array_agg(seq_idx) FROM blobs.doc_obj b_do WHERE b_do.fk_doc = b_vdm.pk_doc), ARRAY[]::integer[] ) AS seq_idx_list FROM blobs.v_doc_med b_vdm WHERE pk_patient = 12 AND pk_doc IN (SELECT b_vo.pk_doc FROM blobs.v_obj4doc_no_data b_vo WHERE b_vo.pk_patient = 12 AND b_vo.reviewed IS TRUE) ORDER BY clin_when DESC;
9	0.02	SELECT * FROM dem.v_message_inbox WHERE pk_patient = 12 AND is_overdue IS TRUE ORDER BY due_date, importance DESC, received_when DESC;
10	0.02	SELECT * FROM dem.v_message_inbox WHERE pk_patient = 12 ORDER BY due_date NULLS LAST, importance DESC, received_when DESC;
11	0.02	SELECT * FROM dem.v_message_inbox WHERE pk_patient = 12 AND is_overdue IS TRUE ORDER BY due_date, importance DESC, received_when DESC;
12	0.01	SELECT * FROM clin.v_pat_episodes WHERE pk_episode IN ( SELECT DISTINCT fk_episode FROM clin.clin_root_item WHERE fk_encounter = 1819 UNION SELECT DISTINCT fk_episode FROM blobs.doc_med WHERE fk_encounter = 1819 );
13	0.01	SELECT *, COALESCE ( (SELECT array_agg(seq_idx) FROM blobs.doc_obj b_do WHERE b_do.fk_doc = b_vdm.pk_doc), ARRAY[]::integer[] ) AS seq_idx_list FROM blobs.v_doc_med b_vdm WHERE pk_doc IN ( SELECT DISTINCT ON (b_vo.pk_doc) b_vo.pk_doc FROM blobs.v_obj4doc_no_data b_vo WHERE pk_patient = 12 AND reviewed IS FALSE ) ORDER BY clin_when DESC;
14	0.01	( SELECT pk_episode AS DATA, description AS field_label, coalesce ( description \|\| ' - ' \|\| health_issue, description ) AS list_label, 1 AS rank FROM clin.v_pat_episodes WHERE episode_open IS true AND description ILIKE '%other documents%' AND pk_patient = 12 ) UNION ALL ( SELECT pk_episode AS DATA, description AS field_label, coalesce ( description \|\| _(' (closed)') \|\| ' - ' \|\| health_issue, description \|\| _(' (closed)') ) AS list_label, 2 AS rank FROM clin.v_pat_episodes WHERE description ILIKE '%other documents%' AND episode_open IS false AND pk_patient = 12 ) ORDER BY rank, list_label LIMIT 30;
15	0.01	SELECT * FROM clin.v_pat_substance_intake WHERE pk_patient = 12 AND is_currently_active IN (true, NULL) ORDER BY substance;
16	0.01	SELECT * FROM clin.v_pat_encounters c_vpe WHERE c_vpe.pk_patient = 12 ORDER BY started;
17	0.01	SELECT * FROM clin.v_pat_encounters c_vpe WHERE c_vpe.pk_patient = 12 ORDER BY started;
18	0.01	SELECT * FROM clin.v_pat_encounters c_vpe WHERE c_vpe.pk_patient = 12 ORDER BY started;
19	0.01	SELECT * FROM clin.v_pat_encounters c_vpe WHERE c_vpe.pk_patient = 12 ORDER BY started;
20	0.01	SELECT * FROM clin.v_pat_encounters c_vpe WHERE c_vpe.pk_patient = 12 ORDER BY started;

Most frequent queries (N) ^

Rank

Times executed

Total duration

Av. duration (s)

Query

135

0.0s

0.00

ROLLBACK;

0.2s

0.01

SELECT * FROM clin.v_pat_encounters c_vpe WHERE c_vpe.pk_patient = 0 ORDER BY started;

0.0s

0.00

COMMIT;

0.1s

0.00

SELECT DISTINCT fk_encounter FROM clin.clin_root_item WHERE fk_episode IN (...);

0.1s

0.00

SELECT * FROM clin.v_pat_episodes WHERE pk_episode=0;

0.0s

0.00

SELECT oid, typname FROM pg_type;

0.0s

0.00

SELECT *, False AS is_potential_problem FROM clin.v_problem_list WHERE pk_episode = 0 AND pk_patient = 0 AND pk_health_issue IS NULL;

0.0s

0.00

SET bytea_output TO '';

0.0s

0.00

SELECT type FROM cfg.cfg_template WHERE name='';

0.0s

0.00

SELECT *, False AS is_potential_problem FROM clin.v_problem_list WHERE pk_episode IS NULL AND pk_patient = 0 AND pk_health_issue = 0;

0.0s

0.00

SELECT *, xmin_health_issue FROM clin.v_health_issues WHERE pk_health_issue=0;

0.0s

0.00

SELECT vco.value FROM cfg.v_cfg_opts_string vco WHERE vco.owner = CURRENT_USER AND vco.workplace = '' AND vco.OPTION = '' AND vco.cookie IS NULL LIMIT 0;

0.0s

0.00

SET timezone TO '';

0.0s

0.00

SELECT DISTINCT pk FROM clin.episode WHERE fk_health_issue IN (...);

0.0s

0.00

SHOW default_transaction_isolation;

0.0s

0.00

SELECT CURRENT_USER;

0.0s

0.00

SET session characteristics AS transaction READ WRITE;

0.0s

0.00

SET default_transaction_isolation TO '';

0.0s

0.00

SET default_transaction_read_only TO off;

0.0s

0.02

SELECT * FROM dem.v_message_inbox WHERE pk_patient = 0 AND is_overdue IS TRUE ORDER BY due_date, importance DESC, received_when DESC;

Slowest queries (N) ^

Rank

Av. duration (s)

Times executed

Total duration

Query

0.51

0.5s

SELECT substring(image FROM 1 FOR 26780) FROM ref.tag_image WHERE pk = 5;

0.24

0.2s

0.17

0.2s

SELECT pk_vaccination, l10n_indication, indication_count FROM clin.v_pat_last_vacc4indication WHERE pk_patient = 12;

0.09

0.1s

SELECT *, _(role) AS l10n_role FROM dem.v_staff WHERE TRUE ORDER BY can_login DESC, is_active DESC, short_alias ASC;

0.08

0.1s

SELECT * FROM dem.v_pat_addresses WHERE pk_identity = 12;

0.07

0.1s

SELECT * FROM clin.get_hints_for_patient(12);

0.03

0.0s

SELECT clin.remove_old_empty_encounters(12::integer, '1 week'::interval);

0.02

0.0s

0.02

0.0s

SELECT * FROM dem.v_message_inbox WHERE pk_patient = 0 AND is_overdue IS TRUE ORDER BY due_date, importance DESC, received_when DESC;

0.02

0.0s

SELECT * FROM dem.v_message_inbox WHERE pk_patient = 12 ORDER BY due_date NULLS LAST, importance DESC, received_when DESC;

0.01

0.0s

SELECT *, COALESCE ( (SELECT array_agg(seq_idx) FROM blobs.doc_obj b_do WHERE b_do.fk_doc = b_vdm.pk_doc), ARRAY[]::integer[] ) AS seq_idx_list FROM blobs.v_doc_med b_vdm WHERE pk_doc IN ( SELECT DISTINCT ON (b_vo.pk_doc) b_vo.pk_doc FROM blobs.v_obj4doc_no_data b_vo WHERE pk_patient = 12 AND reviewed IS FALSE ) ORDER BY clin_when DESC;

0.01

0.0s

( SELECT pk_episode AS DATA, description AS field_label, coalesce ( description || ' - ' || health_issue, description ) AS list_label, 1 AS rank FROM clin.v_pat_episodes WHERE episode_open IS true AND description ILIKE '%other documents%' AND pk_patient = 12 ) UNION ALL ( SELECT pk_episode AS DATA, description AS field_label, coalesce ( description || _(' (closed)') || ' - ' || health_issue, description || _(' (closed)') ) AS list_label, 2 AS rank FROM clin.v_pat_episodes WHERE description ILIKE '%other documents%' AND episode_open IS false AND pk_patient = 12 ) ORDER BY rank, list_label LIMIT 30;

0.01

0.0s

SELECT * FROM clin.v_pat_substance_intake WHERE pk_patient = 12 AND is_currently_active IN (true, NULL) ORDER BY substance;

0.01

0.0s

0.01

0.2s

SELECT * FROM clin.v_pat_encounters c_vpe WHERE c_vpe.pk_patient = 0 ORDER BY started;

0.01

0.0s

SELECT l10n_type, count(1) AS frequency FROM clin.v_pat_encounters WHERE pk_patient = 12 GROUP BY l10n_type ORDER BY frequency DESC;

0.01

0.0s

SELECT * FROM clin.v_pat_encounters WHERE pk_patient = 0 ORDER BY started DESC LIMIT 0;

0.01

0.0s

SELECT * FROM clin.v_test_results WHERE pk_patient = 12 ORDER BY clin_when DESC LIMIT 1;

0.01

0.0s

SELECT * FROM blobs.v_obj4doc_no_data WHERE pk_obj = 1;

0.01

0.0s

SELECT * FROM clin.v_test_results WHERE pk_patient = 12 AND reviewed IS FALSE ORDER BY (trim(coalesce(abnormality_indicator), '') <> '') DESC NULLS LAST, unified_abbrev;