I am using Google Cloud SQL. I am using a g1-small
. I have a table that looks like this:
+-----------+-------------------+-------------+
| Column | Type | Modifiers |
|-----------+-------------------+-------------|
| id | character varying | not null |
| reportId | uuid | not null |
| value | jsonb | |
| isTouched | boolean | not null |
+-----------+-------------------+-------------+
Indexes:
"report_variable_pkey" PRIMARY KEY, btree (id, "reportId")
"report_variable_id_idx" btree (id)
"report_variable_isTouched_idx" btree ("isTouched")
"report_variable_reportId_idx" btree ("reportId")
"report_variable_value_idx" gin ((value::text) gin_trgm_ops)
Foreign-key constraints:
I have a query that looks like this:
INSERT INTO
report_variable ("id",
"reportId",
"value",
"isTouched")
SELECT
*
FROM
UNNEST($1::"varchar"[], $2::"uuid"[], $3::"jsonb"[], $4::"bool"[])
ON
CONFLICT ("id",
"reportId") DO
UPDATE
SET
"value" = EXCLUDED."value",
"isTouched" = EXCLUDED."isTouched" RETURNING *
My table has ~1.6m rows in it. This query runs thousands of times per day. Occasionally (as in, every 100 executions?), it will take a VERY long time. Here is a graph showing latency:
Here is the data that comes from the Google Cloud SQL Query Insights page, for an average query:
Here is the data for one of the strange queries:
Some things I noticed:
- Why is
I/O Read Time
so much higher? - Why is
Shared Dirtied Blocks
,Shared Hit Blocks
,Shared Read Blocks
andShared Written Blocks
? Why are they so much higher?
How can I find out more information? Let me know if you need any more info. Thank you in advance.
alter index report_variable_value_idx set (fastupdate = off)