0
377
ViewsCloudSQL with PostgreSQL very slow performance
I wanted to migrate from BigQuery to CloudSQL to save cost. My problem is that CloudSQL with PostgreSQL is very very slow compare to BigQuery. A query that takes 1.5 seconds in BigQuery takes almost 4.5 minutes(!) on CloudSQL with PostgreSQL.
I have CloudSQL with PostgreSQL server with the following configs:
My database have a main table with 16M rows (around 14GB in RAM).
A example query:
EXPLAIN ANALYZE
SELECT
"title"
FROM
public.videos
WHERE
EXISTS (SELECT
*
FROM (
SELECT
COUNT(DISTINCT CASE WHEN LOWER(param) LIKE '%thriller%' THEN '0'
WHEN LOWER(param) LIKE '%crime%' THEN '1' END) AS count
FROM
UNNEST(categories) AS param
) alias
WHERE count = 2)
ORDER BY views DESC
LIMIT 12 OFFSET 0
The table is a videos tables with categories column as text[].
The search condition here looks where there is a categories which is like '%thriller%' and like '%crime%' exactly two times
The EXPLAIN ANALYZE of this query gives this output (CSV): link. The EXPLAIN (BUFFERS) of this query gives this output (CSV): link.
Query Insights graph:
Memory profile:
BigQuery reference for the same query on the same table size:
Server config: link.
Table describe: link.
My goal is to have Cloud SQL with the same query speed as Big Query
3 answers
Answer question0
The initial query looks overcomplicated. It could be rewritten as:
SELECT v."title"
FROM public.videos v
WHERE array_to_string(v.categories, '^') ILIKE ALL (ARRAY['%thriller%', '%crime%'])
ORDER BY views DESC
LIMIT 12 OFFSET 0;
0
For anyone coming here wondering how to tune their postgres machine on cloud sql they call it flags and you can do it from the UI although not all the config options are edit able.
0
I believe you need to use a full-text search and the special GIN index. The steps:
Create the helper function for index:
CREATE OR REPLACE FUNCTION immutable_array_to_string(text[]) RETURNS text as $$ SELECT array_to_string($1, ','); $$ LANGUAGE sql IMMUTABLE;Create index itself:
CREATE INDEX videos_cats_fts_idx ON videos USING gin(to_tsvector('english', LOWER(immutable_array_to_string(categories))));
Use the following query:
SELECT title FROM videos WHERE (to_tsvector('english', immutable_array_to_string(categories)) @@ (to_tsquery('english', 'thriller & crime'))) limit 12 offset 0;
Be aware that this query has a different meaning for 'crime' and 'thriller'. They are not just substrings. They are tokens in English phrases. But it looks that actually it is better for your task. Also, this index is not good for frequently changed data. It should work fine when you have mostly read-only data.
PS This answer is inspired by answer & comments: https://stackoverflow.com/a/29640418/159923