Das in der Praxis am häufigsten verwendete Verfahren der Query-Performance-Optimierung ist vermutlich in allen RDBMS immer noch das der Umformulierung: da es in SQL für so ziemlich jede Fragestellung mehrere Lösungsmöglichkeiten gibt, kann man in aller Regel versuchen, eine andere syntaktische Variante zu wählen - und hoffen, dass der Optimizer dafür einen effektiveren Plan findet. Ich will dieses Verfahren nicht grundsätzlich negativ bewerten: es kann ohne Zweifel seine Erfolge vorweisen und es gibt RDBMS, bei denen die Instrumentierung so mangelhaft ist, dass man mit ihrer Hilfe nicht allzu viele Hinweise auf die eigentlichen Probleme der Ausführung erhält. Die meisten RDBMS besitzen allerdings inzwischen eine gute oder sogar sehr gute Instrumentierung, die eine exaktere Analyse von Zugriffsproblemen gestattet.
Darüber hinaus sind die Optimizer der meisten RDBMS selbst dazu in der Lage, eine Query intern in eine semantisch äquivalente Form umzuwandeln, die sich leichter optimieren lässt. Eine relativ einfache Option im Rahmen solcher Transformationen ist das Predicate Pushing, das eine einschränkende Bedingung, die für eine View (bzw. Inline-View) angegeben wird, an die in der View enthaltenen Sub-Queries überträgt. Dazu zunächst ein Beispiel mit Oracle:
-- 12.1.0.1
drop table t1;
drop table t2;
create table t1
as
select rownum id, lpad('*', 50, '*') col1
from dual
connect by level <= 10000;
create table t2
as
select rownum id, lpad('*', 50, '*') col1
from dual
connect by level <= 10000;
create index t1_idx on t1(id);
create index t2_idx on t2(id);
select *
from (select *
from t1
union all
select *
from t2)
where id <= 10;
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 1100 | 3 (0)| 00:00:01 |
| 1 | VIEW | | 20 | 1100 | 3 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 10 | 550 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T1_IDX | 10 | | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 10 | 550 | 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T2_IDX | 10 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."ID"<=10)
6 - access("T2"."ID"<=10)
select * from t1 where id <= 10
union all
select * from t2 where id <= 10;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 1100 | 6 (50)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 10 | 550 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_IDX | 10 | | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 10 | 550 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T2_IDX | 10 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"<=10)
5 - access("ID"<=10)
Gesucht sind alle Datensätze mit einer id <= 10: in der ersten Query erfolgt diese Einschränkung für eine Inline-View, in der zweiten Query wird sie für beide angesprochenen Tabellen explizit angegeben. Der Blick auf den Ausführungsplan zeigt, dass die Einschränkung auch bei der Verwendung der Inline-View an die Basistabellen weitergeleitet wird: es ist also nicht so, dass die Klammerung dazu führen würde, dass erst die Menge aller Sätze aus t1 und t2 ermittelt werden müsste, ehe die Einschränkungen durchgeführt werden. Einen Unterschied gibt es allerdings zwischen den beiden Varianten: die Cost-Angabe für die Version mit der Inline-View ist nur halb so hoch, wie die für die beiden über UNION ALL verbundenen Queries. Außerdem ist für die Inline-View ein zusätzlicher Step VIEW enthalten, der aber keinen Einfluss auf den eigentlichen Zugriff haben sollte. Meiner Einschätzung nach ist die Kostenangabe für den zweiten Fall plausibler, da die Kosten der beiden Teiloperationen addiert werden sollten. Natürlich könnte sich ein solcher Unterschied im Rahmen einer komplexeren Query auf folgende Schritte auswirken.
Wie sieht der Fall nun für Postgres aus? Dazu ein entsprechendes Beispiel:
-- PostgreSQL 9.3.6
drop table t1;
drop table t2;
create table t1
as
with
basedata as (
select * from generate_series(1, 10000) id
)
select id
, lpad('*', 50, '*')::text col2
from basedata
;
-- manuelle Statistikerfassung
analyze t1;
create index t1_idx on t1(id);
create table t2
as
with
basedata as (
select * from generate_series(1, 10000) id
)
select id
, lpad('*', 50, '*')::text col2
from basedata
;
-- manuelle Statistikerfassung
analyze t2;
create index t2_idx on t2(id);
explain
select *
from (select *
from t1
union all
select *
from t2) t
where id <= 10;
+------------------------------------------------------------------------+
| QUERY PLAN |
+------------------------------------------------------------------------+
| Append (cost=0.29..16.88 rows=18 width=55) |
| -> Index Scan using t1_idx on t1 (cost=0.29..8.44 rows=9 width=55) |
| Index Cond: (id <= 10) |
| -> Index Scan using t2_idx on t2 (cost=0.29..8.44 rows=9 width=55) |
| Index Cond: (id <= 10) |
+------------------------------------------------------------------------+
explain
select * from t1 where id <= 10
union all
select * from t2 where id <= 10;
+------------------------------------------------------------------------+
| QUERY PLAN |
+------------------------------------------------------------------------+
| Append (cost=0.29..17.06 rows=18 width=55) |
| -> Index Scan using t1_idx on t1 (cost=0.29..8.44 rows=9 width=55) |
| Index Cond: (id <= 10) |
| -> Index Scan using t2_idx on t2 (cost=0.29..8.44 rows=9 width=55) |
| Index Cond: (id <= 10) |
+------------------------------------------------------------------------+
Postgres kommt somit für beide Varianten exakt zum gleichen Plan.
Nun ist das Predicate Pushing für den Fall einer Inline-View mit über UNION ALL verknüpften Sub-Queries natürlich eher trivial, weil es auf der Hand liegt, dass alle Teiloperationen komplett getrennt behandelt werden können, da das Gesamtergebnis als Summe der Teilergebnisse definiert ist. Aber die Optimizer der großen RDBMS beherrschen noch sehr viele andere Tricks, die dafür sorgen können, dass die tatsächlich ausgeführte Operation deutlich anders aussieht, als das, was das abgesetzte SQL erwarten ließe. Für Oracle kann man die Evaluierung der Transformationen und ihr Ergebnis mit Hilfe eines CBO-Trace-Files (Event 10053) untersuchen - und für postgres könnte man sich den Optimizer-Code anschauen bzw. debuggen. In vielen Fällen genügt aber eine Blick auf den Ausführungsplan, um zu erkennen, was intern geschehen sein muss.
Dass es trotz dieser Transformationen immer noch sehr viele Fälle gibt, in denen sich relativ simple Änderungen in der Formulierung von SQL-Queries massiv auf den Ausführungsplan auswirken, steht auf einem anderen Blatt.
Keine Kommentare:
Kommentar veröffentlichen