On Wed, Apr 23, 2008 at 10:57:04AM +0200, A. Kretschmer wrote: > am Wed, dem 23.04.2008, um 9:58:10 +0200 mailte A. Kretschmer folgendes: > > > Query A (two queries) > > > > > > select distinct moment.mid from moment,timecard where parent = 45 and > > > (pid=17 and timecard.mid = moment.mid) order by moment.mid; > > > select distinct moment.mid from moment,timecard where parent = 45 and > > > (pbar = 0) order by moment.mid; > > > > > > Query B (combining the two with OR) > > > > > > select distinct moment.mid from moment,timecard where parent = 45 and > > > ((pid=17 and timecard.mid = moment.mid) or (pbar = 0)) order by > > > moment.mid; > > Thanks to depesz on #postgresql (irc-channel): > > Query A, the second query: there are no join between the 2 tables. > Mistake?
No, I just wanted to show the time differences, I haven't used join
before. Now that you have adviced me to, I have tried your suggestion
to rewrite B as a union and it works good! Just as fast as the A Query!
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=4100.27..4101.39 rows=223 width=4) (actual time=120.963..121.124
rows=42 loops=1)
-> Sort (cost=4100.27..4100.83 rows=223 width=4) (actual
time=120.959..121.008 rows=43 loops=1)
Sort Key: mid
-> Append (cost=287.66..4091.57 rows=223 width=4) (actual
time=11.274..120.795 rows=43 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=287.66..290.70 rows=203
width=4) (actual time=11.270..11.604 rows=41 loops=1)
-> Unique (cost=287.66..288.67 rows=203 width=4) (actual
time=11.264..11.469 rows=41 loops=1)
-> Sort (cost=287.66..288.16 rows=203 width=4)
(actual time=11.260..11.331 rows=57 loops=1)
Sort Key: moment.mid
-> Hash Join (cost=60.98..279.88 rows=203
width=4) (actual time=2.563..11.136 rows=57 loops=1)
Hash Cond: ("outer".mid = "inner".mid)
-> Seq Scan on timecard
(cost=0.00..211.78 rows=1017 width=4) (actual time=0.032..7.156 rows=995
loops=1)
Filter: (pid = 17)
-> Hash (cost=59.88..59.88 rows=444
width=4) (actual time=2.329..2.329 rows=0 loops=1)
-> Seq Scan on moment
(cost=0.00..59.88 rows=444 width=4) (actual time=0.035..1.980 rows=199 loops=1)
Filter: (parent = 45)
-> Subquery Scan "*SELECT* 2" (cost=192.62..3800.87 rows=20
width=4) (actual time=0.583..109.073 rows=2 loops=1)
-> Unique (cost=192.62..3800.67 rows=20 width=4) (actual
time=0.578..109.061 rows=2 loops=1)
-> Nested Loop (cost=192.62..3417.57 rows=153240
width=4) (actual time=0.576..89.437 rows=15324 loops=1)
-> Index Scan using moment_mid_idx on moment
(cost=0.00..160.15 rows=20 width=4) (actual time=0.544..3.527 rows=2 loops=1)
Filter: ((parent = 45) AND (pbar = 0))
-> Materialize (cost=192.62..269.24
rows=7662 width=0) (actual time=0.014..21.930 rows=7662 loops=2)
-> Seq Scan on timecard
(cost=0.00..192.62 rows=7662 width=0) (actual time=0.005..14.560 rows=7662
loops=1)
Total runtime: 122.076 ms
(23 rows)
--
Hans Ekbrand (http://sociologi.cjb.net) <[EMAIL PROTECTED]>
A. Because it breaks the logical sequence of discussion
Q. Why is top posting bad?
signature.asc
Description: Digital signature
