funciona - tipos de datos en postgresql

¿El grupo postgres por columnas de tipo entero es más rápido que las columnas de tipo de caracteres?

Question

Tengo 4 tablas que son

create table web_content_3 ( content integer, hits bigint, bytes bigint, appid varchar(32) );
create table web_content_4 ( content character varying (128 ), hits bigint, bytes bigint, appid varchar(32) );
create table web_content_5 ( content character varying (128 ), hits bigint, bytes bigint, appid integer );
create table web_content_6 ( content integer, hits bigint, bytes bigint, appid integer );

Estoy usando la misma consulta para el grupo por aproximadamente 2 millones de registros, es decir SELECT content, sum(hits) as hits, sum(bytes) as bytes, appid from web_content_{3,4,5,6} GROUP BY content,appid; El resultado es:

 - Table Name | Content | appid | Time Taken [In ms]
- ===========================================================
- web_content_3 | integer | Character | 27277.931
- web_content_4 | Character | Character | 151219.388
- web_content_5 | Character | integer | 127252.023
- web_content_6 | integer | integer | 5412.096

Aquí la consulta de web_content_6 tarda alrededor de 5 segundos y solo se compara con otras tres combinaciones, usando estas estadísticas podemos decir que la combinación entera y entera para group by es mucho más rápida pero Question is WHY?

También tengo EXPLAIN Results, pero me da una explicación del cambio drástico entre web_content_4 y web_content_6 query.

aquí está.

test=# EXPLAIN ANALYSE SELECT content, sum(hits) as hits, sum(bytes) as bytes, appid from web_content_4 GROUP BY content,appid;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=482173.36..507552.31 rows=17680 width=63) (actual time=138099.612..151565.655 rows=17680 loops=1)
-> Sort (cost=482173.36..487196.11 rows=2009100 width=63) (actual time=138099.202..149256.707 rows=2009100 loops=1)
Sort Key: content, appid
Sort Method: external merge Disk: 152488kB
-> Seq Scan on web_content_4 (cost=0.00..45218.00 rows=2009100 width=63) (actual time=0.010..349.144 rows=2009100 loops=1)
Total runtime: 151613.569 ms
(6 rows)
Time: 151614.106 ms
test=# EXPLAIN ANALYSE SELECT content, sum(hits) as hits, sum(bytes) as bytes, appid from web_content_6 GROUP BY content,appid;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=368814.36..394194.51 rows=17760 width=24) (actual time=3282.333..5840.953 rows=17760 loops=1)
-> Sort (cost=368814.36..373837.11 rows=2009100 width=24) (actual time=3282.176..3946.025 rows=2009100 loops=1)
Sort Key: content, appid
Sort Method: external merge Disk: 74632kB
-> Seq Scan on web_content_6 (cost=0.00..34864.00 rows=2009100 width=24) (actual time=0.011..297.235 rows=2009100 loops=1)
Total runtime: 6172.960 ms

Answer 1

El rendimiento de esta agregación va a ser impulsado por la velocidad del género. En igualdad de condiciones, los datos más grandes requerirán más tiempo que los datos más cortos. El caso "rápido" está ordenando 74Mbytes; el "lento", 152Mbytes.

Esto explicaría algunas diferencias en el rendimiento, pero no la diferencia de 30x en la mayoría de los casos. El único caso donde vería una diferencia drástica es cuando los datos más pequeños encajan en la memoria y el más grande no. Derramar en el disco es costoso.

Una sospecha es que los datos ya están ordenados, o casi ordenados, por web_content_6(content, appid) . Esto podría acortar el tiempo necesario para el género. Si compara el tiempo real y el "costo" para cada uno de los dos géneros, verá que la versión "rápida" se ejecuta relativamente mucho más rápido de lo esperado (suponiendo que los costos sean comparables).

Answer 2

Gordon Linoff tiene razón, por supuesto. Derramar en el disco es costoso.

Si puede conservar la memoria, puede decirle a PostgreSQL que use más para clasificar y demás. Construí una tabla, la llené con datos aleatorios y la analicé antes de ejecutar esta consulta.

EXPLAIN ANALYSE
SELECT content, sum(hits) as hits, sum(bytes) as bytes, appid
from web_content_4
GROUP BY content,appid;
"GroupAggregate (cost=364323.43..398360.86 rows=903791 width=96) (actual time=25059.086..29789.234 rows=1998067 loops=1)"
" -> Sort (cost=364323.43..369323.34 rows=1999961 width=96) (actual time=25057.540..27907.143 rows=2000000 loops=1)"
" Sort Key: content, appid"
" Sort Method: external merge Disk: 216016kB"
" -> Seq Scan on web_content_4 (cost=0.00..52472.61 rows=1999961 width=96) (actual time=0.010..475.187 rows=2000000 loops=1)"
"Total runtime: 30012.427 ms"

Obtengo el mismo plan de ejecución que tú. En mi caso, esta consulta realiza una clasificación de fusión externa que requiere aproximadamente 216 MB de disco. Puedo decir a PostgreSQL que permita más memoria para esta consulta estableciendo el valor de work_mem. (Establecer work_mem de esta manera afecta solo a mi conexión actual.)

set work_mem = ''250MB'';
EXPLAIN ANALYSE
SELECT content, sum(hits) as hits, sum(bytes) as bytes, appid
from web_content_4
GROUP BY content,appid;
"HashAggregate (cost=72472.22..81510.13 rows=903791 width=96) (actual time=3196.777..4505.290 rows=1998067 loops=1)"
" -> Seq Scan on web_content_4 (cost=0.00..52472.61 rows=1999961 width=96) (actual time=0.019..437.252 rows=2000000 loops=1)"
"Total runtime: 4726.401 ms"

Ahora PostgreSQL está usando un agregado hash, y el tiempo de ejecución se redujo en un factor de 6, 30 segundos a 5 segundos.

No probé web_content_6, porque reemplazar texto con enteros generalmente requerirá un par de uniones para recuperar el texto. Así que no estoy seguro de que comparemos manzanas con manzanas allí.