1. 31 Jul, 2007 1 commit
    • Tom Lane's avatar
      Fix a bug in the original implementation of redundant-join-clause removal: · ed5d55da
      Tom Lane authored
      clauses in which one side or the other references both sides of the join
      cannot be removed as redundant, because that expression won't have been
      constrained below the join.  Per report from Sergey Burladyan.
      
      CVS HEAD does not contain this bug due to EquivalenceClass rewrite, but it
      seems wise to include the regression test for it anyway.
      ed5d55da
  2. 22 May, 2007 1 commit
    • Tom Lane's avatar
      Repair planner bug introduced in 8.2 by ability to rearrange outer joins: · 11086f2f
      Tom Lane authored
      in cases where a sub-SELECT inserts a WHERE clause between two outer joins,
      that clause may prevent us from re-ordering the two outer joins.  The code
      was considering only the joins' own ON-conditions in determining reordering
      safety, which is not good enough.  Add a "delay_upper_joins" flag to
      OuterJoinInfo to flag that we have detected such a clause and higher-level
      outer joins shouldn't be permitted to commute with this one.  (This might
      seem overly coarse, but given the current rules for OJ reordering, it's
      sufficient AFAICT.)
      
      The failure case is actually pretty narrow: it needs a WHERE clause within
      the RHS of a left join that checks the RHS of a lower left join, but is not
      strict for that RHS (else we'd have simplified the lower join to a plain
      join).  Even then no failure will be manifest unless the planner chooses to
      rearrange the join order.
      
      Per bug report from Adam Terrey.
      11086f2f
  3. 16 Feb, 2007 1 commit
    • Tom Lane's avatar
      Restructure code that is responsible for ensuring that clauseless joins are · 6bef118b
      Tom Lane authored
      considered when it is necessary to do so because of a join-order restriction
      (that is, an outer-join or IN-subselect construct).  The former coding was a
      bit ad-hoc and inconsistent, and it missed some cases, as exposed by Mario
      Weilguni's recent bug report.  His specific problem was that an IN could be
      turned into a "clauseless" join due to constant-propagation removing the IN's
      joinclause, and if the IN's subselect involved more than one relation and
      there was more than one such IN linking to the same upper relation, then the
      only valid join orders involve "bushy" plans but we would fail to consider the
      specific paths needed to get there.  (See the example case added to the join
      regression test.)  On examining the code I wonder if there weren't some other
      problem cases too; in particular it seems that GEQO was defending against a
      different set of corner cases than the main planner was.  There was also an
      efficiency problem, in that when we did realize we needed a clauseless join
      because of an IN, we'd consider clauseless joins against every other relation
      whether this was sensible or not.  It seems a better design is to use the
      outer-join and in-clause lists as a backup heuristic, just as the rule of
      joining only where there are joinclauses is a heuristic: we'll join two
      relations if they have a usable joinclause *or* this might be necessary to
      satisfy an outer-join or IN-clause join order restriction.  I refactored the
      code to have just one place considering this instead of three, and made sure
      that it covered all the cases that any of them had been considering.
      
      Backpatch as far as 8.1 (which has only the IN-clause form of the disease).
      By rights 8.0 and 7.4 should have the bug too, but they accidentally fail
      to fail, because the joininfo structure used in those releases preserves some
      memory of there having once been a joinclause between the inner and outer
      sides of an IN, and so it leads the code in the right direction anyway.
      I'll be conservative and not touch them.
      6bef118b
  4. 17 Mar, 2006 1 commit
    • Tom Lane's avatar
      Fix bug introduced into mergejoin logic by performance improvement patch of · b3358e26
      Tom Lane authored
      2005-05-13.  When we find that a new inner tuple can't possibly match any
      outer tuple (because it contains a NULL), we can't immediately skip the
      tuple when we are in NEXTINNER state.  Doing so can lead to emitting
      multiple copies of the tuple in FillInner mode, because we may rescan the
      tuple after returning to a previous marked tuple.  Instead, proceed to
      NEXTOUTER state the same as we used to do.  After we've found that there's
      no need to return to the marked position, we can go to SKIPINNER_ADVANCE
      state instead of SKIP_TEST when the inner tuple is unmatchable; this
      preserves the performance improvement.  Per bug report from Bruce.
      I also made a couple of cosmetic code rearrangements and added a regression
      test for the problem.
      b3358e26
  5. 14 Mar, 2006 1 commit
    • Tom Lane's avatar
      Improve parser so that we can show an error cursor position for errors · 20ab467d
      Tom Lane authored
      during parse analysis, not only errors detected in the flex/bison stages.
      This is per my earlier proposal.  This commit includes all the basic
      infrastructure, but locations are only tracked and reported for errors
      involving column references, function calls, and operators.  More could
      be done later but this seems like a good set to start with.  I've also
      moved the ReportSyntaxErrorPosition logic out of psql and into libpq,
      which should make it available to more people --- even within psql this
      is an improvement because warnings weren't handled by ReportSyntaxErrorPosition.
      20ab467d
  6. 07 Mar, 2006 1 commit
  7. 22 Jul, 2005 1 commit
    • Tom Lane's avatar
      Fix compare_fuzzy_path_costs() to behave a bit more sanely. The original · 37c443ee
      Tom Lane authored
      coding would ignore startup cost differences of less than 1% of the
      estimated total cost; which was OK for normal planning but highly not OK
      if a very small LIMIT was applied afterwards, so that startup cost becomes
      the name of the game.  Instead, compare startup and total costs fuzzily
      but independently.  This changes the plan selected for two queries in the
      regression tests; adjust expected-output files for resulting changes in
      row order.  Per reports from Dawid Kuroczko and Sam Mason.
      37c443ee
  8. 07 Apr, 2005 1 commit
    • Neil Conway's avatar
      Add a "USING" clause to DELETE, which is equivalent to the FROM clause · f5ab0a14
      Neil Conway authored
      in UPDATE. We also now issue a NOTICE if a query has _any_ implicit
      range table entries -- in the past, we would only warn about implicit
      RTEs in SELECTs with at least one explicit RTE.
      
      As a result of the warning change, 25 of the regression tests had to
      be updated. I also took the opportunity to remove some bogus whitespace
      differences between some of the float4 and float8 variants. I believe
      I have correctly updated all the platform-specific variants, but let
      me know if that's not the case.
      
      Original patch for DELETE ... USING from Euler Taveira de Oliveira,
      reworked by Neil Conway.
      f5ab0a14
  9. 24 Mar, 2005 1 commit
    • Tom Lane's avatar
      Tweak planner to use a minimum size estimate of 10 pages for a · 208ec47b
      Tom Lane authored
      never-yet-vacuumed relation.  This restores the pre-8.0 behavior of
      avoiding seqscans during initial data loading, while still allowing
      reasonable optimization after a table has been vacuumed.  Several
      regression test cases revert to 7.4-like behavior, which is probably
      a good sign.  Per gripes from Keith Browne and others.
      208ec47b
  10. 03 Dec, 2004 1 commit
  11. 02 Dec, 2004 1 commit
    • Tom Lane's avatar
      Make some adjustments to reduce platform dependencies in plan selection. · 4e91824b
      Tom Lane authored
      In particular, there was a mathematical tie between the two possible
      nestloop-with-materialized-inner-scan plans for a join (ie, we computed
      the same cost with either input on the inside), resulting in a roundoff
      error driven choice, if the relations were both small enough to fit in
      sort_mem.  Add a small cost factor to ensure we prefer materializing the
      smaller input.  This changes several regression test plans, but with any
      luck we will now have more stability across platforms.
      4e91824b
  12. 01 Dec, 2004 1 commit
    • Tom Lane's avatar
      Change planner to use the current true disk file size as its estimate of · 5374d097
      Tom Lane authored
      a relation's number of blocks, rather than the possibly-obsolete value
      in pg_class.relpages.  Scale the value in pg_class.reltuples correspondingly
      to arrive at a hopefully more accurate number of rows.  When pg_class
      contains 0/0, estimate a tuple width from the column datatypes and divide
      that into current file size to estimate number of rows.  This improved
      methodology allows us to jettison the ancient hacks that put bogus default
      values into pg_class when a table is first created.  Also, per a suggestion
      from Simon, make VACUUM (but not VACUUM FULL or ANALYZE) adjust the value
      it puts into pg_class.reltuples to try to represent the mean tuple density
      instead of the minimal density that actually prevails just after VACUUM.
      These changes alter the plans selected for certain regression tests, so
      update the expected files accordingly.  (I removed join_1.out because
      it's not clear if it still applies; we can add back any variant versions
      as they are shown to be needed.)
      5374d097
  13. 25 Sep, 2003 1 commit
  14. 07 Aug, 2003 1 commit
  15. 19 Jul, 2003 1 commit
  16. 10 Feb, 2003 1 commit
  17. 25 Jan, 2003 1 commit
    • Tom Lane's avatar
      Allow the planner to collapse explicit inner JOINs together, rather than · 9f5f2124
      Tom Lane authored
      necessarily following the JOIN syntax to develop the query plan.  The old
      behavior is still available by setting GUC variable JOIN_COLLAPSE_LIMIT
      to 1.  Also create a GUC variable FROM_COLLAPSE_LIMIT to control the
      similar decision about when to collapse sub-SELECT lists into their parent
      lists.  (This behavior existed already, but the limit was always
      GEQO_THRESHOLD/2; now it's separately adjustable.)
      9f5f2124
  18. 30 Oct, 2002 2 commits
  19. 28 Oct, 2002 1 commit
  20. 28 Apr, 2002 1 commit
    • Tom Lane's avatar
      Second try at fixing join alias variables. Instead of attaching miscellaneous · 6c598869
      Tom Lane authored
      lists to join RTEs, attach a list of Vars and COALESCE expressions that will
      replace the join's alias variables during planning.  This simplifies
      flatten_join_alias_vars while still making it easy to fix up varno references
      when transforming the query tree.  Add regression test cases for interactions
      of subqueries with outer joins.
      6c598869
  21. 12 Mar, 2002 1 commit
    • Tom Lane's avatar
      Restructure representation of join alias variables. An explicit JOIN · 6eeb95f0
      Tom Lane authored
      now has an RTE of its own, and references to its outputs now are Vars
      referencing the JOIN RTE, rather than CASE-expressions.  This allows
      reverse-listing in ruleutils.c to use the correct alias easily, rather
      than painfully reverse-engineering the alias namespace as it used to do.
      Also, nested FULL JOINs work correctly, because the result of the inner
      joins are simple Vars that the planner can cope with.  This fixes a bug
      reported a couple times now, notably by Tatsuo on 18-Nov-01.  The alias
      Vars are expanded into COALESCE expressions where needed at the very end
      of planning, rather than during parsing.
      Also, beginnings of support for showing plan qualifier expressions in
      EXPLAIN.  There are probably still cases that need work.
      initdb forced due to change of stored-rule representation.
      6eeb95f0
  22. 14 Dec, 2000 1 commit
    • Tom Lane's avatar
      Planner speedup hacking. Avoid saving useless pathkeys, so that path · ea166f11
      Tom Lane authored
      comparison does not consider paths different when they differ only in
      uninteresting aspects of sort order.  (We had a special case of this
      consideration for indexscans already, but generalize it to apply to
      ordered join paths too.)  Be stricter about what is a canonical pathkey
      to allow faster pathkey comparison.  Cache canonical pathkeys and
      dispersion stats for left and right sides of a RestrictInfo's clause,
      to avoid repeated computation.  Total speedup will depend on number of
      tables in a query, but I see about 4x speedup of planning phase for
      a sample seven-table query.
      ea166f11
  23. 06 Nov, 2000 2 commits
  24. 12 Sep, 2000 1 commit
  25. 12 May, 2000 1 commit
  26. 15 Feb, 2000 1 commit
  27. 15 Jan, 2000 1 commit
  28. 09 Jan, 2000 1 commit
  29. 23 Feb, 1999 1 commit