Re: mysterious difference in speed when combining two queries with OR - Mailing list pgsql-performance

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)
orderby 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=41loops=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=57loops=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.156rows=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.980rows=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=2loops=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=15324loops=1) 
                                 ->  Index Scan using moment_mid_idx on moment  (cost=0.00..160.15 rows=20 width=4)
(actualtime=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=7662loops=2) 
                                       ->  Seq Scan on timecard  (cost=0.00..192.62 rows=7662 width=0) (actual
time=0.005..14.560rows=7662 loops=1) 
 Total runtime: 122.076 ms
(23 rows)

--
Hans Ekbrand (http://sociologi.cjb.net) <hans@sociologi.cjb.net>
A. Because it breaks the logical sequence of discussion
Q. Why is top posting bad?