Re: Unterabfragen zusammenfassen???

Hallo Thomas,

danke für die prompte Antwort, habe die Abfrafe inzwischen etwas „decrypten“
können:

SELECT CASE WHEN d.val = 0 THEN FALSE ELSE TRUE END AS result

FROM tbl_d AS d

JOIN tbl_a AS a ON d.tbl_a_id = a.id

JOIN view_a AS v ON a.tbl_b_id = v.tbl_b_id AND a.tbl_c_id = v.tbl_c_id
AND v.id_x = 564 AND v_ident_y = 'foo' AND v.ident_x = 'bar'

ORDER BY d.ts DESC LIMIT 1

Funktioniert auch und sieht finde ich nicht ganz so schrecklich aus. EXPLAIN
bestätig das ebenfalls, der Planer benötigt „nur noch“ 30 statt 52
Schritten, was natürlich die Gesamtkosten drückt, soweit so gut. Was ich
nicht ganz verstehe, die Variante mit den Unterabfragen läuft in der Praxis
wesentlich schneller, Laufzeiten:

subqueries | join

-----------+-------

183 ms | 251 ms

71 ms | 192 ms

71 ms | 171 ms

73 ms | 172 ms

71 ms | 171 ms

Den Rechner habe ich vorher einen Kaltstart machen lassen - um einen
frischen Cache zu haben. Kann mir jemand meinen Denkfehler erklären? Ich
verstehe nicht wieso der join verliert, obwohl er laut Ausgabe von EXPLAIN
deutlich schneller sein sollte.

Davon ab habe ich die Abfrage inzwischen ohne aufgeteilt, da es aktuell am
schnellsten ist, die beiden IDs vorher zu erfragen und auf das ganze joinen
bzw. unterabfragen zu verzichten, dennoch würde mich interessieren was ich
da falsch interpretiere.

Viele Grüße

Lars

PS: Die Ausgabe von EXPLAIN

subqueries

join

"Limit (cost=50.17..50.18 rows=1 width=16)"

" InitPlan 3 (returns $4)"

" -> Seq Scan on tbl_mds_values (cost=47.09..48.82 rows=1 width=8)"

" Filter: ((tbl_mds_id = $1) AND (tbl_ds_values_id = $3))"

" InitPlan 1 (returns $1)"

" -> Nested Loop (cost=0.29..23.54 rows=1 width=8)"

" Join Filter: (a.id = e.tbl_m_ds_descs_id)"

" -> Nested Loop (cost=0.29..22.23 rows=1 width=8)"

" -> Nested Loop (cost=0.15..14.05 rows=1
width=16)"

" -> Nested Loop (cost=0.00..5.88 rows=1
width=24)"

" Join Filter: (c.id =
a.tbl_ds_descs_id)"

" -> Nested Loop (cost=0.00..4.50
rows=1 width=24)"

" Join Filter: (c.id =
d.tbl_ds_descs_id)"

" -> Seq Scan on tbl_ds_descs c
(cost=0.00..1.18 rows=1 width=16)"

" Filter: ((ident)::text =
'moduleData'::text)"

" -> Seq Scan on tbl_ds_values d
(cost=0.00..3.31 rows=1 width=8)"

" Filter: ((ident)::text =
'provideInverterScan'::text)"

" -> Seq Scan on tbl_m_ds_descs a
(cost=0.00..1.26 rows=9 width=24)"

" Filter: (tbl_modules_id = 564)"

" -> Index Only Scan using tbl_modules_pk on
tbl_modules b (cost=0.15..8.17 rows=1 width=8)"

" Index Cond: (id = 564)"

" -> Index Only Scan using tbl_m_types_pk on
tbl_types f (cost=0.15..8.17 rows=1 width=8)"

" Index Cond: (id = c.tbl_types_id)"

" -> Seq Scan on tbl_mds e (cost=0.00..1.14 rows=14
width=16)"

" InitPlan 2 (returns $3)"

" -> Nested Loop (cost=0.29..23.54 rows=1 width=8)"

" Join Filter: (a_1.id = e_1.tbl_m_ds_descs_id)"

" -> Nested Loop (cost=0.29..22.23 rows=1 width=16)"

" -> Nested Loop (cost=0.15..14.05 rows=1
width=24)"

" -> Nested Loop (cost=0.00..5.88 rows=1
width=32)"

" Join Filter: (c_1.id =
a_1.tbl_ds_descs_id)"

" -> Nested Loop (cost=0.00..4.50
rows=1 width=32)"

" Join Filter: (c_1.id =
d_1.tbl_ds_descs_id)"

" -> Seq Scan on tbl_ds_descs c_1
(cost=0.00..1.18 rows=1 width=16)"

" Filter: ((ident)::text =
'moduleData'::text)"

" -> Seq Scan on tbl_ds_values d_1
(cost=0.00..3.31 rows=1 width=16)"

" Filter: ((ident)::text =
'provideInverterScan'::text)"

" -> Seq Scan on tbl_m_ds_descs a_1
(cost=0.00..1.26 rows=9 width=24)"

" Filter: (tbl_modules_id = 564)"

" -> Index Only Scan using tbl_modules_pk on
tbl_modules b_1 (cost=0.15..8.17 rows=1 width=8)"

" Index Cond: (id = 564)"

" -> Index Only Scan using tbl_m_types_pk on
tbl_types f_1 (cost=0.15..8.17 rows=1 width=8)"

" Index Cond: (id = c_1.tbl_types_id)"

" -> Seq Scan on tbl_mds e_1 (cost=0.00..1.14 rows=14
width=8)"

" -> Sort (cost=1.35..1.35 rows=1 width=16)"

" Sort Key: tbl_vs_cfg_long.ts"

" -> Seq Scan on tbl_vs_cfg_long (cost=0.00..1.34 rows=1 width=16)"

" Filter: (tbl_mds_values_id = $4)"

"Limit (cost=22.96..22.97 rows=1 width=16)"

" -> Sort (cost=22.96..22.97 rows=1 width=16)"

" Sort Key: a.ts"

" -> Nested Loop (cost=0.71..22.95 rows=1 width=16)"

" -> Nested Loop (cost=0.57..14.77 rows=1 width=24)"

" -> Nested Loop (cost=0.42..6.60 rows=1 width=32)"

" -> Nested Loop (cost=0.28..5.99 rows=1
width=24)"

" Join Filter: (c.id = d.tbl_ds_descs_id)"

" -> Nested Loop (cost=0.14..4.98 rows=2
width=48)"

" -> Nested Loop (cost=0.00..3.86
rows=1 width=40)"

" Join Filter: (a_1.id =
e.tbl_m_ds_descs_id)"

" -> Nested Loop
(cost=0.00..2.55 rows=1 width=40)"

" Join Filter:
(a_1.tbl_ds_descs_id = c.id)"

" -> Seq Scan on
tbl_ds_descs c (cost=0.00..1.18 rows=1 width=16)"

" Filter:
((ident)::text = 'moduleData'::text)"

" -> Seq Scan on
tbl_m_ds_descs a_1 (cost=0.00..1.26 rows=9 width=24)"

" Filter:
(tbl_modules_id = 564)"

" -> Seq Scan on tbl_mds e
(cost=0.00..1.14 rows=14 width=16)"

" -> Index Scan using
tbl_mds_values_cs_unique on tbl_mds_values b (cost=0.14..1.08 rows=4
width=24)"

" Index Cond: (tbl_mds_id =
e.id)"

" -> Index Scan using tbl_ds_values_pk on
tbl_ds_values d (cost=0.14..0.49 rows=1 width=16)"

" Index Cond: (id =
b.tbl_ds_values_id)"

" Filter: ((ident)::text =
'provideInverterScan'::text)"

" -> Index Scan using
tbl_vs_cfg_long_index_mds_values_id on tbl_vs_cfg_long a (cost=0.14..0.60
rows=1 width=24)"

" Index Cond: (tbl_mds_values_id = b.id)"

" -> Index Only Scan using tbl_modules_pk on tbl_modules
b_1 (cost=0.15..8.17 rows=1 width=8)"

" Index Cond: (id = 564)"

" -> Index Only Scan using tbl_m_types_pk on tbl_types f
(cost=0.15..8.17 rows=1 width=8)"

" Index Cond: (id = c.tbl_types_id)"

Von: Thomas Markus [mailto:t(dot)markus(at)proventis(dot)net]
Gesendet: Dienstag, 1. April 2014 12:40
An: Lars Grundei; pgsql-de-allgemein(at)postgresql(dot)org
Betreff: Re: [pgsql-de-allgemein] Unterabfragen zusammenfassen???

Moins,

bereite die Query doch mal auf ;) so kann die nicht funktionieren

Ich würde die auf joins umstellen (hoffe es ist richtig so)

SELECT
CASE WHEN d.val = 0 THEN FALSE ELSE TRUE END AS result
FROM
tbl_d d
join tbl_a a on d.tbl_a_id=a.id
join view_a v1 on d.tbl_d_id=v1.tbl_b_id and v1.id_x = 564 AND
v1.ident_y = 'foo' AND v1.ident_z = 'bar'
join view_a v2 on d.tbl_c_id=v1.tbl_c_id and v2.id_x = 564 AND
v2.ident_y = 'foo' AND v2.ident_z = 'bar'

ORDER BY d.ts DESC LIMIT 1

das view_a doppelt auftritt lässt sich wohl kaum vermeiden

Gruss
Thomas

Am 01.04.2014 12:14, schrieb Lars Grundei:

Hallo Zusammen,

Ich habe diese Abfrage, die auch soweit Funktioniert:

SELECT CASE WHEN val = 0 THEN FALSE ELSE TRUE END AS result

FROM tbl_d WHERE tbl_a_id = (SELECT id FROM tbl_a WHERE

tbl_b_id = (SELECT tbl_b_id FROM view_a WHERE id_x = 564 AND ident_y =
'foo' AND ident_z = 'bar') AND

tbl_c_id = (SELECT tbl_c_id FROM view_a WHERE id_x = 564 AND ident_y =
'foo' AND ident_z = 'bar')

)

ORDER BY ts DESC LIMIT 1

Was mich stört ist der Umstand, dass eine Unterabfrage bzw. Subquery zweimal
ausgeführt wird (wenn EXPLAIN nicht lügt), einmal wird halt tbl_b_id und
einmal tbl_c_id geliefert, gibt es irgendwie eine Möglichkeit, dass ich die
Zusammenfasse?

Vielen Dank

Lars