Re: mysterious difference in speed when combining two queries with OR

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) <hans(at)sociologi(dot)cjb(dot)net>
A. Because it breaks the logical sequence of discussion
Q. Why is top posting bad?