Using the same condition twice

I came across a quite interesting issue I don't really understand but
maybe Tom will know.
This happened rather accidentally.

I have a rather complex query which executes efficiently.

There is one interesting thing - let's have a look at the query:

SELECT t_struktur.id, t_text.code, COUNT(t_wert.wert) AS x
FROM t_struktur JOIN t_sportstruktur
ON (t_struktur.id = t_sportstruktur.strukturid),
t_text, t_master, t_strukturtyp,
t_master AS a JOIN t_struktur AS b
ON (a.slave_id = b.id) JOIN t_strukturtyp AS c
ON (b.typid = c.id),
t_wert JOIN t_werttyp
ON (t_werttyp.id = t_wert.werttypid)
WHERE t_struktur.id = t_text.suchid
AND t_text.sprache = 1
AND t_text.texttyp IS NULL
AND t_text.icode = 'struktur'

AND t_master.master_id IN (11, 6, 10, 9, 5, 3, 7, 8, 13)
AND t_master.slave_id = t_struktur.id
AND t_struktur.typid = t_strukturtyp.id
AND t_strukturtyp.kommentar = 'geoort'

AND a.master_id = t_struktur.id
AND c.sortierung = '60005'

AND t_sportstruktur.sportid IN (1, 2, 3, 4, 5)
AND t_struktur.id = t_wert.strukturid
AND t_werttyp.id = t_wert.werttypid
AND t_werttyp.anzeige IN (40550, 40555, 40525,
41070, 41073, 41075, 41077, 41080,
40745, 40750, 40775, 40735, 40780,
40785, 40760, 40710, 41110, 41115,
41090, 41120, 40265, 41085, 41030,
41570, 41550)
AND (t_wert.wert > '0' OR t_wert.wert = 't')
GROUP BY t_struktur.id, t_text.code
ORDER BY x DESC;

On my good old P166 it takes

root(at)actionscouts:/tmp# time psql action < c.sql
id | code | x
-----+------------+----
301 | Schladming | 16
204 | Kitzbühel | 8
(2 rows)

real 0m1.475s
user 0m0.050s
sys 0m0.010s

It takes around 5 seconds to execute the query without explicit joins
(brief comment to the discussion we had yesterday).

As you can see the query is redundant:

t_wert JOIN t_werttyp
ON (t_werttyp.id = t_wert.werttypid)

I also use:

AND t_werttyp.id = t_wert.werttypid

I have done with join twice since I have forgotten to remove the line
below when tweaking the stuff.

However, when I remove this AND the query is logically the same but ...

root(at)actionscouts:/tmp# time psql action < c.sql
id | code | x
-----+------------+----
301 | Schladming | 16
204 | Kitzbühel | 8
(2 rows)

real 0m2.280s
user 0m0.060s
sys 0m0.010s

It is 50% slower ...
Does anybody have an idea why?

Here are the execution plans - the first one uses the redundant query;
the second one does not use the AND in the WHERE clause.

root(at)actionscouts:/tmp# time psql action < c.sql
NOTICE: QUERY PLAN:

Sort (cost=425.34..425.34 rows=1 width=132)
-> Aggregate (cost=425.32..425.33 rows=1 width=132)
-> Group (cost=425.32..425.33 rows=1 width=132)
-> Sort (cost=425.32..425.32 rows=1 width=132)
-> Nested Loop (cost=240.47..425.31 rows=1 width=132)
-> Nested Loop (cost=240.47..415.76 rows=1
width=124)
-> Hash Join (cost=240.47..399.06
rows=1 width=101)
-> Nested Loop
(cost=0.00..154.76 rows=765 width=29)
-> Seq Scan on t_werttyp
(cost=0.00..14.69 rows=23 width=8)
-> Index Scan using
idx_wert_werttypid on t_wert (cost=0.00..5.98 rows=1 width=21)
-> Hash (cost=240.47..240.47
rows=1 width=72)
-> Hash Join
(cost=114.57..240.47 rows=1 width=72)
-> Hash Join
(cost=22.45..148.23 rows=24 width=40)
-> Hash Join
(cost=18.82..128.85 rows=3091 width=32)
-> Seq
Scan on t_master a (cost=0.00..55.59 rows=3159 width=16)
-> Hash
(cost=16.66..16.66 rows=866 width=16)
->
Seq Scan on t_struktur b (cost=0.00..16.66 rows=866 width=16)
-> Hash
(cost=3.62..3.62 rows=1 width=8)
-> Seq
Scan on t_strukturtyp c (cost=0.00..3.62 rows=1 width=8)
-> Hash
(cost=92.11..92.11 rows=3 width=32)
-> Hash Join
(cost=41.12..92.11 rows=3 width=32)
-> Hash
Join (cost=37.49..86.40 rows=273 width=24)
->
Seq Scan on t_sportstruktur (cost=0.00..44.13 rows=273 width=8)
->
Hash (cost=16.66..16.66 rows=866 width=16)

-> Seq Scan on t_struktur (cost=0.00..16.66 rows=866 width=16)
-> Hash
(cost=3.62..3.62 rows=1 width=8)
->
Seq Scan on t_strukturtyp (cost=0.00..3.62 rows=1 width=8)
-> Index Scan using idx_text_suchid on
t_text (cost=0.00..16.68 rows=1 width=23)
-> Index Scan using idx_master_slaveid on
t_master (cost=0.00..9.54 rows=1 width=8)

EXPLAIN

real 0m0.616s
user 0m0.050s
sys 0m0.010s

oot(at)actionscouts:/tmp# time psql action < c.sql
NOTICE: QUERY PLAN:

Sort (cost=824.56..824.56 rows=1 width=132)
-> Aggregate (cost=824.55..824.55 rows=1 width=132)
-> Group (cost=824.55..824.55 rows=1 width=132)
-> Sort (cost=824.55..824.55 rows=1 width=132)
-> Nested Loop (cost=255.22..824.54 rows=1 width=132)
-> Nested Loop (cost=255.22..814.98 rows=1
width=124)
-> Hash Join (cost=255.22..798.28
rows=1 width=101)
-> Hash Join (cost=14.75..553.98
rows=765 width=29)
-> Seq Scan on t_wert
(cost=0.00..501.03 rows=5729 width=21)
-> Hash (cost=14.69..14.69
rows=23 width=8)
-> Seq Scan on
t_werttyp (cost=0.00..14.69 rows=23 width=8)
-> Hash (cost=240.47..240.47
rows=1 width=72)
-> Hash Join
(cost=114.57..240.47 rows=1 width=72)
-> Hash Join
(cost=22.45..148.23 rows=24 width=40)
-> Hash Join
(cost=18.82..128.85 rows=3091 width=32)
-> Seq
Scan on t_master a (cost=0.00..55.59 rows=3159 width=16)
-> Hash
(cost=16.66..16.66 rows=866 width=16)
->
Seq Scan on t_struktur b (cost=0.00..16.66 rows=866 width=16)
-> Hash
(cost=3.62..3.62 rows=1 width=8)
-> Seq
Scan on t_strukturtyp c (cost=0.00..3.62 rows=1 width=8)
-> Hash
(cost=92.11..92.11 rows=3 width=32)
-> Hash Join
(cost=41.12..92.11 rows=3 width=32)
-> Hash
Join (cost=37.49..86.40 rows=273 width=24)
->
Seq Scan on t_sportstruktur (cost=0.00..44.13 rows=273 width=8)
->
Hash (cost=16.66..16.66 rows=866 width=16)

-> Seq Scan on t_struktur (cost=0.00..16.66 rows=866 width=16)
-> Hash
(cost=3.62..3.62 rows=1 width=8)
->
Seq Scan on t_strukturtyp (cost=0.00..3.62 rows=1 width=8)
-> Index Scan using idx_text_suchid on
t_text (cost=0.00..16.68 rows=1 width=23)
-> Index Scan using idx_master_slaveid on
t_master (cost=0.00..9.54 rows=1 width=8)

EXPLAIN

real 0m0.659s
user 0m0.040s
sys 0m0.030s

The execution plans are slightly different.
Is it "normal"?

Also: My third PostgreSQL book is ready. It is in German - does anybody
of those PostgreSQL hackers out there want a free issue?

Hans

--
*Cybertec Geschwinde u Schoenig*
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/1/913 68 09; +43/664/233 90 75
www.postgresql.at <http://www.postgresql.at>, cluster.postgresql.at
<http://cluster.postgresql.at>, www.cybertec.at
<http://www.cybertec.at>, kernel.cybertec.at <http://kernel.cybertec.at>