pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Joseph Shraibman <jks@selectacast.net>
To: pgsql-performance@postgresql.org
Subject: two seperate queries run faster than queries ORed together
Date: Thu, 18 Mar 2004 16:21:32 -0500
Message-ID: <c3d3sm$fdg$1@news.hub.org> (raw)

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.



view thread (8+ messages)  latest in thread

Message-ID: <c3d3sm$fdg$1@news.hub.org>
Permalink:  ../c3d3sm$fdg$1@news.hub.org/
Also on:    postgresql.org/message-id/c3d3sm$fdg$1@news.hub.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: jks@selectacast.net
  Subject: Re: two seperate queries run faster than queries ORed together
  In-Reply-To: <c3d3sm$fdg$1@news.hub.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox