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

two seperate queries run faster than queries ORed together

From: Joseph Shraibman <jks(at)selectacast(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: two seperate queries run faster than queries ORed together
Date: 2004-03-18 21:21:32
Message-ID: c3d3sm$fdg$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-performance
explain
SELECT COUNT(u.ukey) FROM u, d WHERE d.ukey = u.ukey AND u.pkey = 260 
AND (u.status = 3 ) AND NOT u.boolfield ;
                                           QUERY PLAN
----------------------------------------------------------------------------------------------
  Aggregate  (cost=45707.84..45707.84 rows=1 width=4)
    ->  Nested Loop  (cost=0.00..45707.16 rows=273 width=4)
          ->  Seq Scan on usertable u  (cost=0.00..44774.97 rows=272 
width=4)
                Filter: ((pkey = 260) AND (status = 3) AND (NOT boolfield))
          ->  Index Scan using d_pkey on d  (cost=0.00..3.41 rows=1 width=4)
                Index Cond: (d.ukey = "outer".ukey)


explain
SELECT COUNT(u.ukey) FROM u, d WHERE d.ukey = u.ukey AND u.pkey = 260 
AND (d.status = 3 ) AND NOT u.boolfield ;

                                           QUERY PLAN
----------------------------------------------------------------------------------------------
  Aggregate  (cost=28271.38..28271.38 rows=1 width=4)
    ->  Nested Loop  (cost=0.00..28271.38 rows=1 width=4)
          ->  Seq Scan on d  (cost=0.00..28265.47 rows=1 width=4)
                Filter: (status = 3)
          ->  Index Scan using u_pkey on u  (cost=0.00..5.89 rows=1 width=4)
                Index Cond: (("outer".ukey = u.ukey) AND (u.pkey = 260))
                Filter: (NOT boolfield)


explain
SELECT COUNT(u.ukey) FROM u, d WHERE d.ukey = u.ukey AND u.pkey = 260 
AND (u.status = 3 OR d.status = 3 ) AND NOT u.boolfield ;


                                       QUERY PLAN
---------------------------------------------------------------------------------------
  Aggregate  (cost=128867.45..128867.45 rows=1 width=4)
    ->  Hash Join  (cost=32301.47..128866.77 rows=272 width=4)
          Hash Cond: ("outer".ukey = "inner".ukey)
          Join Filter: (("inner".status = 3) OR ("outer".status = 3))
          ->  Seq Scan on u  (cost=0.00..41215.97 rows=407824 width=6)
                Filter: ((pkey = 260) AND (NOT boolfield))
          ->  Hash  (cost=25682.98..25682.98 rows=1032998 width=6)
                ->  Seq Scan on d  (cost=0.00..25682.98 rows=1032998 
width=6)


... so what do I do?  It would be a real pain to rewrite this query to 
run twice and add the results up, especially since I don't always know 
beforehand when it will be faster based on different values to the query.

Responses

pgsql-performance by date

Next:From: Kevin BrownDate: 2004-03-19 00:41:12
Subject: Re: [HACKERS] fsync method checking
Previous:From: Kurt RoeckxDate: 2004-03-18 21:09:51
Subject: Re: [HACKERS] fsync method checking

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