Thread

1. Re: [HACKERS] Slow - grindingly slow - query

   Tom Lane <tgl@sss.pgh.pa.us> — 1999-11-13T04:30:55Z

   Brian Hirt <bhirt@mobygames.com> writes:
   > On Fri, Nov 12, 1999 at 09:58:14AM -0500, Tom Lane wrote:
   >> If it's bugging you enough to go fix it now, contributions are always
   >> welcome ;-)
   
   > Okay, what would be the correct approach to solving the problem, 
   > and where would be a good place to start?  I'v only been on this list
   > for a few weeks, so I'm missed discussion on the approach to solving 
   > this problem.  Should this change be localized to just the planner? 
   > Should the rewrite system be creating a different query tree?  Will both 
   > need to be changed?  If a lot of work is being done to this part of 
   > the system, is now a bad time to try this work?
   
   Well, actually, figuring out how & where to do it is the trickiest part
   of the work.  Might not be the best project for a newbie backend-hacker
   to start with :-(.
   
   After a few moments' thought, it seems to me that this issue might be
   closely intertwined with the OUTER JOIN stuff that Thomas is working on
   and the querytree representation redesign that Jan and I have been
   muttering about (but not yet actually doing anything about).  We want
   to handle SELECT ... WHERE expr IN (SELECT ...) like a join, but the
   semantics aren't exactly the same as a conventional join, so it might
   be that the thing needs to be rewritten as a special join type.  In
   that case it'd fit right in with OUTER JOIN, I suspect.
   
   The Informix EXPLAIN results that Theo Kramer posted (a few messages
   back in this thread) are pretty interesting too.  If I'm reading that
   printout right, Informix is not any smarter than we are about choosing
   the scan types for the outer and inner queries; and yet they have a much
   faster runtime for the WHERE IN query.  I speculate that they are doing
   the physical matching of outer and inner tuples in a smarter way than we
   are --- perhaps they are doing one scan of the inner query and entering
   all the values into a hashtable that's then probed for each outer tuple.
   (As opposed to rescanning the inner query for each outer tuple, as we
   currently do.)  If that's the answer, then it could probably be
   implemented as a localized change: rewrite the SubPlan node executor to
   look more like the HashJoin node executor.  This isn't perfect --- it
   wouldn't pick up the possibility of a merge-style join --- but it would
   be better than what we have for a lot less work than the "full" solution.
   
   This is all shooting from the hip; I haven't spent time looking into it.
   Has anyone else got insights to offer?
   
   			regards, tom lane
   
   

2. Re: [HACKERS] Slow - grindingly slow - query

   Theo Kramer <theo@flame.co.za> — 1999-11-13T09:55:38Z

   Tom Lane wrote:
   
   > The Informix EXPLAIN results that Theo Kramer posted (a few messages
   > back in this thread) are pretty interesting too.  If I'm reading that
   > printout right, Informix is not any smarter than we are about choosing
   > the scan types for the outer and inner queries; and yet they have a much
   > faster runtime for the WHERE IN query.
   
   The informix EXPLAIN for the 'not in' query was when I did not have an
   index on registrationtype (the explain appends to file sqexplain.out so I
   missed it :(). Anyway here is the Informix EXPLAIN with the index on
   registrationtype.
   
   
   QUERY:
   ------
   select accountdetail.domain from accountdetail where
     accountdetail.domain not in (select accountmaster.domain from accountmaster)
   
   Estimated Cost: 4510
   Estimated # of Rows Returned: 58810
   
   1) informix.accounts: SEQUENTIAL SCAN
   
       Filters: (informix.accounts.domain != ALL <subquery> AND
   informix.accounts.registrationtype != 'N' )
   
       Subquery:
       ---------
       Estimated Cost: 12
       Estimated # of Rows Returned: 10
   
       1) informix.accounts: INDEX PATH
   
           (1) Index Keys: registrationtype
               Lower Index Filter: informix.accounts.registrationtype = 'N'
   
   
   The speed difference with or without the subquery index is neglible for
   Informix.
   --------
   Regards
   Theo