Using the same condition twice - Mailing list pgsql-hackers
| From | Hans-Jürgen Schönig |
|---|---|
| Subject | Using the same condition twice |
| Date | |
| Msg-id | 3DB7ACF1.2060903@cybertec.at Whole thread Raw |
| In response to | Re: One 7.3 item left (Bruce Momjian <pgman@candle.pha.pa.us>) |
| Responses |
Re: Using the same condition twice
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-hackers |
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_masterAS 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
BYx DESC;
On my good old P166 it takes
root@actionscouts:/tmp# time psql action < c.sqlid | code | x
-----+------------+----301 | Schladming | 16204 | 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@actionscouts:/tmp# time psql action < c.sqlid | code | x
-----+------------+----301 | Schladming | 16204 | 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@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.33rows=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_suchidon
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@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.55rows=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_suchidon
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>
pgsql-hackers by date: