Postgres Gruppe von Integer-Typ Spalten schneller als Charaktertyp Spalten?

Ich habe 4 Tische, die sind

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 ); 

Ich benutze die gleiche Abfrage für die Gruppe von ca. 2 Millionen datasätze dh SELECT content, sum(hits) as hits, sum(bytes) as bytes, appid from web_content_{3,4,5,6} GROUP BY content,appid; Ergebnis ist:

  - 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 

Hier web_content_6 Abfrage dauert etwa 5 Sekunden nur mit anderen drei Kombination zu vergleichen, mit dieser Statistik können wir sagen, dass Ganzzahl, Ganzzahl Kombination für Gruppe von ist viel schneller, aber Frage ist WARUM?

Ich habe EXPLAIN Ergebnisse auch, aber es gibt mir keine Erklärung für drastische Änderung zwischen web_content_4 und web_content_6 Abfrage.

hier ist es.

 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 

Solutions Collecting From Web of "Postgres Gruppe von Integer-Typ Spalten schneller als Charaktertyp Spalten?"

Gordon Linoff hat natürlich Recht. Das Überspielen auf die Festplatte ist teuer.

Wenn Sie den memory schonen können, können Sie PostgreSQL anweisen, mehr für das Sortieren zu verwenden. Ich habe eine Tabelle erstellt, sie mit randomen data gefüllt und sie vor dem Ausführen dieser Abfrage analysiert.

 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" 

Ich bekomme denselben Ausführungsplan wie du. In meinem Fall führt diese Abfrage eine externe Zusammenführung durch, die ungefähr 216 MB dataträger erfordert. Ich kann PostgreSQL anweisen, mehr memory für diese Abfrage zuzulassen, indem ich den Wert von work_mem festlege. (Die Einstellung von work_mem wirkt sich nur auf meine aktuelle Verbindung aus.)

 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" 

Jetzt verwendet PostgreSQL ein Hash-Aggregat und die Ausführungszeit sank um einen Faktor von 6, 30 Sekunden bis 5 Sekunden.


Ich habe web_content_6 nicht getestet, da das Ersetzen von Text durch Integer normalerweise einige Joins erfordert, um den Text wiederherzustellen. Ich bin mir also nicht sicher, ob wir Äpfel mit Äpfeln vergleichen würden.

Die performance dieser Aggregation wird von der Geschwindigkeit der sorting bestimmt. Bei gleichen Bedingungen benötigen größere data mehr time als kürzere data. Der "schnelle" Fall sortiert 74 MByte; das "langsame", 152Mbytes.

Dies würde für einen gewissen Unterschied in der performance verantwortlich sein, jedoch nicht für den 30-fachen Unterschied in den meisten Fällen. Der eine Fall, in dem Sie einen drastischen Unterschied sehen würden, ist, wenn die kleineren data in den memory passen und der größere nicht. Das Überspielen auf die Festplatte ist teuer.

Ein Verdacht besteht darin, dass die data bereits nach web_content_6(content, appid) sortiert oder fast sortiert sind. Dies könnte die für die sorting benötigte time verkürzen. Wenn Sie die tatsächliche time und die "Kosten" für jede der beiden Sorten vergleichen, sehen Sie, dass die "schnelle" Version relativ viel schneller läuft als erwartet (unter der Annahme, dass die Kosten vergleichbar sind).