pgcrypto - La consulta de PostgreSQL se ejecuta más rápido con la exploración de índice, pero el motor elige la combinación hash

postgresql 11 download (4)

Ejecuté el código de testbed de sayap (¡Gracias!), Con las siguientes modificaciones:

el código se ejecuta cuatro veces con random_page_cost establecido en 8,4,2,1; en ese orden. (el cpc = 8 está destinado a cebar el disco-búfer-caché)
La prueba se repite con una fracción reducida (1 / 2,1 / 4,1 / 8) de los atacantes (respectivamente: 3K, 1K5,750 y 375 hardhitters), el resto de los registros se mantiene sin cambios.
Estas pruebas 4 * 4 se repiten con una configuración más baja (64 K, el mínimo) para work_mem.

Después de esta carrera, hice la misma carrera, pero aumenté diez veces la escala: con 1M5 de registros (30K de bateadores duros)

Actualmente, estoy realizando la misma prueba con un aumento de cien veces, pero la inicialización es bastante lenta ...

Resultados Las entradas en las celdas son el tiempo total en mseg más una cadena que denota el plan de consulta elegido. (solo un puñado de planes ocurren)

Conclusión preliminar:

"el conjunto de trabajo" para la consulta original es demasiado pequeño: todo encaja en el núcleo, lo que hace que el costo de las capturas de página sea sobreestimado. La configuración de RPC en 2 (o 1) "resuelve" este problema, pero una vez que la consulta se amplía, los costos de página se vuelven dominantes y RPC = 4 se vuelve comparable o incluso mejor.
Establecer work_mem en un valor más bajo es otra forma de hacer que el optimizador cambie a los escaneos de índice (en lugar de hash + escaneo de mapa de bits). Las diferencias que encontré son más pequeñas de lo que Sayap informó. ¿Tal vez tengo un efecto más efectivo de caché o se olvidó de cebar el caché?
Se sabe que el optimizador tiene problemas con las distribuciones "sesgadas" (y las distribuciones multidimensionales "torcidas" o "en punta"). Las pruebas de prueba con 1/4 y 1/8 de los endurecedores iniciales 3K / 150K muestran que este efecto desaparece una vez que el "pico" " se aplana.
Algo sucede en el límite del 2%: los planes gererales 3000/150000 son diferentes (peores) que los que tienen <2% de duros. ¿Podría ser esta la granularidad de los histogramas?

La consulta:

SELECT "replays_game".* FROM "replays_game" INNER JOIN "replays_playeringame" ON "replays_game"."id" = "replays_playeringame"."game_id" WHERE "replays_playeringame"."player_id" = 50027

Si configuro SET enable_seqscan = off , entonces hace lo más rápido, que es:

QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.00..27349.80 rows=3395 width=72) (actual time=28.726..65.056 rows=3398 loops=1) -> Index Scan using replays_playeringame_player_id on replays_playeringame (cost=0.00..8934.43 rows=3395 width=4) (actual time=0.019..2.412 rows=3398 loops=1) Index Cond: (player_id = 50027) -> Index Scan using replays_game_pkey on replays_game (cost=0.00..5.41 rows=1 width=72) (actual time=0.017..0.017 rows=1 loops=3398) Index Cond: (id = replays_playeringame.game_id) Total runtime: 65.437 ms

Pero sin el temido enable_seqscan, elige hacer algo más lento:

QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------- Hash Join (cost=7330.18..18145.24 rows=3395 width=72) (actual time=92.380..535.422 rows=3398 loops=1) Hash Cond: (replays_playeringame.game_id = replays_game.id) -> Index Scan using replays_playeringame_player_id on replays_playeringame (cost=0.00..8934.43 rows=3395 width=4) (actual time=0.020..2.899 rows=3398 loops=1) Index Cond: (player_id = 50027) -> Hash (cost=3668.08..3668.08 rows=151208 width=72) (actual time=90.842..90.842 rows=151208 loops=1) Buckets: 1024 Batches: 32 (originally 16) Memory Usage: 1025kB -> Seq Scan on replays_game (cost=0.00..3668.08 rows=151208 width=72) (actual time=0.020..29.061 rows=151208 loops=1) Total runtime: 535.821 ms

Aquí están los índices relevantes:

Entonces, mi pregunta es, ¿qué estoy haciendo mal de que Postgres esté estimando mal los costos relativos de las dos formas de unirse? Veo en las estimaciones de costos que cree que la combinación de hash será más rápida. Y su estimación del costo de la unión de índice está fuera de lugar por un factor de 500.

¿Cómo puedo dar a Postgres más de una pista? VACUUM ANALYZE un VACUUM ANALYZE inmediatamente antes de ejecutar todo lo anterior.

Curiosamente, si ejecuto esta consulta para un jugador con un número menor de juegos, Postgres elige hacer el escaneo de índice + bucle anidado. Entonces, algo sobre el gran número de juegos hace cosquillas a este comportamiento no deseado donde el costo estimado relativo no coincide con el costo estimado real.

Finalmente, ¿debería estar usando Postgres? No deseo convertirme en un experto en el ajuste de bases de datos, por lo que estoy buscando una base de datos que funcione razonablemente bien con un nivel de atención de desarrollador consciente, a diferencia de un DBA dedicado. Me temo que si me quedo con Postgres tendré un flujo constante de temas como este que me obligarán a convertirme en un experto en Postgres, y tal vez otro DB sea más indulgente con un enfoque más informal.

Un experto de Postgres (RhodiumToad) revisó la configuración de mi base de datos completa ( http://pastebin.com/77QuiQSp ) y recomendó set cpu_tuple_cost = 0.1 . Eso le dio una aceleración dramática: http://pastebin.com/nTHvSHVd

Alternativamente, el cambio a MySQL también solucionó el problema bastante bien. Tengo una instalación predeterminada de MySQL y Postgres en mi caja de OS X, y MySQL es 2 veces más rápido, comparando consultas que se "calientan" al ejecutar repetidamente la consulta. En las consultas "frías", es decir, la primera vez que se ejecuta una consulta determinada, MySQL es de 5 a 150 veces más rápido. El rendimiento de las consultas en frío es bastante importante para mi aplicación particular.

La gran pregunta, en lo que a mí respecta, es aún sobresaliente: ¿Postgres requerirá más trampa y configuración para funcionar bien que MySQL? Por ejemplo, considere que ninguna de las sugerencias ofrecidas por los comentaristas aquí funcionó.

Esta es una publicación antigua, pero es bastante útil que acabo de encontrar un problema similar.

Aquí está mi descubrimiento hasta ahora. Dado que hay 151208 filas en replays_game , el costo promedio de golpear un elemento es aproximadamente log(151208)=12 . Debido a que hay 3395 registros en replays_playeringame después del filtrado, el costo promedio es 12*3395 , que es bastante alto. Además, el planificador sobreestimó el costo de la página: asume que todas las filas se distribuyen aleatoriamente, mientras que no lo está. Si eso fuera cierto, un escaneo secuencial sería mucho mejor. Básicamente, el plan de consulta está tratando de evitar los peores escenarios.

El problema de @dsjoerg es que no hay índice en replays_playeringame(game_id) . La exploración de índice siempre se usaría si hay un índice en replays_playeringame(game_id) : el costo de la exploración de índice se convertiría en 3395+12 (o algo parecido a eso).

@Neil sugirió tener un índice en (player_id, game_id) , que está cerca pero no es exacto. El índice correcto para tener es (game_id) o (game_id, player_id) .

Mi conjetura es que está utilizando el random_page_cost = 4 predeterminado, que es demasiado alto, lo que hace que el escaneo del índice sea demasiado costoso.

Intento reconstruir las 2 tablas con este script:

CREATE TABLE replays_game ( id integer NOT NULL, PRIMARY KEY (id) ); CREATE TABLE replays_playeringame ( player_id integer NOT NULL, game_id integer NOT NULL, PRIMARY KEY (player_id, game_id), CONSTRAINT replays_playeringame_game_fkey FOREIGN KEY (game_id) REFERENCES replays_game (id) ); CREATE INDEX ix_replays_playeringame_game_id ON replays_playeringame (game_id); -- 150k games INSERT INTO replays_game SELECT generate_series(1, 150000); -- ~150k players, ~2 games each INSERT INTO replays_playeringame select trunc(random() * 149999 + 1), generate_series(1, 150000); INSERT INTO replays_playeringame SELECT * FROM ( SELECT trunc(random() * 149999 + 1) as player_id, generate_series(1, 150000) as game_id ) AS t WHERE NOT EXISTS ( SELECT 1 FROM replays_playeringame WHERE t.player_id = replays_playeringame.player_id AND t.game_id = replays_playeringame.game_id ) ; -- the heavy player with 3000 games INSERT INTO replays_playeringame select 999999, generate_series(1, 3000);

Con el valor por defecto de 4:

game=# set random_page_cost = 4; SET game=# explain analyse SELECT "replays_game".* FROM "replays_game" INNER JOIN "replays_playeringame" ON "replays_game"."id" = "replays_playeringame"."game_id" WHERE "replays_playeringame"."player_id" = 999999; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------- Hash Join (cost=1483.54..4802.54 rows=3000 width=4) (actual time=3.640..110.212 rows=3000 loops=1) Hash Cond: (replays_game.id = replays_playeringame.game_id) -> Seq Scan on replays_game (cost=0.00..2164.00 rows=150000 width=4) (actual time=0.012..34.261 rows=150000 loops=1) -> Hash (cost=1446.04..1446.04 rows=3000 width=4) (actual time=3.598..3.598 rows=3000 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 106kB -> Bitmap Heap Scan on replays_playeringame (cost=67.54..1446.04 rows=3000 width=4) (actual time=0.586..2.041 rows=3000 loops=1) Recheck Cond: (player_id = 999999) -> Bitmap Index Scan on replays_playeringame_pkey (cost=0.00..66.79 rows=3000 width=0) (actual time=0.560..0.560 rows=3000 loops=1) Index Cond: (player_id = 999999) Total runtime: 110.621 ms

Después de bajarlo a 2:

game=# set random_page_cost = 2; SET game=# explain analyse SELECT "replays_game".* FROM "replays_game" INNER JOIN "replays_playeringame" ON "replays_game"."id" = "replays_playeringame"."game_id" WHERE "replays_playeringame"."player_id" = 999999; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=45.52..4444.86 rows=3000 width=4) (actual time=0.418..27.741 rows=3000 loops=1) -> Bitmap Heap Scan on replays_playeringame (cost=45.52..1424.02 rows=3000 width=4) (actual time=0.406..1.502 rows=3000 loops=1) Recheck Cond: (player_id = 999999) -> Bitmap Index Scan on replays_playeringame_pkey (cost=0.00..44.77 rows=3000 width=0) (actual time=0.388..0.388 rows=3000 loops=1) Index Cond: (player_id = 999999) -> Index Scan using replays_game_pkey on replays_game (cost=0.00..0.99 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=3000) Index Cond: (id = replays_playeringame.game_id) Total runtime: 28.542 ms (8 rows)

Si utilizara SSD, lo rebajaría aún más a 1.1.

En cuanto a tu última pregunta, realmente creo que deberías quedarte con postgresql. Tengo experiencia con postgresql y mssql, y necesito poner el triple de esfuerzo en este último para que se desempeñe la mitad del mismo.

Puede obtener un mejor plan de ejecución utilizando un índice de múltiples columnas (player_id, game_id) en la tabla replays_playeringame . Esto evita tener que usar una página al azar para buscar los ID de juego para el ID de jugador.