Skip site navigation (1) Skip section navigation (2)

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

From: Hans Ekbrand <hans(dot)ekbrand(at)sociology(dot)gu(dot)se>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: mysterious difference in speed when combining two queries with OR
Date: 2008-04-23 09:22:09
Message-ID: 20080423092205.GI11886@amin (view raw or flat)
Thread:
Lists: 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) 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?

In response to

Responses

pgsql-performance by date

Next:From: Theo KramerDate: 2008-04-23 11:00:07
Subject: Re: mysterious difference in speed when combining two queries with OR
Previous:From: hansDate: 2008-04-23 09:06:08
Subject: Re: mysterious difference in speed when combining twoqueries with OR

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group