|
37 min
59%
|
117,772 ms
|
19
postgres
|
SELECT * FROM public.business_reconciliation
ORDER BY business_seq ASC
Covered by index on (business_seq)
Rows: 536247
Row progression: 536247, 1
Row estimates
- business_seq (sort): 1
Existing indexes
- business_seq PRIMARY
- agb_doc_id UNIQUE
- agb_id bpchar_pattern_ops NULLS FIRST) WITH (deduplicate_items='true'
- audit_has_changes
- mailable
- mailable_reason
- validation_state
|
|
8 min
13%
|
499,707 ms
|
1
postgres
|
COPY public.id_lookup ( public_id, internal_id ) FROM STDIN WITH(FORMAT csv, DELIMITER ',', HEADER, ENCODING 'UTF8', QUOTE '"', ESCAPE '"')
|
|
6 min
10%
|
75,152 ms
|
5
postgres
|
SELECT * FROM public.business_reconciliation
ORDER BY cyclops_business_id ASC NULLS FIRST
Details
CREATE INDEX CONCURRENTLY ON business_reconciliation (cyclops_business_id)
Rows: 536247
Row progression: 536247, 1
Row estimates
- cyclops_business_id (sort): 1
Existing indexes
- business_seq PRIMARY
- agb_doc_id UNIQUE
- agb_id bpchar_pattern_ops NULLS FIRST) WITH (deduplicate_items='true'
- audit_has_changes
- mailable
- mailable_reason
- validation_state
|
|
4 min
6%
|
0 ms
|
2,020,864
postgres
|
INSERT INTO business_reconciliation (agb_doc_id, agb_id)
VALUES ($1, $2)
ON CONFLICT (agb_doc_id) DO UPDATE
SET agb_id = COALESCE(EXCLUDED.agb_id, business_reconciliation.agb_id),
last_seen_ts = now()
|
|
3 min
5%
|
60,738 ms
|
3
postgres
|
SELECT * FROM public.business_reconciliation
WHERE matched_existing_cyclops is false
|
|
3 min
4%
|
0 ms
|
1,463,462
postgres
|
UPDATE business_reconciliation
SET agb_id = $1, normalized_website = $2, website_hostname = $3, website_upper_domain = $4, website_ok = $5, website_issues = $6, agb_address_full = $7, agb_city = $8, agb_state = $9, agb_zip = $10, norm_street_line = $11, norm_state = $12, norm_zip5 = $13, norm_address_key = $14, cyclops_business_id = $15, matched_existing_cyclops = $16, mailable = $17, mailable_reason = $18, mailable_last_mailed_on = $19, mailable_days_since = $20, mailable_threshold_days = $21, validation_state = $22, audit_has_changes = $23, audit_change_count = $24, audit_diff_fields = $25, audit_diff_before = $26, audit_diff_after = $27, audit_last_compared_at = $28, last_seen_ts = now()
WHERE agb_doc_id = $29
RETURNING business_seq, agb_doc_id
Covered by index on (agb_doc_id)
Rows: 536247
Row progression: 536247, 1
Row estimates
- agb_doc_id (=): 1
Existing indexes
- business_seq PRIMARY
- agb_doc_id UNIQUE
- agb_id bpchar_pattern_ops NULLS FIRST) WITH (deduplicate_items='true'
- audit_has_changes
- mailable
- mailable_reason
- validation_state
|
|
1 min
0.8%
|
0 ms
|
190,306
postgres
|
UPDATE business_reconciliation
SET normalized_website = $1, website_hostname = $2, website_upper_domain = $3, website_ok = $4, website_issues = $5, agb_address_full = $6, agb_city = $7, agb_state = $8, agb_zip = $9, matched_existing_cyclops = $10, mailable_reason = $11, mailable_threshold_days = $12, validation_state = $13, last_seen_ts = now()
WHERE agb_doc_id = $14
RETURNING business_seq, agb_doc_id
Covered by index on (agb_doc_id)
Rows: 536247
Row progression: 536247, 1
Row estimates
- agb_doc_id (=): 1
Existing indexes
- business_seq PRIMARY
- agb_doc_id UNIQUE
- agb_id bpchar_pattern_ops NULLS FIRST) WITH (deduplicate_items='true'
- audit_has_changes
- mailable
- mailable_reason
- validation_state
|
|
0 min
0.6%
|
0 ms
|
160,110
postgres
|
UPDATE business_reconciliation
SET normalized_website = $1, website_hostname = $2, website_upper_domain = $3, website_ok = $4, website_issues = $5, agb_address_full = $6, agb_city = $7, agb_state = $8, agb_zip = $9, norm_street_line = $10, norm_state = $11, norm_zip5 = $12, norm_address_key = $13, cyclops_business_id = $14, matched_existing_cyclops = $15, mailable = $16, mailable_reason = $17, mailable_last_mailed_on = $18, mailable_days_since = $19, mailable_threshold_days = $20, validation_state = $21, audit_has_changes = $22, audit_change_count = $23, audit_diff_fields = $24, audit_diff_before = $25, audit_diff_after = $26, audit_last_compared_at = $27, last_seen_ts = now()
WHERE agb_doc_id = $28
RETURNING business_seq, agb_doc_id
Covered by index on (agb_doc_id)
Rows: 536247
Row progression: 536247, 1
Row estimates
- agb_doc_id (=): 1
Existing indexes
- business_seq PRIMARY
- agb_doc_id UNIQUE
- agb_id bpchar_pattern_ops NULLS FIRST) WITH (deduplicate_items='true'
- audit_has_changes
- mailable
- mailable_reason
- validation_state
|
|
0 min
0.6%
|
0 ms
|
2,414,700
postgres
|
SELECT $1
|
|
0 min
0.2%
|
0 ms
|
105,939
postgres
|
UPDATE business_reconciliation
SET normalized_website = $1, website_hostname = $2, website_upper_domain = $3, website_ok = $4, website_issues = $5, norm_street_line = $6, norm_state = $7, norm_zip5 = $8, norm_address_key = $9, cyclops_business_id = $10, matched_existing_cyclops = $11, last_seen_ts = now()
WHERE agb_doc_id = $12
RETURNING business_seq, agb_doc_id
Covered by index on (agb_doc_id)
Rows: 536247
Row progression: 536247, 1
Row estimates
- agb_doc_id (=): 1
Existing indexes
- business_seq PRIMARY
- agb_doc_id UNIQUE
- agb_id bpchar_pattern_ops NULLS FIRST) WITH (deduplicate_items='true'
- audit_has_changes
- mailable
- mailable_reason
- validation_state
|
|
0 min
0.2%
|
0 ms
|
70,744
postgres
|
UPDATE business_reconciliation
SET agb_id = $1, normalized_website = $2, website_hostname = $3, website_upper_domain = $4, website_ok = $5, website_issues = $6, agb_address_full = $7, agb_city = $8, agb_state = $9, agb_zip = $10, norm_street_line = $11, norm_state = $12, norm_zip5 = $13, norm_address_key = $14, matched_existing_cyclops = $15, mailable_reason = $16, mailable_threshold_days = $17, validation_state = $18, last_seen_ts = now()
WHERE agb_doc_id = $19
RETURNING business_seq, agb_doc_id
Covered by index on (agb_doc_id)
Rows: 536247
Row progression: 536247, 1
Row estimates
- agb_doc_id (=): 1
Existing indexes
- business_seq PRIMARY
- agb_doc_id UNIQUE
- agb_id bpchar_pattern_ops NULLS FIRST) WITH (deduplicate_items='true'
- audit_has_changes
- mailable
- mailable_reason
- validation_state
|
|
0 min
< 0.1%
|
0 ms
|
6,878
postgres
|
/*pga4dash*/
SELECT $1 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
(SELECT count(*) FROM pg_catalog.pg_stat_activity WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $2)) AS "Total",
(SELECT count(*) FROM pg_catalog.pg_stat_activity WHERE state = $3 AND datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $4)) AS "Active",
(SELECT count(*) FROM pg_catalog.pg_stat_activity WHERE state = $5 AND datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $6)) AS "Idle"
) t
UNION ALL
SELECT $7 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(xact_commit) + sum(xact_rollback) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $8)) AS "Transactions",
(SELECT sum(xact_commit) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $9)) AS "Commits",
(SELECT sum(xact_rollback) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $10)) AS "Rollbacks"
) t
UNION ALL
SELECT $11 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(tup_inserted) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $12)) AS "Inserts",
(SELECT sum(tup_updated) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $13)) AS "Updates",
(SELECT sum(tup_deleted) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $14)) AS "Deletes"
) t
UNION ALL
SELECT $15 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(tup_fetched) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $16)) AS "Fetched",
(SELECT sum(tup_returned) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $17)) AS "Returned"
) t
UNION ALL
SELECT $18 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(blks_read) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $19)) AS "Reads",
(SELECT sum(blks_hit) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $20)) AS "Hits"
) t
|
|
0 min
< 0.1%
|
3 ms
|
618
postgres
|
SELECT schemaname AS schema, t.relname AS table, ix.relname AS name, regexp_replace(pg_get_indexdef(i.indexrelid), $1, $2) AS columns, regexp_replace(pg_get_indexdef(i.indexrelid), $3, $4) AS using, indisunique AS unique, indisprimary AS primary, indisvalid AS valid, indexprs::text, indpred::text, pg_get_indexdef(i.indexrelid) AS definition FROM pg_index i INNER JOIN pg_class t ON t.oid = i.indrelid INNER JOIN pg_class ix ON ix.oid = i.indexrelid LEFT JOIN pg_stat_user_indexes ui ON ui.indexrelid = i.indexrelid WHERE schemaname IS NOT NULL ORDER BY 1, 2 /*pghero*/
|
|
0 min
< 0.1%
|
25 ms
|
81
postgres
|
SELECT COUNT(*)::text FROM public.business_reconciliation
|
|
0 min
< 0.1%
|
52 ms
|
29
postgres
|
SELECT * FROM public.business_reconciliation
ORDER BY business_seq ASC LIMIT $1
Covered by index on (business_seq)
Rows: 536247
Row progression: 536247, 1
Row estimates
- business_seq (sort): 1
Existing indexes
- business_seq PRIMARY
- agb_doc_id UNIQUE
- agb_id bpchar_pattern_ops NULLS FIRST) WITH (deduplicate_items='true'
- audit_has_changes
- mailable
- mailable_reason
- validation_state
|
|
0 min
< 0.1%
|
0 ms
|
17,197
postgres
|
/*pga4dash*/
SELECT $1 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(tup_fetched) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $2)) AS "Fetched",
(SELECT sum(tup_returned) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $3)) AS "Returned"
) t
UNION ALL
SELECT $4 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(blks_read) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $5)) AS "Reads",
(SELECT sum(blks_hit) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $6)) AS "Hits"
) t
|
|
0 min
< 0.1%
|
0 ms
|
4,256
postgres
|
/*pga4dash*/
SELECT $1 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
(SELECT count(*) FROM pg_catalog.pg_stat_activity WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $2)) AS "Total",
(SELECT count(*) FROM pg_catalog.pg_stat_activity WHERE state = $3 AND datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $4)) AS "Active",
(SELECT count(*) FROM pg_catalog.pg_stat_activity WHERE state = $5 AND datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $6)) AS "Idle"
) t
UNION ALL
SELECT $7 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(xact_commit) + sum(xact_rollback) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $8)) AS "Transactions",
(SELECT sum(xact_commit) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $9)) AS "Commits",
(SELECT sum(xact_rollback) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $10)) AS "Rollbacks"
) t
UNION ALL
SELECT $11 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(tup_inserted) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $12)) AS "Inserts",
(SELECT sum(tup_updated) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $13)) AS "Updates",
(SELECT sum(tup_deleted) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $14)) AS "Deletes"
) t
UNION ALL
SELECT $15 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(blks_read) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $16)) AS "Reads",
(SELECT sum(blks_hit) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $17)) AS "Hits"
) t
|
|
0 min
< 0.1%
|
0 ms
|
8,596
postgres
|
UPDATE business_reconciliation
SET normalized_website = $1, website_hostname = $2, website_upper_domain = $3, website_ok = $4, website_issues = $5, agb_address_full = $6, agb_city = $7, agb_state = $8, agb_zip = $9, norm_street_line = $10, norm_state = $11, norm_zip5 = $12, norm_address_key = $13, matched_existing_cyclops = $14, mailable_reason = $15, mailable_threshold_days = $16, validation_state = $17, last_seen_ts = now()
WHERE agb_doc_id = $18
RETURNING business_seq, agb_doc_id
Covered by index on (agb_doc_id)
Rows: 536247
Row progression: 536247, 1
Row estimates
- agb_doc_id (=): 1
Existing indexes
- business_seq PRIMARY
- agb_doc_id UNIQUE
- agb_id bpchar_pattern_ops NULLS FIRST) WITH (deduplicate_items='true'
- audit_has_changes
- mailable
- mailable_reason
- validation_state
|
|
0 min
< 0.1%
|
2 ms
|
868
postgres
|
SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
FROM pg_type as t
LEFT JOIN pg_range as r ON oid = rngtypid
WHERE
t.typname IN ($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)
|
|
0 min
< 0.1%
|
0 ms
|
16,942
postgres
|
/*pga4dash*/
SELECT $1 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(blks_read) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $2)) AS "Reads",
(SELECT sum(blks_hit) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = $3)) AS "Hits"
) t
|
|
0 min
< 0.1%
|
2 ms
|
590
postgres
|
WITH query_stats AS ( SELECT LEFT(query, $1) AS query, queryid AS query_hash, rolname AS user, ((total_plan_time + total_exec_time) / $2 / $3) AS total_minutes, ((total_plan_time + total_exec_time) / calls) AS average_time, calls FROM pg_stat_statements INNER JOIN pg_database ON pg_database.oid = pg_stat_statements.dbid INNER JOIN pg_roles ON pg_roles.oid = pg_stat_statements.userid WHERE calls > $4 AND pg_database.datname = current_database() ) SELECT query, query AS explainable_query, query_hash, query_stats.user, total_minutes, average_time, calls, total_minutes * $5 / (SELECT SUM(total_minutes) FROM query_stats) AS total_percent, (SELECT SUM(total_minutes) FROM query_stats) AS all_queries_total_minutes FROM query_stats ORDER BY "total_minutes" DESC LIMIT $6 /*pghero*/
|
|
0 min
< 0.1%
|
457 ms
|
2
postgres
|
SELECT COUNT(*)::text FROM public.id_lookup
|
|
0 min
< 0.1%
|
0 ms
|
8,301
postgres
|
UPDATE business_reconciliation
SET normalized_website = $1, website_hostname = $2, website_upper_domain = $3, website_ok = $4, website_issues = $5, norm_street_line = $6, norm_state = $7, norm_zip5 = $8, norm_address_key = $9, matched_existing_cyclops = $10, last_seen_ts = now()
WHERE agb_doc_id = $11
RETURNING business_seq, agb_doc_id
Covered by index on (agb_doc_id)
Rows: 536247
Row progression: 536247, 1
Row estimates
- agb_doc_id (=): 1
Existing indexes
- business_seq PRIMARY
- agb_doc_id UNIQUE
- agb_id bpchar_pattern_ops NULLS FIRST) WITH (deduplicate_items='true'
- audit_has_changes
- mailable
- mailable_reason
- validation_state
|
|
0 min
< 0.1%
|
0 ms
|
4,426
postgres
|
UPDATE business_reconciliation
SET agb_id = $1, normalized_website = $2, website_hostname = $3, website_upper_domain = $4, website_ok = $5, website_issues = $6, agb_address_full = $7, agb_city = $8, agb_state = $9, agb_zip = $10, norm_state = $11, norm_zip5 = $12, matched_existing_cyclops = $13, mailable_reason = $14, mailable_threshold_days = $15, validation_state = $16, last_seen_ts = now()
WHERE agb_doc_id = $17
RETURNING business_seq, agb_doc_id
Covered by index on (agb_doc_id)
Rows: 536247
Row progression: 536247, 1
Row estimates
- agb_doc_id (=): 1
Existing indexes
- business_seq PRIMARY
- agb_doc_id UNIQUE
- agb_id bpchar_pattern_ops NULLS FIRST) WITH (deduplicate_items='true'
- audit_has_changes
- mailable
- mailable_reason
- validation_state
|
|
0 min
< 0.1%
|
0 ms
|
4,200
postgres
|
INSERT INTO business_reconciliation (
agb_doc_id, agb_id, cyclops_business_id, matched_existing_cyclops,
agb_address_full, agb_city, agb_state, agb_zip,
norm_street_line, norm_state, norm_zip5, norm_address_key
)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12)
ON CONFLICT (agb_doc_id)
DO UPDATE SET
agb_id = COALESCE(EXCLUDED.agb_id, business_reconciliation.agb_id),
cyclops_business_id = COALESCE(EXCLUDED.cyclops_business_id, business_reconciliation.cyclops_business_id),
matched_existing_cyclops = EXCLUDED.matched_existing_cyclops,
agb_address_full = COALESCE(EXCLUDED.agb_address_full, business_reconciliation.agb_address_full),
agb_city = COALESCE(EXCLUDED.agb_city, business_reconciliation.agb_city),
agb_state = COALESCE(EXCLUDED.agb_state, business_reconciliation.agb_state),
agb_zip = COALESCE(EXCLUDED.agb_zip, business_reconciliation.agb_zip),
norm_street_line = COALESCE(EXCLUDED.norm_street_line, business_reconciliation.norm_street_line),
norm_state = COALESCE(EXCLUDED.norm_state, business_reconciliation.norm_state),
norm_zip5 = COALESCE(EXCLUDED.norm_zip5, business_reconciliation.norm_zip5),
norm_address_key = COALESCE(EXCLUDED.norm_address_key, business_reconciliation.norm_address_key),
last_seen_ts = now()
RETURNING business_seq, agb_doc_id
|
|
0 min
< 0.1%
|
145 ms
|
3
postgres
|
SELECT * FROM public.business_reconciliation
WHERE matched_existing_cyclops is null
Details
CREATE INDEX CONCURRENTLY ON business_reconciliation (matched_existing_cyclops)
Rows: 536247
Row progression: 536247, 0
Row estimates
- matched_existing_cyclops (null): 0
Existing indexes
- business_seq PRIMARY
- agb_doc_id UNIQUE
- agb_id bpchar_pattern_ops NULLS FIRST) WITH (deduplicate_items='true'
- audit_has_changes
- mailable
- mailable_reason
- validation_state
|
|
0 min
< 0.1%
|
399 ms
|
1
postgres
|
EXPLAIN (FORMAT JSON, ANALYZE true, VERBOSE true, COSTS true, TIMING true, BUFFERS true, SUMMARY true, SETTINGS true, WAL true, GENERIC_PLAN false) SELECT * FROM public.business_reconciliation
WHERE matched_existing_cyclops is false
|
|
0 min
< 0.1%
|
0 ms
|
4,200
postgres
|
INSERT INTO business_reconciliation (
agb_doc_id, agb_id,
raw_website, normalized_website, website_hostname, website_upper_domain,
website_ok, website_issues
)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8)
ON CONFLICT (agb_doc_id)
DO UPDATE SET
agb_id = COALESCE(EXCLUDED.agb_id, business_reconciliation.agb_id),
raw_website = COALESCE(EXCLUDED.raw_website, business_reconciliation.raw_website),
normalized_website = COALESCE(EXCLUDED.normalized_website, business_reconciliation.normalized_website),
website_hostname = COALESCE(EXCLUDED.website_hostname, business_reconciliation.website_hostname),
website_upper_domain = COALESCE(EXCLUDED.website_upper_domain, business_reconciliation.website_upper_domain),
website_ok = EXCLUDED.website_ok,
website_issues = EXCLUDED.website_issues,
last_seen_ts = now()
RETURNING business_seq, agb_doc_id
|
|
0 min
< 0.1%
|
1 ms
|
586
postgres
|
SELECT n.nspname AS table_schema, c.relname AS table, attname AS column, format_type(a.atttypid, a.atttypmod) AS column_type, pg_get_expr(d.adbin, d.adrelid) AS default_value FROM pg_catalog.pg_attribute a INNER JOIN pg_catalog.pg_class c ON c.oid = a.attrelid INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace INNER JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid, d.adnum) WHERE NOT a.attisdropped AND a.attnum > $1 AND pg_get_expr(d.adbin, d.adrelid) LIKE $2 AND n.nspname NOT LIKE $3 /*pghero*/
|
|
0 min
< 0.1%
|
28 ms
|
8
postgres
|
SELECT * FROM public.business_reconciliation
ORDER BY business_seq DESC LIMIT $1
Covered by index on (business_seq)
Rows: 536247
Row progression: 536247, 1
Row estimates
- business_seq (sort): 1
Existing indexes
- business_seq PRIMARY
- agb_doc_id UNIQUE
- agb_id bpchar_pattern_ops NULLS FIRST) WITH (deduplicate_items='true'
- audit_has_changes
- mailable
- mailable_reason
- validation_state
|
|
0 min
< 0.1%
|
0 ms
|
1,966
postgres
|
UPDATE business_reconciliation
SET agb_id = $1, normalized_website = $2, website_hostname = $3, website_upper_domain = $4, website_ok = $5, website_issues = $6, agb_address_full = $7, agb_city = $8, agb_state = $9, agb_zip = $10, norm_street_line = $11, norm_state = $12, matched_existing_cyclops = $13, mailable_reason = $14, mailable_threshold_days = $15, validation_state = $16, last_seen_ts = now()
WHERE agb_doc_id = $17
RETURNING business_seq, agb_doc_id
Covered by index on (agb_doc_id)
Rows: 536247
Row progression: 536247, 1
Row estimates
- agb_doc_id (=): 1
Existing indexes
- business_seq PRIMARY
- agb_doc_id UNIQUE
- agb_id bpchar_pattern_ops NULLS FIRST) WITH (deduplicate_items='true'
- audit_has_changes
- mailable
- mailable_reason
- validation_state
|
|
0 min
< 0.1%
|
0 ms
|
868
postgres
|
SET SESSION timezone TO 'UTC'
|
|
0 min
< 0.1%
|
0 ms
|
2,068
postgres
|
UPDATE business_reconciliation
SET agb_id = $1, raw_website = $2, normalized_website = $3, website_hostname = $4, website_upper_domain = $5, website_ok = $6, website_issues = $7, agb_address_full = $8, agb_city = $9, agb_state = $10, agb_zip = $11, norm_street_line = $12, norm_state = $13, norm_zip5 = $14, norm_address_key = $15, matched_existing_cyclops = $16, last_seen_ts = now()
WHERE agb_doc_id = $17
RETURNING business_seq, agb_doc_id
Covered by index on (agb_doc_id)
Rows: 536247
Row progression: 536247, 1
Row estimates
- agb_doc_id (=): 1
Existing indexes
- business_seq PRIMARY
- agb_doc_id UNIQUE
- agb_id bpchar_pattern_ops NULLS FIRST) WITH (deduplicate_items='true'
- audit_has_changes
- mailable
- mailable_reason
- validation_state
|
|
0 min
< 0.1%
|
88 ms
|
2
postgres
|
SELECT * FROM public.pg_stat_statements
|
|
0 min
< 0.1%
|
0 ms
|
764
postgres
|
SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
FROM pg_type as t
LEFT JOIN pg_range as r ON oid = rngtypid
WHERE
t.typelem IN ($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)
|
|
0 min
< 0.1%
|
0 ms
|
868
postgres
|
SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
FROM pg_type as t
LEFT JOIN pg_range as r ON oid = rngtypid
WHERE
t.typtype IN ($1, $2, $3)
|
|
0 min
< 0.1%
|
0 ms
|
586
postgres
|
SELECT n.nspname AS schema, c.relname AS table, $1 - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid)) AS transactions_left FROM pg_class c INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_class t ON c.reltoastrelid = t.oid WHERE c.relkind = $2 AND ($3 - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid))) < $4 ORDER BY 3, 1, 2 /*pghero*/
|
|
0 min
< 0.1%
|
1 ms
|
144
postgres
|
SELECT set_config($1,$2,$3) FROM pg_show_all_settings() WHERE name = $4
|
|
0 min
< 0.1%
|
134 ms
|
1
postgres
|
SELECT
raw_website,
normalized_website,
website_hostname,
website_upper_domain
FROM public.business_reconciliation
WHERE raw_website ILIKE $1
ORDER BY last_seen_ts DESC
LIMIT $2
Details
CREATE INDEX CONCURRENTLY ON business_reconciliation USING gist (raw_website gist_trgm_ops)
Rows: 536247
Row progression: 536247, 0
Row estimates
- raw_website (~~*): 0
- last_seen_ts (sort): 1
Existing indexes
- business_seq PRIMARY
- agb_doc_id UNIQUE
- agb_id bpchar_pattern_ops NULLS FIRST) WITH (deduplicate_items='true'
- audit_has_changes
- mailable
- mailable_reason
- validation_state
|
|
0 min
< 0.1%
|
0 ms
|
1,300
postgres
|
INSERT INTO business_reconciliation (
agb_doc_id,
agb_id,
cyclops_business_id,
matched_existing_cyclops,
agb_address_full,
agb_city,
agb_state,
agb_zip,
norm_street_line,
norm_state,
norm_zip5,
norm_address_key
)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12)
ON CONFLICT (agb_doc_id)
DO UPDATE SET
agb_id = EXCLUDED.agb_id,
cyclops_business_id = EXCLUDED.cyclops_business_id,
matched_existing_cyclops = EXCLUDED.matched_existing_cyclops,
agb_address_full = EXCLUDED.agb_address_full,
agb_city = EXCLUDED.agb_city,
agb_state = EXCLUDED.agb_state,
agb_zip = EXCLUDED.agb_zip,
norm_street_line = EXCLUDED.norm_street_line,
norm_state = EXCLUDED.norm_state,
norm_zip5 = EXCLUDED.norm_zip5,
norm_address_key = EXCLUDED.norm_address_key,
last_seen_ts = now()
RETURNING business_seq, agb_doc_id
|
|
0 min
< 0.1%
|
1 ms
|
138
postgres
|
SELECT DISTINCT att.attname as name, att.attnum as OID, pg_catalog.format_type(ty.oid,$1) AS datatype,
att.attnotnull as not_null,
CASE WHEN att.atthasdef OR att.attidentity != $2 OR ty.typdefault IS NOT NULL THEN $3
ELSE $4 END as has_default_val, des.description, seq.seqtypid
FROM pg_catalog.pg_attribute att
JOIN pg_catalog.pg_type ty ON ty.oid=atttypid
JOIN pg_catalog.pg_namespace tn ON tn.oid=ty.typnamespace
JOIN pg_catalog.pg_class cl ON cl.oid=att.attrelid
JOIN pg_catalog.pg_namespace na ON na.oid=cl.relnamespace
LEFT OUTER JOIN pg_catalog.pg_type et ON et.oid=ty.typelem
LEFT OUTER JOIN pg_catalog.pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum
LEFT OUTER JOIN (pg_catalog.pg_depend JOIN pg_catalog.pg_class cs ON classid=$5::regclass AND objid=cs.oid AND cs.relkind=$6) ON refobjid=att.attrelid AND refobjsubid=att.attnum
LEFT OUTER JOIN pg_catalog.pg_namespace ns ON ns.oid=cs.relnamespace
LEFT OUTER JOIN pg_catalog.pg_index pi ON pi.indrelid=att.attrelid AND indisprimary
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=att.attrelid AND des.objsubid=att.attnum AND des.classoid=$7::regclass)
LEFT OUTER JOIN pg_catalog.pg_sequence seq ON cs.oid=seq.seqrelid
WHERE
att.attrelid = $8::oid
AND att.attnum > $9
AND att.attisdropped IS FALSE
ORDER BY att.attnum
|
|
0 min
< 0.1%
|
0 ms
|
1,169
postgres
|
UPDATE business_reconciliation
SET normalized_website = $1, website_hostname = $2, website_upper_domain = $3, website_ok = $4, website_issues = $5, norm_street_line = $6, norm_state = $7, norm_zip5 = $8, norm_address_key = $9, last_seen_ts = now()
WHERE agb_doc_id = $10
RETURNING business_seq, agb_doc_id
Covered by index on (agb_doc_id)
Rows: 536247
Row progression: 536247, 1
Row estimates
- agb_doc_id (=): 1
Existing indexes
- business_seq PRIMARY
- agb_doc_id UNIQUE
- agb_id bpchar_pattern_ops NULLS FIRST) WITH (deduplicate_items='true'
- audit_has_changes
- mailable
- mailable_reason
- validation_state
|
|
0 min
< 0.1%
|
0 ms
|
868
postgres
|
SELECT t.oid, t.typname
FROM pg_type as t
WHERE t.typname IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
|
|
0 min
< 0.1%
|
95 ms
|
1
postgres
|
CREATE DATABASE "Cyclops"
WITH
OWNER = postgres
ENCODING = 'UTF8'
LOCALE_PROVIDER = 'libc'
CONNECTION LIMIT = -1
IS_TEMPLATE = False
|
|
0 min
< 0.1%
|
31 ms
|
3
postgres
|
SELECT n.nspname schema_name,
p.proname func_name,
p.proargnames arg_names,
COALESCE(proallargtypes::regtype[], proargtypes::regtype[])::text[] arg_types,
p.proargmodes arg_modes,
prorettype::regtype::text return_type,
CASE WHEN p.prokind = $1 THEN $2 ELSE $3 END is_aggregate,
CASE WHEN p.prokind = $4 THEN $5 ELSE $6 END is_window,
p.proretset is_set_returning,
d.deptype = $7 is_extension,
pg_catalog.pg_get_expr(proargdefaults, $8) AS arg_defaults
FROM pg_catalog.pg_proc p
INNER JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
LEFT JOIN pg_catalog.pg_depend d ON d.objid = p.oid and d.deptype = $9
WHERE p.prorettype::regtype != $10::regtype
AND n.nspname IN ($11,$12)
ORDER BY 1, 2
|
|
0 min
< 0.1%
|
0 ms
|
1,007
postgres
|
/*pga4dash*/
SELECT $1 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(tup_fetched) FROM pg_catalog.pg_stat_database) AS "Fetched",
(SELECT sum(tup_returned) FROM pg_catalog.pg_stat_database) AS "Returned"
) t
UNION ALL
SELECT $2 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(blks_read) FROM pg_catalog.pg_stat_database) AS "Reads",
(SELECT sum(blks_hit) FROM pg_catalog.pg_stat_database) AS "Hits"
) t
|
|
0 min
< 0.1%
|
14 ms
|
6
postgres
|
SELECT n.nspname schema_name,
p.proname func_name,
p.proargnames,
COALESCE(proallargtypes::regtype[], proargtypes::regtype[])::text[],
p.proargmodes,
prorettype::regtype::text return_type,
p.prokind is_aggregate,
p.prokind is_window,
p.proretset is_set_returning,
pg_get_expr(proargdefaults, $1) AS arg_defaults
FROM pg_catalog.pg_proc p
INNER JOIN pg_catalog.pg_namespace n
ON n.oid = p.pronamespace
WHERE p.prorettype::regtype != $2::regtype
ORDER BY 1, 2
|
|
0 min
< 0.1%
|
0 ms
|
586
postgres
|
SELECT pid, state, application_name AS source, age(NOW(), COALESCE(query_start, xact_start)) AS duration, (wait_event IS NOT NULL) AS waiting, query, COALESCE(query_start, xact_start) AS started_at, EXTRACT($1 FROM NOW() - COALESCE(query_start, xact_start)) * $2 AS duration_ms, usename AS user, backend_type FROM pg_stat_activity WHERE state <> $3 AND pid <> pg_backend_pid() AND datname = current_database() AND NOW() - COALESCE(query_start, xact_start) > interval $4 AND query <> $5 ORDER BY COALESCE(query_start, xact_start) DESC /*pghero*/
|
|
0 min
< 0.1%
|
4 ms
|
22
postgres
|
SELECT pg_database_size(current_database()) /*pghero*/
|
|
0 min
< 0.1%
|
0 ms
|
1,007
postgres
|
/*pga4dash*/
SELECT $1 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(blks_read) FROM pg_catalog.pg_stat_database) AS "Reads",
(SELECT sum(blks_hit) FROM pg_catalog.pg_stat_database) AS "Hits"
) t
|
|
0 min
< 0.1%
|
7 ms
|
11
postgres
|
SELECT * FROM public.business_reconciliation
ORDER BY matched_existing_cyclops ASC NULLS LAST
|
|
0 min
< 0.1%
|
0 ms
|
678
postgres
|
UPDATE business_reconciliation
SET agb_id = $1, normalized_website = $2, website_hostname = $3, website_upper_domain = $4, website_ok = $5, website_issues = $6, agb_address_full = $7, agb_city = $8, agb_state = $9, agb_zip = $10, norm_state = $11, matched_existing_cyclops = $12, mailable_reason = $13, mailable_threshold_days = $14, validation_state = $15, last_seen_ts = now()
WHERE agb_doc_id = $16
RETURNING business_seq, agb_doc_id
Covered by index on (agb_doc_id)
Rows: 536247
Row progression: 536247, 1
Row estimates
- agb_doc_id (=): 1
Existing indexes
- business_seq PRIMARY
- agb_doc_id UNIQUE
- agb_id bpchar_pattern_ops NULLS FIRST) WITH (deduplicate_items='true'
- audit_has_changes
- mailable
- mailable_reason
- validation_state
|
|
0 min
< 0.1%
|
0 ms
|
265
postgres
|
/*pga4dash*/
SELECT $1 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
(SELECT count(*) FROM pg_catalog.pg_stat_activity) AS "Total",
(SELECT count(*) FROM pg_catalog.pg_stat_activity WHERE state = $2) AS "Active",
(SELECT count(*) FROM pg_catalog.pg_stat_activity WHERE state = $3) AS "Idle"
) t
UNION ALL
SELECT $4 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(xact_commit) + sum(xact_rollback) FROM pg_catalog.pg_stat_database) AS "Transactions",
(SELECT sum(xact_commit) FROM pg_catalog.pg_stat_database) AS "Commits",
(SELECT sum(xact_rollback) FROM pg_catalog.pg_stat_database) AS "Rollbacks"
) t
UNION ALL
SELECT $5 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(tup_inserted) FROM pg_catalog.pg_stat_database) AS "Inserts",
(SELECT sum(tup_updated) FROM pg_catalog.pg_stat_database) AS "Updates",
(SELECT sum(tup_deleted) FROM pg_catalog.pg_stat_database) AS "Deletes"
) t
UNION ALL
SELECT $6 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(tup_fetched) FROM pg_catalog.pg_stat_database) AS "Fetched",
(SELECT sum(tup_returned) FROM pg_catalog.pg_stat_database) AS "Returned"
) t
UNION ALL
SELECT $7 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(blks_read) FROM pg_catalog.pg_stat_database) AS "Reads",
(SELECT sum(blks_hit) FROM pg_catalog.pg_stat_database) AS "Hits"
) t
|
|
0 min
< 0.1%
|
64 ms
|
1
postgres
|
-- last apply error, if any
-- 3) Helpful indexes for ops and analytics
CREATE INDEX IF NOT EXISTS idx_business_recon_mailable
ON business_reconciliation (mailable)
|
|
0 min
< 0.1%
|
64 ms
|
1
postgres
|
CREATE INDEX IF NOT EXISTS idx_business_recon_validation_state
ON business_reconciliation (validation_state)
|
|
0 min
< 0.1%
|
0 ms
|
586
postgres
|
SELECT state, COUNT(*) AS connections FROM pg_stat_activity GROUP BY 1 ORDER BY 2 DESC, 1 /*pghero*/
|
|
0 min
< 0.1%
|
59 ms
|
1
postgres
|
CREATE INDEX IF NOT EXISTS idx_business_recon_mailable_reason
ON business_reconciliation (mailable_reason)
|
|
0 min
< 0.1%
|
0 ms
|
251
postgres
|
/*pga4dash*/
SELECT $1 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
(SELECT count(*) FROM pg_catalog.pg_stat_activity) AS "Total",
(SELECT count(*) FROM pg_catalog.pg_stat_activity WHERE state = $2) AS "Active",
(SELECT count(*) FROM pg_catalog.pg_stat_activity WHERE state = $3) AS "Idle"
) t
UNION ALL
SELECT $4 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(xact_commit) + sum(xact_rollback) FROM pg_catalog.pg_stat_database) AS "Transactions",
(SELECT sum(xact_commit) FROM pg_catalog.pg_stat_database) AS "Commits",
(SELECT sum(xact_rollback) FROM pg_catalog.pg_stat_database) AS "Rollbacks"
) t
UNION ALL
SELECT $5 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(tup_inserted) FROM pg_catalog.pg_stat_database) AS "Inserts",
(SELECT sum(tup_updated) FROM pg_catalog.pg_stat_database) AS "Updates",
(SELECT sum(tup_deleted) FROM pg_catalog.pg_stat_database) AS "Deletes"
) t
UNION ALL
SELECT $6 AS chart_name, pg_catalog.row_to_json(t) AS chart_data
FROM (SELECT
(SELECT sum(blks_read) FROM pg_catalog.pg_stat_database) AS "Reads",
(SELECT sum(blks_hit) FROM pg_catalog.pg_stat_database) AS "Hits"
) t
|
|
0 min
< 0.1%
|
59 ms
|
1
postgres
|
SELECT * FROM public.business_reconciliation
WHERE mailable IS TRUE
ORDER BY business_seq ASC LIMIT $1
|
|
0 min
< 0.1%
|
57 ms
|
1
postgres
|
CREATE INDEX IF NOT EXISTS idx_business_recon_audit_has_changes
ON business_reconciliation (audit_has_changes)
|
|
0 min
< 0.1%
|
0 ms
|
495
postgres
|
UPDATE business_reconciliation
SET normalized_website = $1, website_hostname = $2, website_upper_domain = $3, website_ok = $4, website_issues = $5, norm_street_line = $6, norm_state = $7, norm_zip5 = $8, norm_address_key = $9, cyclops_business_id = $10, matched_existing_cyclops = $11, mailable = $12, mailable_reason = $13, mailable_last_mailed_on = $14, mailable_days_since = $15, mailable_threshold_days = $16, validation_state = $17, audit_has_changes = $18, audit_change_count = $19, audit_diff_fields = $20, audit_diff_before = $21, audit_diff_after = $22, audit_last_compared_at = $23, last_seen_ts = now()
WHERE agb_doc_id = $24
RETURNING business_seq, agb_doc_id
Covered by index on (agb_doc_id)
Rows: 536247
Row progression: 536247, 1
Row estimates
- agb_doc_id (=): 1
Existing indexes
- business_seq PRIMARY
- agb_doc_id UNIQUE
- agb_id bpchar_pattern_ops NULLS FIRST) WITH (deduplicate_items='true'
- audit_has_changes
- mailable
- mailable_reason
- validation_state
|
|
0 min
< 0.1%
|
4 ms
|
12
postgres
|
SELECT nsp.nspname schema_name,
cls.relname table_name,
att.attname column_name,
att.atttypid::regtype::text type_name,
att.atthasdef AS has_default,
pg_get_expr(def.adbin, def.adrelid) as default
FROM pg_catalog.pg_attribute att
INNER JOIN pg_catalog.pg_class cls
ON att.attrelid = cls.oid
INNER JOIN pg_catalog.pg_namespace nsp
ON cls.relnamespace = nsp.oid
LEFT OUTER JOIN pg_attrdef def
ON def.adrelid = att.attrelid
AND def.adnum = att.attnum
WHERE cls.relkind = ANY($1)
AND NOT att.attisdropped
AND att.attnum > $2
AND NOT cls.relispartition
ORDER BY 1, 2, att.attnum
|
|
0 min
< 0.1%
|
47 ms
|
1
postgres
|
CREATE INDEX business_id_idx
ON public.business_reconciliation USING btree
(agb_id bpchar_pattern_ops ASC NULLS FIRST)
WITH (deduplicate_items=True)
|
|
0 min
< 0.1%
|
0 ms
|
300
postgres
|
UPDATE business_reconciliation
SET normalized_website = $1, website_hostname = $2, website_upper_domain = $3, website_ok = $4, website_issues = $5, norm_street_line = $6, norm_state = $7, norm_zip5 = $8, norm_address_key = $9, cyclops_business_id = $10, matched_existing_cyclops = $11, mailable = $12, mailable_reason = $13, mailable_threshold_days = $14, last_seen_ts = now()
WHERE agb_doc_id = $15
RETURNING business_seq, agb_doc_id
Covered by index on (agb_doc_id)
Rows: 536247
Row progression: 536247, 1
Row estimates
- agb_doc_id (=): 1
Existing indexes
- business_seq PRIMARY
- agb_doc_id UNIQUE
- agb_id bpchar_pattern_ops NULLS FIRST) WITH (deduplicate_items='true'
- audit_has_changes
- mailable
- mailable_reason
- validation_state
|
|
0 min
< 0.1%
|
0 ms
|
518
postgres
|
SELECT last_value FROM "public"."business_addresses_id_seq" UNION ALL SELECT last_value FROM "public"."campaigns_id_seq" UNION ALL SELECT last_value FROM "public"."categories_id_seq" UNION ALL SELECT last_value FROM "public"."products_product_id_seq" /*pghero*/
|
|
0 min
< 0.1%
|
0 ms
|
273
postgres
|
SELECT at.attname, at.attnum, ty.typname
FROM pg_catalog.pg_attribute at LEFT JOIN pg_catalog.pg_type ty ON (ty.oid = at.atttypid)
JOIN pg_catalog.pg_class as cl ON cl.oid=AT.attrelid
JOIN pg_catalog.pg_namespace as nsp ON nsp.oid=cl.relnamespace
WHERE
cl.relname = $1 AND nsp.nspname=$2 AND
attnum = ANY (
(SELECT con.conkey FROM pg_catalog.pg_class rel LEFT OUTER JOIN pg_catalog.pg_constraint con ON con.conrelid=rel.oid
JOIN pg_catalog.pg_namespace as nsp ON nsp.oid=REL.relnamespace
AND con.contype=$3 WHERE rel.relkind IN ($4,$5,$6, $7) AND
rel.relname = $8 AND nsp.nspname=$9
)::oid[])
|
|
0 min
< 0.1%
|
43 ms
|
1
postgres
|
CREATE EXTENSION IF NOT EXISTS "btree_gin"
|
|
0 min
< 0.1%
|
0 ms
|
388
postgres
|
UPDATE business_reconciliation
SET agb_id = $1, normalized_website = $2, website_hostname = $3, website_upper_domain = $4, website_ok = $5, website_issues = $6, matched_existing_cyclops = $7, mailable_reason = $8, mailable_threshold_days = $9, validation_state = $10, last_seen_ts = now()
WHERE agb_doc_id = $11
RETURNING business_seq, agb_doc_id
Covered by index on (agb_doc_id)
Rows: 536247
Row progression: 536247, 1
Row estimates
- agb_doc_id (=): 1
Existing indexes
- business_seq PRIMARY
- agb_doc_id UNIQUE
- agb_id bpchar_pattern_ops NULLS FIRST) WITH (deduplicate_items='true'
- audit_has_changes
- mailable
- mailable_reason
- validation_state
|
|
0 min
< 0.1%
|
42 ms
|
1
postgres
|
DROP DATABASE IF EXISTS "Cyclops"
|
|
0 min
< 0.1%
|
7 ms
|
6
postgres
|
TRUNCATE TABLE public.business_reconciliation
|
|
0 min
< 0.1%
|
0 ms
|
586
postgres
|
SELECT n.nspname AS schema, c.relname AS sequence, has_sequence_privilege(c.oid, $1) AS readable FROM pg_class c INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = $2 AND n.nspname NOT IN ($3, $4) /*pghero*/
|
|
0 min
< 0.1%
|
2 ms
|
22
postgres
|
SELECT n.nspname AS schema, c.relname AS relation, CASE c.relkind WHEN $1 THEN $2 WHEN $3 then $4 ELSE $5 END AS type, pg_table_size(c.oid) AS size_bytes FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname NOT IN ($6, $7) AND n.nspname !~ $8 AND c.relkind IN ($9, $10, $11) ORDER BY pg_table_size(c.oid) DESC, 2 ASC /*pghero*/
|
|
0 min
< 0.1%
|
32 ms
|
1
postgres
|
CREATE EXTENSION IF NOT EXISTS pg_stat_statements /*pghero*/
|
|
0 min
< 0.1%
|
0 ms
|
300
postgres
|
INSERT INTO business_reconciliation (
agb_doc_id,
agb_id,
raw_website,
normalized_website,
website_hostname,
website_upper_domain,
website_ok,
website_issues
)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8)
ON CONFLICT (agb_doc_id)
DO UPDATE SET
agb_id = EXCLUDED.agb_id,
raw_website = EXCLUDED.raw_website,
normalized_website = EXCLUDED.normalized_website,
website_hostname = EXCLUDED.website_hostname,
website_upper_domain = EXCLUDED.website_upper_domain,
website_ok = EXCLUDED.website_ok,
website_issues = EXCLUDED.website_issues,
last_seen_ts = now()
RETURNING business_seq, agb_doc_id
|
|
0 min
< 0.1%
|
1 ms
|
24
postgres
|
SELECT rel.oid, rel.relname AS name, rel.reltablespace AS spcoid,rel.relacl AS relacl_str,
(CASE WHEN length(spc.spcname::text) > $1 OR rel.relkind = $2 THEN spc.spcname ELSE
(SELECT sp.spcname FROM pg_catalog.pg_database dtb
JOIN pg_catalog.pg_tablespace sp ON dtb.dattablespace=sp.oid
WHERE dtb.oid = $3::oid)
END) as spcname,
(CASE rel.relreplident
WHEN $4 THEN $5
WHEN $6 THEN $7
WHEN $8 THEN $9
WHEN $10 THEN $11
END) as replica_identity,
(select nspname FROM pg_catalog.pg_namespace WHERE oid = $12::oid ) as schema,
pg_catalog.pg_get_userbyid(rel.relowner) AS relowner, rel.relkind,
(CASE WHEN rel.relkind = $13 THEN $14 ELSE $15 END) AS is_partitioned,
rel.relhassubclass, rel.reltuples::bigint, des.description, con.conname, con.conkey,
EXISTS(select $16 FROM pg_catalog.pg_trigger
JOIN pg_catalog.pg_proc pt ON pt.oid=tgfoid AND pt.proname=$17
JOIN pg_catalog.pg_proc pc ON pc.pronamespace=pt.pronamespace AND pc.proname=$18
WHERE tgrelid=rel.oid) AS isrepl,
(SELECT count(*) FROM pg_catalog.pg_trigger WHERE tgrelid=rel.oid AND tgisinternal = $19) AS triggercount,
(SELECT ARRAY(SELECT CASE WHEN (nspname NOT LIKE $20) THEN
pg_catalog.quote_ident(nspname)||$21||pg_catalog.quote_ident(c.relname)
ELSE pg_catalog.quote_ident(c.relname) END AS inherited_tables
FROM pg_catalog.pg_inherits i
JOIN pg_catalog.pg_class c ON c.oid = i.inhparent
JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace
WHERE i.inhrelid = rel.oid ORDER BY inhseqno)) AS coll_inherits,
(SELECT count(*)
FROM pg_catalog.pg_inherits i
JOIN pg_catalog.pg_class c ON c.oid = i.inhparent
JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace
WHERE i.inhrelid = rel.oid) AS inherited_tables_cnt,
(CASE WHEN rel.relpersistence = $22 THEN $23 ELSE $24 END) AS relpersistence,
(SELECT st.setting from pg_catalog.pg_show_all_settings() st WHERE st.name = $25) as default_amname,
substring(pg_catalog.array_to_string(rel.reloptions, $26) FROM $27) AS fillfactor,
substring(pg_catalog.array_to_string(rel.reloptions, $28) FROM $29) AS parallel_workers,
substring(pg_catalog.array_to_string(rel.reloptions, $30) FROM $31) AS toast_tuple_target,
(substring(pg_catalog.array_to_string(rel.reloptions, $32) FROM $33))::BOOL AS autovacuum_enabled,
substring(pg_catalog.array_to_string(rel.reloptions, $34) FROM $35) AS autovacuum_vacuum_threshold,
substring(pg_catalog.array_to_string(rel.reloptions, $36) FROM $37) AS autovacuum_vacuum_scale_factor,
substring(pg_catalog.array_to_string(rel.reloptions, $38) FROM $39) AS autovacuum_analyze_threshold,
substring(pg_catalog.array_to_string(rel.reloptions, $40) FROM $41) AS autovacuum_analyze_scale_factor,
substring(pg_catalog.array_to_string(rel.reloptions, $42) FROM $43) AS autovacuum_vacuum_cost_delay,
substring(pg_catalog.array_to_string(rel.reloptions, $44) FROM $45) AS autovacuum_vacuum_cost_limit,
substring(pg_catalog.array_to_string(rel.reloptions, $46) FROM $47) AS autovacuum_freeze_min_age,
substring(pg_catalog.array_to_string(rel.reloptions, $48) FROM $49) AS autovacuum_freeze_max_age,
substring(pg_catalog.array_to_string(rel.reloptions, $50) FROM $51) AS autovacuum_freeze_table_age,
(substring(pg_catalog.array_to_string(tst.reloptions, $52) FROM $53))::BOOL AS toast_autovacuum_enabled,
substring(pg_catalog.array_to_string(tst.reloptions, $54) FROM $55) AS toast_autovacuum_vacuum_threshold,
substring(pg_catalog.array_to_string(tst.reloptions, $56) FROM $57) AS toast_autovacuum_vacuum_scale_factor,
substring(pg_catalog.array_to_string(tst.reloptions, $58) FROM $59) AS toast_autovacuum_analyze_threshold,
substring(pg_catalog.array_to_string(tst.reloptions, $60) FROM $61) AS toast_autovacuum_analyze_scale_factor,
substring(pg_catalog.array_to_string(tst.reloptions, $62) FROM $63) AS toast_autovacuum_vacuum_cost_delay,
substring(pg_catalog.array_to_string(tst.reloptions, $64) FROM $65) AS toast_autovacuum_vacuum_cost_limit,
substring(pg_catalog.array_to_string(tst.reloptions, $66) FROM $67) AS toast_autovacuum_freeze_min_age,
substring(pg_catalog.array_to_string(tst.reloptions, $68) FROM $69) AS toast_autovacuum_freeze_max_age,
substring(pg_catalog.array_to_string(tst.reloptions, $70) FROM $71) AS toast_autovacuum_freeze_table_age,
rel.reloptions AS reloptions, tst.reloptions AS toast_reloptions, rel.reloftype, am.amname,
CASE WHEN typ.typname IS NOT NULL THEN (select pg_catalog.quote_ident(nspname) FROM pg_catalog.pg_namespace WHERE oid = $72::oid )||$73||pg_catalog.quote_ident(typ.typname) ELSE typ.typname END AS typname,
typ.typrelid AS typoid, rel.relrowsecurity as rlspolicy, rel.relforcerowsecurity as forcerlspolicy,
(CASE WHEN rel.reltoastrelid = $74 THEN $75 ELSE $76 END) AS hastoasttable,
(SELECT pg_catalog.array_agg(provider || $77 || label) FROM pg_catalog.pg_seclabels sl1 WHERE sl1.objoid=rel.oid AND sl1.objsubid=$78) AS seclabels,
(CASE WHEN rel.oid <= $79::oid THEN $80 ElSE $81 END) AS is_sys_table
-- Added for partition table
, (CASE WHEN rel.relkind = $82 THEN pg_catalog.pg_get_partkeydef($83::oid) ELSE $84 END) AS partition_scheme FROM pg_catalog.pg_class rel
LEFT OUTER JOIN pg_catalog.pg_tablespace spc on spc.oid=rel.reltablespace
LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=rel.oid AND des.objsubid=$85 AND des.classoid=$86::regclass)
LEFT OUTER JOIN pg_catalog.pg_constraint con ON con.conrelid=rel.oid AND con.contype=$87
LEFT OUTER JOIN pg_catalog.pg_class tst ON tst.oid = rel.reltoastrelid
LEFT JOIN pg_catalog.pg_type typ ON rel.reloftype=typ.oid
LEFT OUTER JOIN pg_catalog.pg_am am ON am.oid = rel.relam
WHERE rel.relkind IN ($88,$89,$90,$91) AND rel.relnamespace = $92::oid
AND NOT rel.relispartition
AND rel.oid = $93::oid ORDER BY rel.relname
|
|
0 min
< 0.1%
|
0 ms
|
84
postgres
|
SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
FROM pg_type as t
LEFT JOIN pg_range as r ON oid = rngtypid
WHERE
t.typelem IN ($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)
|
|
0 min
< 0.1%
|
2 ms
|
11
postgres
|
SELECT nspname AS schema, relname AS table, reltuples::bigint AS estimated_rows, pg_total_relation_size(pg_class.oid) AS size_bytes FROM pg_class INNER JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE relkind = $1 AND nspname = $2 AND relname IN ($3) ORDER BY 1, 2 /*pghero*/
|
|
0 min
< 0.1%
|
0 ms
|
189
postgres
|
UPDATE business_reconciliation
SET normalized_website = $1, website_hostname = $2, website_upper_domain = $3, website_ok = $4, website_issues = $5, agb_address_full = $6, agb_city = $7, agb_state = $8, agb_zip = $9, norm_street_line = $10, norm_state = $11, norm_zip5 = $12, matched_existing_cyclops = $13, mailable_reason = $14, mailable_threshold_days = $15, validation_state = $16, last_seen_ts = now()
WHERE agb_doc_id = $17
RETURNING business_seq, agb_doc_id
Covered by index on (agb_doc_id)
Rows: 536247
Row progression: 536247, 1
Row estimates
- agb_doc_id (=): 1
Existing indexes
- business_seq PRIMARY
- agb_doc_id UNIQUE
- agb_id bpchar_pattern_ops NULLS FIRST) WITH (deduplicate_items='true'
- audit_has_changes
- mailable
- mailable_reason
- validation_state
|
|
0 min
< 0.1%
|
25 ms
|
1
postgres
|
CREATE EXTENSION IF NOT EXISTS "pg_trgm"
|
|
0 min
< 0.1%
|
23 ms
|
1
postgres
|
CREATE EXTENSION IF NOT EXISTS "citext"
|
|
0 min
< 0.1%
|
23 ms
|
1
postgres
|
CREATE OR REPLACE FUNCTION encode_business_id(n bigint)
RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
letters text := 'ABCDEFGHJKMNPRSTUVWXYZ'; -- no I/O/Q/L
base int := length(letters); -- 22
number_slots int := 1000; -- 000..999
letter_index bigint;
num_index int;
result_letters text := '';
i int;
idx int;
BEGIN
IF n < 0 THEN
RAISE EXCEPTION 'encode_business_id: n must be non-negative, got %', n;
END IF;
letter_index := n / number_slots;
num_index := n % number_slots;
-- build 4 letters, from least-significant "digit" upward
FOR i IN 1..4 LOOP
idx := (letter_index % base)::int; -- remainder in [0,21]
result_letters := substr(letters, idx+1, 1) || result_letters;
letter_index := letter_index / base;
END LOOP;
-- left-pad just in case (should already be len 4)
result_letters := lpad(result_letters, 4, 'A');
RETURN result_letters || lpad(num_index::text, 3, '0'); -- LLLLNNN
END;
$$
|
|
0 min
< 0.1%
|
0 ms
|
237
postgres
|
UPDATE business_reconciliation
SET normalized_website = $1, website_hostname = $2, website_upper_domain = $3, website_ok = $4, website_issues = $5, norm_street_line = $6, norm_zip5 = $7, matched_existing_cyclops = $8, last_seen_ts = now()
WHERE agb_doc_id = $9
RETURNING business_seq, agb_doc_id
Covered by index on (agb_doc_id)
Rows: 536247
Row progression: 536247, 1
Row estimates
- agb_doc_id (=): 1
Existing indexes
- business_seq PRIMARY
- agb_doc_id UNIQUE
- agb_id bpchar_pattern_ops NULLS FIRST) WITH (deduplicate_items='true'
- audit_has_changes
- mailable
- mailable_reason
- validation_state
|
|
0 min
< 0.1%
|
0 ms
|
149
postgres
|
SELECT
gss_authenticated, encrypted
FROM
pg_catalog.pg_stat_gssapi
WHERE pid = pg_backend_pid()
|
|
0 min
< 0.1%
|
2 ms
|
9
postgres
|
WITH query_stats AS ( SELECT LEFT(query, $1) AS query, queryid AS query_hash, rolname AS user, ((total_plan_time + total_exec_time) / $2 / $3) AS total_minutes, ((total_plan_time + total_exec_time) / calls) AS average_time, calls FROM pg_stat_statements INNER JOIN pg_database ON pg_database.oid = pg_stat_statements.dbid INNER JOIN pg_roles ON pg_roles.oid = pg_stat_statements.userid WHERE calls > $4 AND pg_database.datname = current_database() ) SELECT query, query AS explainable_query, query_hash, query_stats.user, total_minutes, average_time, calls, total_minutes * $5 / (SELECT SUM(total_minutes) FROM query_stats) AS total_percent, (SELECT SUM(total_minutes) FROM query_stats) AS all_queries_total_minutes FROM query_stats ORDER BY "average_time" DESC LIMIT $6 /*pghero*/
|
|
0 min
< 0.1%
|
0 ms
|
151
postgres
|
UPDATE business_reconciliation
SET normalized_website = $1, website_hostname = $2, website_upper_domain = $3, website_ok = $4, website_issues = $5, agb_address_full = $6, agb_city = $7, agb_state = $8, agb_zip = $9, norm_street_line = $10, norm_state = $11, matched_existing_cyclops = $12, mailable_reason = $13, mailable_threshold_days = $14, validation_state = $15, last_seen_ts = now()
WHERE agb_doc_id = $16
RETURNING business_seq, agb_doc_id
Covered by index on (agb_doc_id)
Rows: 536247
Row progression: 536247, 1
Row estimates
- agb_doc_id (=): 1
Existing indexes
- business_seq PRIMARY
- agb_doc_id UNIQUE
- agb_id bpchar_pattern_ops NULLS FIRST) WITH (deduplicate_items='true'
- audit_has_changes
- mailable
- mailable_reason
- validation_state
|
|
0 min
< 0.1%
|
0 ms
|
586
postgres
|
SELECT slot_name, database, active FROM pg_replication_slots /*pghero*/
|
|
0 min
< 0.1%
|
2 ms
|
9
postgres
|
WITH query_stats AS ( SELECT LEFT(query, $1) AS query, queryid AS query_hash, rolname AS user, ((total_plan_time + total_exec_time) / $2 / $3) AS total_minutes, ((total_plan_time + total_exec_time) / calls) AS average_time, calls FROM pg_stat_statements INNER JOIN pg_database ON pg_database.oid = pg_stat_statements.dbid INNER JOIN pg_roles ON pg_roles.oid = pg_stat_statements.userid WHERE calls > $4 AND pg_database.datname = current_database() ) SELECT query, query AS explainable_query, query_hash, query_stats.user, total_minutes, average_time, calls, total_minutes * $5 / (SELECT SUM(total_minutes) FROM query_stats) AS total_percent, (SELECT SUM(total_minutes) FROM query_stats) AS all_queries_total_minutes FROM query_stats ORDER BY "calls" DESC LIMIT $6 /*pghero*/
|
|
0 min
< 0.1%
|
2 ms
|
10
postgres
|
SELECT COUNT(*) AS count FROM pg_available_extensions WHERE name = $1 /*pghero*/
|
|
0 min
< 0.1%
|
0 ms
|
166
postgres
|
UPDATE business_reconciliation
SET agb_id = $1, normalized_website = $2, website_hostname = $3, website_upper_domain = $4, website_ok = $5, website_issues = $6, agb_address_full = $7, matched_existing_cyclops = $8, mailable_reason = $9, mailable_threshold_days = $10, validation_state = $11, last_seen_ts = now()
WHERE agb_doc_id = $12
RETURNING business_seq, agb_doc_id
Covered by index on (agb_doc_id)
Rows: 536247
Row progression: 536247, 1
Row estimates
- agb_doc_id (=): 1
Existing indexes
- business_seq PRIMARY
- agb_doc_id UNIQUE
- agb_id bpchar_pattern_ops NULLS FIRST) WITH (deduplicate_items='true'
- audit_has_changes
- mailable
- mailable_reason
- validation_state
|
|
0 min
< 0.1%
|
0 ms
|
120
postgres
|
UPDATE business_reconciliation
SET normalized_website = $1, website_hostname = $2, website_upper_domain = $3, website_ok = $4, website_issues = $5, norm_street_line = $6, norm_state = $7, norm_zip5 = $8, norm_address_key = $9, matched_existing_cyclops = $10, mailable_reason = $11, mailable_threshold_days = $12, last_seen_ts = now()
WHERE agb_doc_id = $13
RETURNING business_seq, agb_doc_id
Covered by index on (agb_doc_id)
Rows: 536247
Row progression: 536247, 1
Row estimates
- agb_doc_id (=): 1
Existing indexes
- business_seq PRIMARY
- agb_doc_id UNIQUE
- agb_id bpchar_pattern_ops NULLS FIRST) WITH (deduplicate_items='true'
- audit_has_changes
- mailable
- mailable_reason
- validation_state
|
|
0 min
< 0.1%
|
0 ms
|
112
postgres
|
UPDATE business_reconciliation
SET normalized_website = $1, website_hostname = $2, website_upper_domain = $3, website_ok = $4, website_issues = $5, agb_address_full = $6, agb_city = $7, agb_zip = $8, norm_street_line = $9, norm_zip5 = $10, matched_existing_cyclops = $11, mailable_reason = $12, mailable_threshold_days = $13, validation_state = $14, last_seen_ts = now()
WHERE agb_doc_id = $15
RETURNING business_seq, agb_doc_id
Covered by index on (agb_doc_id)
Rows: 536247
Row progression: 536247, 1
Row estimates
- agb_doc_id (=): 1
Existing indexes
- business_seq PRIMARY
- agb_doc_id UNIQUE
- agb_id bpchar_pattern_ops NULLS FIRST) WITH (deduplicate_items='true'
- audit_has_changes
- mailable
- mailable_reason
- validation_state
|
|
0 min
< 0.1%
|
18 ms
|
1
postgres
|
-- ============================================================
-- Cyclops Sync & Normalization - Postgres Schema (One-shot)
-- Schema: cyclops_stage
-- Notes:
-- - No enums (easy evolution). All timestamps in UTC.
-- - Includes lookup tables requested in the doc (public_id mapping, state codes).
-- ============================================================
CREATE EXTENSION IF NOT EXISTS "uuid-ossp"
|
|
0 min
< 0.1%
|
0 ms
|
1,271
postgres
|
SELECT c.relname FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = ANY (current_schemas($1)) AND c.relname = $2 AND c.relkind IN ($3,$4)
|
|
0 min
< 0.1%
|
0 ms
|
868
postgres
|
SET client_min_messages TO 'warning'
|
|
0 min
< 0.1%
|
0 ms
|
146
postgres
|
UPDATE business_reconciliation
SET agb_id = $1, normalized_website = $2, website_hostname = $3, website_upper_domain = $4, website_ok = $5, website_issues = $6, agb_address_full = $7, agb_state = $8, norm_state = $9, norm_zip5 = $10, matched_existing_cyclops = $11, mailable_reason = $12, mailable_threshold_days = $13, validation_state = $14, last_seen_ts = now()
WHERE agb_doc_id = $15
RETURNING business_seq, agb_doc_id
Covered by index on (agb_doc_id)
Rows: 536247
Row progression: 536247, 1
Row estimates
- agb_doc_id (=): 1
Existing indexes
- business_seq PRIMARY
- agb_doc_id UNIQUE
- agb_id bpchar_pattern_ops NULLS FIRST) WITH (deduplicate_items='true'
- audit_has_changes
- mailable
- mailable_reason
- validation_state
|
|
0 min
< 0.1%
|
0 ms
|
149
postgres
|
SELECT
roles.oid as id, roles.rolname as name,
roles.rolsuper as is_superuser,
CASE WHEN roles.rolsuper THEN $1 ELSE roles.rolcreaterole END as
can_create_role,
CASE WHEN roles.rolsuper THEN $2
ELSE roles.rolcreatedb END as can_create_db,
CASE WHEN $3=ANY(ARRAY(WITH RECURSIVE cte AS (
SELECT pg_roles.oid,pg_roles.rolname FROM pg_roles
WHERE pg_roles.oid = roles.oid
UNION ALL
SELECT m.roleid,pgr.rolname FROM cte cte_1
JOIN pg_auth_members m ON m.member = cte_1.oid
JOIN pg_roles pgr ON pgr.oid = m.roleid)
SELECT rolname FROM cte)) THEN $4
ELSE $5 END as can_signal_backend
FROM
pg_catalog.pg_roles as roles
WHERE
rolname = current_user
|