0
379
ViewsCloudSQL con PostgreSQL rendimiento muy lento
Quería migrar de BigQuery a CloudSQL para ahorrar costos. Mi problema es que CloudSQL con PostgreSQL es muy lento en comparación con BigQuery. Una consulta que tarda 1,5 segundos en BigQuery tarda casi 4,5 minutos (!) en CloudSQL con PostgreSQL.
Tengo CloudSQL con servidor PostgreSQL con las siguientes configuraciones:
Mi base de datos tiene una tabla principal con 16 millones de filas (alrededor de 14 GB en RAM).
Una consulta de ejemplo:
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 La tabla es una tabla de videos con columnas de categories como text[] . La condición de búsqueda aquí busca dónde hay una categoría que es como '%thriller%' y como '%crime%' exactamente dos veces
El EXPLAIN ANALYZE de esta consulta da este resultado (CSV): link . El EXPLAIN (BUFFERS) de esta consulta da este resultado (CSV): link .
Gráfico de información de consultas:
Perfil de memoria:
Referencia de BigQuery para la misma consulta en el mismo tamaño de tabla:
Configuración del servidor: enlace .
Descripción de la tabla: enlace .
Mi objetivo es tener Cloud SQL con la misma velocidad de consulta que Big Query
3 answers
Answer question0
La consulta inicial parece demasiado complicada. Podría reescribirse como:
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
Para cualquiera que venga aquí y se pregunte cómo ajustar su máquina postgres en cloud sql, lo llaman banderas y puede hacerlo desde la interfaz de usuario, aunque no todas las opciones de configuración se pueden editar.
0
Creo que necesita usar una búsqueda de texto completo y el índice GIN especial. Los pasos:
Cree la función auxiliar para el índice:
CREATE OR REPLACE FUNCTION immutable_array_to_string(text[]) RETURNS text as $$ SELECT array_to_string($1, ','); $$ LANGUAGE sql IMMUTABLE;Crear índice en sí mismo:
CREATE INDEX videos_cats_fts_idx ON videos USING gin(to_tsvector('english', LOWER(immutable_array_to_string(categories))));
Use la siguiente consulta: SELECT title FROM videos WHERE (to_tsvector('english', immutable_array_to_string(categories)) @@ (to_tsquery('english', 'thriller & crime'))) limit 12 offset 0;
Tenga en cuenta que esta consulta tiene un significado diferente para 'crimen' y 'thriller'. No son solo subcadenas. Son tokens en frases en inglés. Pero parece que en realidad es mejor para su tarea. Además, este índice no es bueno para datos que cambian con frecuencia. Debería funcionar bien cuando tiene principalmente datos de solo lectura.
PD: esta respuesta está inspirada en la respuesta y los comentarios: https://stackoverflow.com/a/29640418/159923