Thread

1. No hash join across partitioned tables?

   Kris Jurka <books@ejurka.com> — 2009-04-16T23:09:37Z

   PG (8.3.7) doesn't seem to want to do a hash join across two partitioned 
   tables.  I have two partition hierarchies: impounds (with different 
   impound sources) and liens (with vehicle liens from different companies). 
   Trying to match those up gives:
   
   EXPLAIN SELECT COUNT(*)
   FROM impounds i
    	JOIN liens l ON (i.vin = l.vin);
   
     Aggregate  (cost=11164042.66..11164042.67 rows=1 width=0)
       ->  Nested Loop  (cost=0.27..3420012.94 rows=3097611886 width=0)
             Join Filter: ((i.vin)::text = (l.vin)::text)
             ->  Append  (cost=0.00..1072.77 rows=33577 width=21)
                   ->  Seq Scan on impounds i  (cost=0.00..11.40 rows=140 width=21)
                   ->  Seq Scan on impounds_s1 i  (cost=0.00..926.87 rows=29587 width=18)
                   ->  Seq Scan on impounds_s2 i  (cost=0.00..99.96 rows=3296 width=18)
                   ->  Seq Scan on impounds_s3 i  (cost=0.00..23.14 rows=414 width=18)
                   ->  Seq Scan on impounds_s4 i  (cost=0.00..11.40 rows=140 width=21)
             ->  Append  (cost=0.27..101.64 rows=15 width=21)
                   ->  Bitmap Heap Scan on liens l  (cost=0.27..5.60 rows=2 width=21)
                         Recheck Cond: ((l.vin)::text = (i.vin)::text)
                         ->  Bitmap Index Scan on liens_pk  (cost=0.00..0.27 rows=2 width=0)
                               Index Cond: ((l.vin)::text = (i.vin)::text)
                   ->  Index Scan using liens_s1_pk on liens_s1 l  (cost=0.00..7.02 rows=1 width=18)
                         Index Cond: ((l.vin)::text = (i.vin)::text)
                   ->  Index Scan using liens_s2_pk on liens_s2 l  (cost=0.00..3.47 rows=1 width=21)
                         Index Cond: ((l.vin)::text = (i.vin)::text)
                   ->  Index Scan using newliens_s3_pk on liens_s3 l  (cost=0.00..7.52 rows=1 width=18)
                         Index Cond: ((l.vin)::text = (i.vin)::text)
                   ->  Index Scan using newliens_s4_pk on liens_s4 l  (cost=0.00..7.67 rows=1 width=18)
                         Index Cond: ((l.vin)::text = (i.vin)::text)
                   ->  Index Scan using newliens_s5_pk on liens_s5 l  (cost=0.00..7.62 rows=1 width=18)
                         Index Cond: ((l.vin)::text = (i.vin)::text)
                   ->  Index Scan using newliens_s6_pk on liens_s6 l  (cost=0.00..7.61 rows=1 width=18)
                         Index Cond: ((l.vin)::text = (i.vin)::text)
                   ->  Index Scan using newliens_s7_pk on liens_s7 l  (cost=0.00..7.50 rows=1 width=18)
                         Index Cond: ((l.vin)::text = (i.vin)::text)
                   ->  Index Scan using newliens_s8_pk on liens_s8 l  (cost=0.00..7.36 rows=1 width=18)
                         Index Cond: ((l.vin)::text = (i.vin)::text)
                   ->  Index Scan using newliens_s9_pk on liens_s9 l  (cost=0.00..7.43 rows=1 width=18)
                         Index Cond: ((l.vin)::text = (i.vin)::text)
                   ->  Index Scan using newliens_s10_pk on liens_s10 l  (cost=0.00..7.79 rows=1 width=18)
                         Index Cond: ((l.vin)::text = (i.vin)::text)
                   ->  Index Scan using newliens_s11_pk on liens_s11 l  (cost=0.00..8.07 rows=1 width=18)
                         Index Cond: ((l.vin)::text = (i.vin)::text)
                   ->  Index Scan using newliens_s12_pk on liens_s12 l  (cost=0.00..8.45 rows=1 width=18)
                         Index Cond: ((l.vin)::text = (i.vin)::text)
                   ->  Index Scan using newliens_s13_pk on liens_s13 l  (cost=0.00..8.53 rows=1 width=18)
                         Index Cond: ((l.vin)::text = (i.vin)::text)
   
   
   This takes quite a while as it's got to do tons of index probes which 
   results it tons of random IO.  I killed this after five minutes of 
   running.
   
   But if I do:
   
   CREATE TABLE i1 AS SELECT * FROM impounds;
   CREATE TABLE l1 AS SELECT * FROM liens;
   
   I get a reasonable plan, which runs in about 15 seconds, from:
   
   EXPLAIN SELECT COUNT(*)
   FROM i1 i
            JOIN l1 l ON (i.vin = l.vin);
   
     Aggregate  (cost=749054.78..749054.79 rows=1 width=0)
       ->  Hash Join  (cost=1444.18..748971.43 rows=33338 width=0)
             Hash Cond: ((l.vin)::text = (i.vin)::text)
             ->  Seq Scan on l1 l  (cost=0.00..332068.96 rows=18449996 
   width=18)
             ->  Hash  (cost=1027.97..1027.97 rows=33297 width=18)
                   ->  Seq Scan on i1 i  (cost=0.00..1027.97 rows=33297 
   width=18)
   
   
   I've tried to force the hash join plan on the partitioned tables via:
   
   set enable_nestloop to off;
   
   This results in a merge join plan which needs to do a giant sort, again 
   killed after five minutes.
   
     Aggregate  (cost=58285765.20..58285765.21 rows=1 width=0)
       ->  Merge Join  (cost=4077389.31..50541735.48 rows=3097611886 width=0)
             Merge Cond: ((i.vin)::text = (l.vin)::text)
             ->  Sort  (cost=4286.45..4370.39 rows=33577 width=21)
                   Sort Key: i.vin
                   ->  Append  (cost=0.00..1072.77 rows=33577 width=21)
                         ->  Seq Scan on impounds i  (cost=0.00..11.40 rows=140 width=21)
                         ->  [Seq Scans on other partitions]
             ->  Materialize  (cost=4073102.86..4303737.81 rows=18450796 width=21)
                   ->  Sort  (cost=4073102.86..4119229.85 rows=18450796 width=21)
                         Sort Key: l.vin
                         ->  Append  (cost=0.00..332797.96 rows=18450796 width=21)
                               ->  Seq Scan on liens l  (cost=0.00..14.00 rows=400 width=21)
                               ->  [Seq Scans on other partitions]
   
   
   Disabling mergejoin pushes it back to a nestloop join.  Why can't it hash 
   join these two together?
   
   Kris Jurka
   
   

2. Re: No hash join across partitioned tables?

   Tom Lane <tgl@sss.pgh.pa.us> — 2009-04-16T23:12:11Z

   Kris Jurka <books@ejurka.com> writes:
   > PG (8.3.7) doesn't seem to want to do a hash join across two partitioned 
   > tables.
   
   Could we see the whole declaration of these tables?  (pg_dump -s output
   would be convenient)
   
   			regards, tom lane
   
   

3. Re: No hash join across partitioned tables?

   Kris Jurka <books@ejurka.com> — 2009-04-16T23:30:51Z

   
   On Thu, 16 Apr 2009, Tom Lane wrote:
   
   > Kris Jurka <books@ejurka.com> writes:
   >> PG (8.3.7) doesn't seem to want to do a hash join across two partitioned
   >> tables.
   >
   > Could we see the whole declaration of these tables?  (pg_dump -s output
   > would be convenient)
   >
   
   The attached table definition with no data wants to mergejoin first, but 
   after disabling mergejoin it does indeed do a hashjoin.
   
   Looking back at the cost estimates for the merge and nestloop joins, it 
   seems to be selecting the number of rows in the cartesian product * .005 
   while the number of output rows in this case is 2437 (cartesian product * 
   4e-9).  Perhaps the cost estimates for the real data are so high because 
   of this bogus row count that the fudge factor to disable mergejoin isn't 
   enough?
   
   Kris Jurka

4. Re: No hash join across partitioned tables?

   Kris Jurka <books@ejurka.com> — 2009-04-17T01:02:04Z

   
   On Thu, 16 Apr 2009, Kris Jurka wrote:
   
   > Perhaps the cost estimates for the real data are so high because of this 
   > bogus row count that the fudge factor to disable mergejoin isn't enough?
   >
   
   Indeed, I get these cost estimates on 8.4b1 with an increased 
   disable_cost value:
   
   nestloop:  11171206.18
   merge:     58377401.39
   hash:     116763544.76
   
   So the default disable_cost isn't enough to push it to use the hash join 
   plan and goes back to nestloop.  Since disable_cost hasn't been touched 
   since January 2000, perhaps it's time to bump that up to match today's 
   hardware and problem sizes?  This isn't even a particularly big problem, 
   it's joing 18M rows against 30k.
   
   The real problem is getting reasonable stats to pass through the partition 
   Append step, so it can make a reasonable estimate of the join output size.
   
   Kris Jurka
   
   
   

5. Re: No hash join across partitioned tables?

   Tom Lane <tgl@sss.pgh.pa.us> — 2009-04-17T15:02:34Z

   Kris Jurka <books@ejurka.com> writes:
   > So the default disable_cost isn't enough to push it to use the hash join 
   > plan and goes back to nestloop.  Since disable_cost hasn't been touched 
   > since January 2000, perhaps it's time to bump that up to match today's 
   > hardware and problem sizes?
   
   I think disable_cost was originally set at a time when costs were
   integers :-(.  Yeah, there's probably no reason not to throw another
   zero or two on it.
   
   Is there another issue here besides that one?  I think you were hoping
   that the hash join would be faster than the alternatives, but the cost
   estimate says it's a lot slower.  Is that actually the case?
   
   			regards, tom lane
   
   

6. Re: No hash join across partitioned tables?

   Kris Jurka <books@ejurka.com> — 2009-04-17T15:07:21Z

   Tom Lane wrote:
   
   > Is there another issue here besides that one?  I think you were hoping
   > that the hash join would be faster than the alternatives, but the cost
   > estimate says it's a lot slower.  Is that actually the case?
   > 
   
   The hash join takes less than twenty seconds, the other two joins I 
   killed after five minutes.  I can try to collect explain analyze results 
   later today if you'd like.
   
   Kris Jurka
   
   

7. Re: No hash join across partitioned tables?

   Tom Lane <tgl@sss.pgh.pa.us> — 2009-04-17T15:08:24Z

   Kris Jurka <books@ejurka.com> writes:
   > The hash join takes less than twenty seconds, the other two joins I 
   > killed after five minutes.  I can try to collect explain analyze results 
   > later today if you'd like.
   
   Please, unless the test case you already posted has similar behavior.
   
   			regards, tom lane
   
   

8. Re: No hash join across partitioned tables?

   Kris Jurka <books@ejurka.com> — 2009-04-17T17:05:32Z

   Tom Lane wrote:
   > Kris Jurka <books@ejurka.com> writes:
   >> The hash join takes less than twenty seconds, the other two joins I 
   >> killed after five minutes.  I can try to collect explain analyze results 
   >> later today if you'd like.
   > 
   
   Attached are the explain analyze results.  The analyze part hits the 
   hash join worst of all, so I've also included the timings without analyzing.
   
   Method       Time (ms)  Time w/Analyze (ms)
   nestloop     304853     319060
   merge        514517     683757
   hash          18957     143731
   
   Kris Jurka
   

9. Re: No hash join across partitioned tables?

   Tom Lane <tgl@sss.pgh.pa.us> — 2009-04-19T23:31:51Z

   Kris Jurka <books@ejurka.com> writes:
   > The real problem is getting reasonable stats to pass through the partition 
   > Append step, so it can make a reasonable estimate of the join output size.
   
   I dug around a bit and concluded that the lack of stats for the Append
   relation is indeed the main problem.  It's not so much the bad join size
   estimate (although that could hurt for cases where you need to join this
   result to another table).  Rather, it's that the planner is deliberately
   biased against picking hash joins in the absence of stats for the inner
   relation.  Per the comments for estimate_hash_bucketsize:
   
    * If no statistics are available, use a default estimate of 0.1.  This will
    * discourage use of a hash rather strongly if the inner relation is large,
    * which is what we want.  We do not want to hash unless we know that the
    * inner rel is well-dispersed (or the alternatives seem much worse).
   
   While we could back off the default a bit here, I think it'd be better
   to fix it by not punting on the stats-for-append-relations problem.
   That doesn't seem like material for 8.4 at this point, though.
   
   			regards, tom lane
   
   

10. Re: No hash join across partitioned tables?

    Bruce Momjian <bruce@momjian.us> — 2010-02-25T23:46:33Z

    Did this get addressed?
    
    ---------------------------------------------------------------------------
    
    Tom Lane wrote:
    > Kris Jurka <books@ejurka.com> writes:
    > > The real problem is getting reasonable stats to pass through the partition 
    > > Append step, so it can make a reasonable estimate of the join output size.
    > 
    > I dug around a bit and concluded that the lack of stats for the Append
    > relation is indeed the main problem.  It's not so much the bad join size
    > estimate (although that could hurt for cases where you need to join this
    > result to another table).  Rather, it's that the planner is deliberately
    > biased against picking hash joins in the absence of stats for the inner
    > relation.  Per the comments for estimate_hash_bucketsize:
    > 
    >  * If no statistics are available, use a default estimate of 0.1.  This will
    >  * discourage use of a hash rather strongly if the inner relation is large,
    >  * which is what we want.  We do not want to hash unless we know that the
    >  * inner rel is well-dispersed (or the alternatives seem much worse).
    > 
    > While we could back off the default a bit here, I think it'd be better
    > to fix it by not punting on the stats-for-append-relations problem.
    > That doesn't seem like material for 8.4 at this point, though.
    > 
    > 			regards, tom lane
    > 
    > -- 
    > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
    > To make changes to your subscription:
    > http://www.postgresql.org/mailpref/pgsql-performance
    
    -- 
      Bruce Momjian  <bruce@momjian.us>        http://momjian.us
      EnterpriseDB                             http://enterprisedb.com
      PG East:  http://www.enterprisedb.com/community/nav-pg-east-2010.do
      + If your life is a hard drive, Christ can be your backup. +
    
    

11. Re: No hash join across partitioned tables?

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-02-26T00:03:34Z

    Bruce Momjian <bruce@momjian.us> writes:
    > Did this get addressed?
    
    Partially.  There are stats now but autovacuum is not bright about
    when to update them.
    
    			regards, tom lane
    
    

12. Re: No hash join across partitioned tables?

    Robert Haas <robertmhaas@gmail.com> — 2010-03-02T16:16:51Z

    On Thu, Feb 25, 2010 at 7:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
    > Bruce Momjian <bruce@momjian.us> writes:
    >> Did this get addressed?
    >
    > Partially.  There are stats now but autovacuum is not bright about
    > when to update them.
    
    Is that something you're planning to fix for 9.0?  If not, we at least
    need to document what we intend for people to do about it.
    
    ...Robert
    
    

13. Re: No hash join across partitioned tables?

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-03-02T16:23:18Z

    Robert Haas <robertmhaas@gmail.com> writes:
    > On Thu, Feb 25, 2010 at 7:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
    >> Partially. There are stats now but autovacuum is not bright about
    >> when to update them.
    
    > Is that something you're planning to fix for 9.0?  If not, we at least
    > need to document what we intend for people to do about it.
    
    I want to look at it, but I'm not sure whether the fix will be small
    enough that we want to put it in during beta.
    
    			regards, tom lane
    
    

14. Re: No hash join across partitioned tables?

    Greg Jaskiewicz <gryzman@gmail.com> — 2010-03-02T16:27:14Z

    On Tue, Mar 2, 2010 at 4:23 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
    
    > Robert Haas <robertmhaas@gmail.com> writes:
    > > On Thu, Feb 25, 2010 at 7:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
    > >> Partially.  There are stats now but autovacuum is not bright about
    > >> when to update them.
    >
    > > Is that something you're planning to fix for 9.0?  If not, we at least
    > > need to document what we intend for people to do about it.
    >
    > I want to look at it, but I'm not sure whether the fix will be small
    > enough that we want to put it in during beta.
    >
    > I am pretty sure many people will appreciate it, even if it isn't going to
    be small.
    
    Is that stat collection across child tables any useful by it self ?
    
    -- 
    GJ
    

15. Re: No hash join across partitioned tables?

    Robert Haas <robertmhaas@gmail.com> — 2010-06-09T19:47:55Z

    On Tue, Mar 2, 2010 at 12:23 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
    > Robert Haas <robertmhaas@gmail.com> writes:
    >> On Thu, Feb 25, 2010 at 7:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
    >>> Partially.  There are stats now but autovacuum is not bright about
    >>> when to update them.
    >
    >> Is that something you're planning to fix for 9.0?  If not, we at least
    >> need to document what we intend for people to do about it.
    >
    > I want to look at it, but I'm not sure whether the fix will be small
    > enough that we want to put it in during beta.
    
    In going back through emails I had marked as possibly needing another
    look before 9.0 is released, I came across this issue again.  As I
    understand it, analyze (or analyse) now collects statistics for both
    the parent individually, and for the parent and its children together.
     However, as I further understand it, autovacuum won't actually fire
    off an analyze unless there's enough activity on the parent table
    considered individually to warrant it.  So if you have an empty parent
    and a bunch of children with data in it, your stats will still stink,
    unless you analyze by hand.
    
    Assuming my understanding of the problem is correct, we could:
    
    (a) fix it,
    (b) document that you should consider periodic manual analyze commands
    in this situation, or
    (c) do nothing.
    
    Thoughts?
    
    -- 
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise Postgres Company
    
    

16. Re: No hash join across partitioned tables?

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-06-09T20:11:25Z

    Robert Haas <robertmhaas@gmail.com> writes:
    > In going back through emails I had marked as possibly needing another
    > look before 9.0 is released, I came across this issue again.  As I
    > understand it, analyze (or analyse) now collects statistics for both
    > the parent individually, and for the parent and its children together.
    >  However, as I further understand it, autovacuum won't actually fire
    > off an analyze unless there's enough activity on the parent table
    > considered individually to warrant it.  So if you have an empty parent
    > and a bunch of children with data in it, your stats will still stink,
    > unless you analyze by hand.
    
    Check.
    
    > Assuming my understanding of the problem is correct, we could:
    
    > (a) fix it,
    > (b) document that you should consider periodic manual analyze commands
    > in this situation, or
    > (c) do nothing.
    
    > Thoughts?
    
    The objections to (a) are that it might result in excessive ANALYZE work
    if not done intelligently, and that we haven't got a patch ready anyway.
    I would have liked to get to this for 9.0 but I feel it's a bit late
    now.
    
    			regards, tom lane
    
    

17. Re: [PERFORM] No hash join across partitioned tables?

    Robert Haas <robertmhaas@gmail.com> — 2010-06-10T13:29:41Z

    (moving to -hackers)
    
    On Wed, Jun 9, 2010 at 4:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
    > Robert Haas <robertmhaas@gmail.com> writes:
    >> In going back through emails I had marked as possibly needing another
    >> look before 9.0 is released, I came across this issue again.  As I
    >> understand it, analyze (or analyse) now collects statistics for both
    >> the parent individually, and for the parent and its children together.
    >>  However, as I further understand it, autovacuum won't actually fire
    >> off an analyze unless there's enough activity on the parent table
    >> considered individually to warrant it.  So if you have an empty parent
    >> and a bunch of children with data in it, your stats will still stink,
    >> unless you analyze by hand.
    >
    > Check.
    >
    >> Assuming my understanding of the problem is correct, we could:
    >
    >> (a) fix it,
    >> (b) document that you should consider periodic manual analyze commands
    >> in this situation, or
    >> (c) do nothing.
    >
    >> Thoughts?
    >
    > The objections to (a) are that it might result in excessive ANALYZE work
    > if not done intelligently, and that we haven't got a patch ready anyway.
    > I would have liked to get to this for 9.0 but I feel it's a bit late
    > now.
    
    I guess I can't really disagree with that.  Should we try to document
    this in some way?
    
    -- 
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise Postgres Company
    
    

18. Error with GIT Repository

    Luxenberg, Scott I. <scott.luxenberg@noblis.org> — 2010-06-10T14:42:18Z

    Greetings all,
    
    I have been trying to create/run a build farm as part of a project I am
    working on. However, I have noticed the primary git repostitory,
    git.postgresql.org/git, does not seem to be working. Namely, whenever I
    try to clone the directory, I receive this error:
    
    Error: Unable to find 5e4933c31d3cd2750ee1793efe6eca43055fb273e under
    http://git.postgresql.org/git/postgresql.git
    Cannot obtain needed blob 5e4933c31d3cd2750ee1793efe6eca4305fb273e while
    processing commit c5609c66ce2ee4fdb180be95721252b47f90499
    Error: fetch failed.
    
    I thought it would be prudent to notify the list so someone could
    possibly check into this.
    
    Thanks!
    
    Scott Luxenberg
    
    

19. Re: Error with GIT Repository

    Andrew Dunstan <andrew@dunslane.net> — 2010-06-10T15:26:59Z

    
    Luxenberg, Scott I. wrote:
    > Greetings all,
    >
    > I have been trying to create/run a build farm as part of a project I am
    > working on. 
    
    That seems an odd thing to do since we have one ...
    
    > However, I have noticed the primary git repostitory,
    > git.postgresql.org/git, does not seem to be working. Namely, whenever I
    > try to clone the directory, I receive this error:
    >
    > Error: Unable to find 5e4933c31d3cd2750ee1793efe6eca43055fb273e under
    > http://git.postgresql.org/git/postgresql.git
    > Cannot obtain needed blob 5e4933c31d3cd2750ee1793efe6eca4305fb273e while
    > processing commit c5609c66ce2ee4fdb180be95721252b47f90499
    > Error: fetch failed.
    >
    > I thought it would be prudent to notify the list so someone could
    > possibly check into this.
    >
    >
    >   
    
    
    Why are you cloning over http? Here is the best way to clone, which 
    seems to be working:
    
        [andrew@sophia ]$ git clone --mirror
        git://git.postgresql.org/git/postgresql.git
        Initialized empty Git repository in /home/andrew/postgresql.git/
        remote: Counting objects: 376865, done.
        remote: Compressing objects: 100% (87569/87569), done.
        remote: Total 376865 (delta 310187), reused 352950 (delta 287485)
        Receiving objects: 100% (376865/376865), 178.73 MiB | 251 KiB/s, done.
        Resolving deltas: 100% (310187/310187), done.
        [andrew@sophia ]$
    
    cheers
    
    andrew
    
    

20. Re: Error with GIT Repository

    Stephen Frost <sfrost@noblis.org> — 2010-06-10T15:44:16Z

    * Andrew Dunstan (andrew@dunslane.net) wrote:
    > Luxenberg, Scott I. wrote:
    > >I have been trying to create/run a build farm as part of a project I am
    > >working on.
    > 
    > That seems an odd thing to do since we have one ...
    
    To clarify, he's setting up a build farm *member*. :)
    
    > >However, I have noticed the primary git repostitory,
    > >git.postgresql.org/git, does not seem to be working. Namely, whenever I
    > >try to clone the directory, I receive this error:
    > >
    > >Error: Unable to find 5e4933c31d3cd2750ee1793efe6eca43055fb273e under
    > >http://git.postgresql.org/git/postgresql.git
    > >Cannot obtain needed blob 5e4933c31d3cd2750ee1793efe6eca4305fb273e while
    > >processing commit c5609c66ce2ee4fdb180be95721252b47f90499
    > >Error: fetch failed.
    > >
    > >I thought it would be prudent to notify the list so someone could
    > >possibly check into this.
    > 
    > 
    > Why are you cloning over http? Here is the best way to clone, which
    > seems to be working:
    
    Unfortunately for us, the port that git uses isn't currently allowed
    outbound by our corporate firewall.  I expect that to be true for other
    PG users who want git and for some build-farm members, so I think we
    really need to support git cloning over http.
    
    As a side-note, it works just fine from git-hub's http mirror and that's
    what we've been playing with, but I don't know if we want to recommend
    that for build-farm members..
    
            Thanks!
    
                    Stephen
    

21. Re: Error with GIT Repository

    Andrew Dunstan <andrew@dunslane.net> — 2010-06-10T16:15:56Z

    
    Stephen Frost wrote:
    > * Andrew Dunstan (andrew@dunslane.net) wrote:
    >   
    >> Luxenberg, Scott I. wrote:
    >>     
    >>> I have been trying to create/run a build farm as part of a project I am
    >>> working on.
    >>>       
    >> That seems an odd thing to do since we have one ...
    >>     
    >
    > To clarify, he's setting up a build farm *member*. :)
    >   
    
    Aha. Amazing the difference one little word can make ...
    
    >
    > As a side-note, it works just fine from git-hub's http mirror and that's
    > what we've been playing with, but I don't know if we want to recommend
    > that for build-farm members..
    >
    >
    >   
    
    I don't see why not. Buildfarm members are going to have to reset their 
    repos when we finally cut over in a few months. Luckily, this is a 
    fairly painless operation - blow away the repo and change the config 
    file and the script will resync as if nothing had happened.
    
    cheers
    
    andrew
    
    

22. Re: Error with GIT Repository

    Stephen Frost <sfrost@snowman.net> — 2010-06-10T16:20:29Z

    * Andrew Dunstan (andrew@dunslane.net) wrote:
    > I don't see why not. Buildfarm members are going to have to reset their  
    > repos when we finally cut over in a few months. Luckily, this is a  
    > fairly painless operation - blow away the repo and change the config  
    > file and the script will resync as if nothing had happened.
    
    Should we stop bothering to offer http://git.postgresql.org then..?  Or
    do we expect it to get fixed and work correctly once we cut over and
    rebuild?  Also, perhaps we could list the git-hub option on the wiki
    (http://wiki.postgresql.org/wiki/Other_Git_Repositories)?
    
    (and, yea, it's the same me)
    
    	Thanks,
    
    		Stephen
    

23. Re: Error with GIT Repository

    Magnus Hagander <magnus@hagander.net> — 2010-06-10T17:30:00Z

    On Thu, Jun 10, 2010 at 18:20, Stephen Frost <sfrost@snowman.net> wrote:
    > * Andrew Dunstan (andrew@dunslane.net) wrote:
    >> I don't see why not. Buildfarm members are going to have to reset their
    >> repos when we finally cut over in a few months. Luckily, this is a
    >> fairly painless operation - blow away the repo and change the config
    >> file and the script will resync as if nothing had happened.
    >
    > Should we stop bothering to offer http://git.postgresql.org then..?  Or
    
    No, we should not.
    
    Especially if someone has a clue how to do it. The last time I fixed
    it by runnin repack, but that didn't work this time. I have no clue
    why it's asking for a file that doesn't exist.
    
    
    -- 
     Magnus Hagander
     Me: http://www.hagander.net/
     Work: http://www.redpill-linpro.com/
    
    

24. Re: Error with GIT Repository

    Alvaro Herrera <alvherre@commandprompt.com> — 2010-06-10T19:23:47Z

    Excerpts from Andrew Dunstan's message of jue jun 10 11:26:59 -0400 2010:
    
    > Why are you cloning over http? Here is the best way to clone, which 
    > seems to be working:
    > 
    >     [andrew@sophia ]$ git clone --mirror
    >     git://git.postgresql.org/git/postgresql.git
    >     Initialized empty Git repository in /home/andrew/postgresql.git/
    
    In case you're a git-ignorant like me and are wondering why the above
    does not produce a usable checkout, the complete recipe is here:
    
    http://archives.postgresql.org/message-id/20090602162347.GF23972@yugib.highrise.ca
    (in short, you need a git clone --reference)
    
    -- 
    Álvaro Herrera <alvherre@commandprompt.com>
    The PostgreSQL Company - Command Prompt, Inc.
    PostgreSQL Replication, Consulting, Custom Development, 24x7 support
    
    

25. Re: Error with GIT Repository

    Leonardo Francalanci <m_lists@yahoo.it> — 2010-06-11T07:43:16Z

    > Why are you cloning over http? 
    
    Me too I've used http, since I'm behind a proxy and I couldn't
    find a "simple" way of having the git:// method working behind
    a proxy...
    
    
          
    
    

26. Re: Error with GIT Repository

    Andres Freund <andres@anarazel.de> — 2010-06-11T17:12:26Z

    On Thursday 10 June 2010 19:30:00 Magnus Hagander wrote:
    > On Thu, Jun 10, 2010 at 18:20, Stephen Frost <sfrost@snowman.net> wrote:
    > > * Andrew Dunstan (andrew@dunslane.net) wrote:
    > >> I don't see why not. Buildfarm members are going to have to reset their
    > >> repos when we finally cut over in a few months. Luckily, this is a
    > >> fairly painless operation - blow away the repo and change the config
    > >> file and the script will resync as if nothing had happened.
    > > 
    > > Should we stop bothering to offer http://git.postgresql.org then..?  Or
    > 
    > No, we should not.
    > 
    > Especially if someone has a clue how to do it. The last time I fixed
    > it by runnin repack, but that didn't work this time. I have no clue
    > why it's asking for a file that doesn't exist.
    Does the repo run  'update-server-info'  in some hook?
    
    Andres
    
    

27. Re: Error with GIT Repository

    Magnus Hagander <magnus@hagander.net> — 2010-06-11T17:19:50Z

    On Fri, Jun 11, 2010 at 19:12, Andres Freund <andres@anarazel.de> wrote:
    > On Thursday 10 June 2010 19:30:00 Magnus Hagander wrote:
    >> On Thu, Jun 10, 2010 at 18:20, Stephen Frost <sfrost@snowman.net> wrote:
    >> > * Andrew Dunstan (andrew@dunslane.net) wrote:
    >> >> I don't see why not. Buildfarm members are going to have to reset their
    >> >> repos when we finally cut over in a few months. Luckily, this is a
    >> >> fairly painless operation - blow away the repo and change the config
    >> >> file and the script will resync as if nothing had happened.
    >> >
    >> > Should we stop bothering to offer http://git.postgresql.org then..?  Or
    >>
    >> No, we should not.
    >>
    >> Especially if someone has a clue how to do it. The last time I fixed
    >> it by runnin repack, but that didn't work this time. I have no clue
    >> why it's asking for a file that doesn't exist.
    > Does the repo run  'update-server-info'  in some hook?
    
    Yup, it runs after every time it pulls from cvs.
    
    -- 
     Magnus Hagander
     Me: http://www.hagander.net/
     Work: http://www.redpill-linpro.com/
    
    

28. Re: [PERFORM] No hash join across partitioned tables?

    Robert Haas <robertmhaas@gmail.com> — 2010-06-14T03:47:06Z

    On Thu, Jun 10, 2010 at 9:29 AM, Robert Haas <robertmhaas@gmail.com> wrote:
    > (moving to -hackers)
    >
    > On Wed, Jun 9, 2010 at 4:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
    >> Robert Haas <robertmhaas@gmail.com> writes:
    >>> In going back through emails I had marked as possibly needing another
    >>> look before 9.0 is released, I came across this issue again.  As I
    >>> understand it, analyze (or analyse) now collects statistics for both
    >>> the parent individually, and for the parent and its children together.
    >>>  However, as I further understand it, autovacuum won't actually fire
    >>> off an analyze unless there's enough activity on the parent table
    >>> considered individually to warrant it.  So if you have an empty parent
    >>> and a bunch of children with data in it, your stats will still stink,
    >>> unless you analyze by hand.
    >>
    >> Check.
    >>
    >>> Assuming my understanding of the problem is correct, we could:
    >>
    >>> (a) fix it,
    >>> (b) document that you should consider periodic manual analyze commands
    >>> in this situation, or
    >>> (c) do nothing.
    >>
    >>> Thoughts?
    >>
    >> The objections to (a) are that it might result in excessive ANALYZE work
    >> if not done intelligently, and that we haven't got a patch ready anyway.
    >> I would have liked to get to this for 9.0 but I feel it's a bit late
    >> now.
    >
    > I guess I can't really disagree with that.  Should we try to document
    > this in some way?
    
    Proposed patch attached.
    
    -- 
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise Postgres Company
    

29. Re: [PERFORM] No hash join across partitioned tables?

    Robert Haas <robertmhaas@gmail.com> — 2010-06-15T18:44:32Z

    On Sun, Jun 13, 2010 at 11:47 PM, Robert Haas <robertmhaas@gmail.com> wrote:
    > Proposed patch attached.
    
    Hearing no objections, I have committed this patch.
    
    -- 
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise Postgres Company
    
    

30. Re: Error with GIT Repository

    Daniel Farina <drfarina@acm.org> — 2010-06-30T22:22:09Z

    On Fri, Jun 11, 2010 at 10:19 AM, Magnus Hagander <magnus@hagander.net> wrote:
    >>> Especially if someone has a clue how to do it. The last time I fixed
    >>> it by runnin repack, but that didn't work this time. I have no clue
    >>> why it's asking for a file that doesn't exist.
    >> Does the repo run  'update-server-info'  in some hook?
    >
    > Yup, it runs after every time it pulls from cvs.
    
    Is this still a problem? I was just noticing this thread
    unceremoniously died, and a long time ago now I remembering discussing
    a problem involving the Postgres git mirror accumulating packfiles
    eternally. It seemed that whatever repacking scheme was used would get
    rid of loose objects, turning them into packs but never consolidate
    packs.
    
    Why not just run 'git gc'? This is probably the only quasi-regularly
    required maintenance command, so much so that git (I think) runs it
    from time to time when certain thresholds are passed in modern day.
    (For a clone-source it is probably a good idea to run it a bit more
    liberally)
    
    fdr
    
    

31. Re: No hash join across partitioned tables?

    Bruce Momjian <bruce@momjian.us> — 2010-07-02T03:00:00Z

    Tom Lane wrote:
    > Robert Haas <robertmhaas@gmail.com> writes:
    > > In going back through emails I had marked as possibly needing another
    > > look before 9.0 is released, I came across this issue again.  As I
    > > understand it, analyze (or analyse) now collects statistics for both
    > > the parent individually, and for the parent and its children together.
    > >  However, as I further understand it, autovacuum won't actually fire
    > > off an analyze unless there's enough activity on the parent table
    > > considered individually to warrant it.  So if you have an empty parent
    > > and a bunch of children with data in it, your stats will still stink,
    > > unless you analyze by hand.
    > 
    > Check.
    > 
    > > Assuming my understanding of the problem is correct, we could:
    > 
    > > (a) fix it,
    > > (b) document that you should consider periodic manual analyze commands
    > > in this situation, or
    > > (c) do nothing.
    > 
    > > Thoughts?
    > 
    > The objections to (a) are that it might result in excessive ANALYZE work
    > if not done intelligently, and that we haven't got a patch ready anyway.
    > I would have liked to get to this for 9.0 but I feel it's a bit late
    > now.
    
    What do we want to do about the above issue?
    
    -- 
      Bruce Momjian  <bruce@momjian.us>        http://momjian.us
      EnterpriseDB                             http://enterprisedb.com
    
      + None of us is going to be here forever. +
    
    

32. Re: No hash join across partitioned tables?

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-07-02T04:05:04Z

    Bruce Momjian <bruce@momjian.us> writes:
    > Tom Lane wrote:
    >> I would have liked to get to this for 9.0 but I feel it's a bit late
    >> now.
    
    > What do we want to do about the above issue?
    
    TODO item.
    
    			regards, tom lane
    
    

33. Re: No hash join across partitioned tables?

    Bruce Momjian <bruce@momjian.us> — 2010-07-02T20:53:44Z

    Tom Lane wrote:
    > Bruce Momjian <bruce@momjian.us> writes:
    > > Tom Lane wrote:
    > >> I would have liked to get to this for 9.0 but I feel it's a bit late
    > >> now.
    > 
    > > What do we want to do about the above issue?
    > 
    > TODO item.
    
    Added to TODO:
    
        Have autoanalyze of parent tables occur when child tables are modified
    
        * http://archives.postgresql.org/message-id/AANLkTinx8lLTEKWcyEQ1rxVz6WMJVKNezfXW5TKnNAU6@mail.gmail.com 
    
    I am surprised there is no documentation update requirement for this.
    
    -- 
      Bruce Momjian  <bruce@momjian.us>        http://momjian.us
      EnterpriseDB                             http://enterprisedb.com
    
      + None of us is going to be here forever. +
    
    

34. Re: No hash join across partitioned tables?

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-07-02T20:58:45Z

    Bruce Momjian <bruce@momjian.us> writes:
    > I am surprised there is no documentation update requirement for this.
    
    Somebody put something about it in the docs a few days ago, IIRC.
    
    			regards, tom lane
    
    

35. Re: No hash join across partitioned tables?

    Robert Haas <robertmhaas@gmail.com> — 2010-07-02T21:10:58Z

    On Fri, Jul 2, 2010 at 4:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
    > Bruce Momjian <bruce@momjian.us> writes:
    >> I am surprised there is no documentation update requirement for this.
    >
    > Somebody put something about it in the docs a few days ago, IIRC.
    
    That was me.
    
    http://archives.postgresql.org/pgsql-committers/2010-06/msg00144.php
    
    -- 
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise Postgres Company
    
    

36. Re: No hash join across partitioned tables?

    Bruce Momjian <bruce@momjian.us> — 2010-07-02T21:12:22Z

    Robert Haas wrote:
    > On Fri, Jul 2, 2010 at 4:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
    > > Bruce Momjian <bruce@momjian.us> writes:
    > >> I am surprised there is no documentation update requirement for this.
    > >
    > > Somebody put something about it in the docs a few days ago, IIRC.
    > 
    > That was me.
    > 
    > http://archives.postgresql.org/pgsql-committers/2010-06/msg00144.php
    
    Oh, thanks, I missed that.
    
    -- 
      Bruce Momjian  <bruce@momjian.us>        http://momjian.us
      EnterpriseDB                             http://enterprisedb.com
    
      + None of us is going to be here forever. +
    
    

37. Re: No hash join across partitioned tables?

    Alvaro Herrera <alvherre@commandprompt.com> — 2010-10-16T05:03:02Z

    Excerpts from Robert Haas's message of mié jun 09 15:47:55 -0400 2010:
    
    > In going back through emails I had marked as possibly needing another
    > look before 9.0 is released, I came across this issue again.  As I
    > understand it, analyze (or analyse) now collects statistics for both
    > the parent individually, and for the parent and its children together.
    >  However, as I further understand it, autovacuum won't actually fire
    > off an analyze unless there's enough activity on the parent table
    > considered individually to warrant it.  So if you have an empty parent
    > and a bunch of children with data in it, your stats will still stink,
    > unless you analyze by hand.
    
    So, is there something we could now do about this, while there's still
    time before 9.1?
    
    I haven't followed this issue very closely, but it seems to me that what
    we want is that we want an ANALYZE in a child table to be mutated into
    an analyze of its parent table, if the conditions are right; and that an
    ANALYZE of a parent removes the child tables from being analyzed on the
    same run.
    
    If we analyze the parent, do we also update the children stats, or is it
    just that we keep two stats for the parent, one with children and one
    without, both being updated when the parent is analyzed?
    
    If the latter's the case, maybe we should modify ANALYZE a bit more, so
    that we can analyze the whole hierarchy in one go, and store the lot of
    stats with a single pass (each child alone, the parent alone, the parent
    plus children).  However it's not real clear how would this work with
    multiple inheritance levels.
    
    -- 
    Álvaro Herrera <alvherre@commandprompt.com>
    The PostgreSQL Company - Command Prompt, Inc.
    PostgreSQL Replication, Consulting, Custom Development, 24x7 support
    
    

38. Re: No hash join across partitioned tables?

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-10-16T05:22:42Z

    Alvaro Herrera <alvherre@commandprompt.com> writes:
    > If we analyze the parent, do we also update the children stats, or is it
    > just that we keep two stats for the parent, one with children and one
    > without, both being updated when the parent is analyzed?
    
    The latter.
    
    The trick here is that we need to fire an analyze on the parent even
    though only its children may have had any updates.
    
    > If the latter's the case, maybe we should modify ANALYZE a bit more, so
    > that we can analyze the whole hierarchy in one go, and store the lot of
    > stats with a single pass (each child alone, the parent alone, the parent
    > plus children).  However it's not real clear how would this work with
    > multiple inheritance levels.
    
    It's also not clear how it works without blowing out memory...
    
    			regards, tom lane
    
    

39. Re: No hash join across partitioned tables?

    Sam Gendler <sgendler@ideasculptor.com> — 2010-10-16T05:35:46Z

    On Fri, Oct 15, 2010 at 10:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
    
    > Alvaro Herrera <alvherre@commandprompt.com> writes:
    > > If we analyze the parent, do we also update the children stats, or is it
    > > just that we keep two stats for the parent, one with children and one
    > > without, both being updated when the parent is analyzed?
    >
    > The latter.
    >
    > The trick here is that we need to fire an analyze on the parent even
    > though only its children may have had any updates.
    >
    > > If the latter's the case, maybe we should modify ANALYZE a bit more, so
    > > that we can analyze the whole hierarchy in one go, and store the lot of
    > > stats with a single pass (each child alone, the parent alone, the parent
    > > plus children).  However it's not real clear how would this work with
    > > multiple inheritance levels.
    >
    
    An issue with automatically analyzing the entire hierarchy is 'abstract'
    table definitions.  I've got a set of tables for storing the same data at
    different granularities of aggregation.  Within each granularity, I've got
    partitions, but because the set of columns is identical for each
    granularity, I've got an abstract table definition that is inherited by
    everything.  I don't need or want statistics kept on that table because I
    never query across the abstract table, only the parent table of each
    aggregation granularity
    
    create table abstract_fact_table (
    time timestamp,
    measure1 bigint,
    measure2 bigint,
    measure3 bigint,
    fk1 bigint,
    fk2 bigint
    );
    
    create table minute_scale_fact_table (
    } inherits abstract_fact_table;
    
    // Then there are several partitions for minute scale data
    
    create table hour_scale_fact_table (
    ) inherits abstract_fact_table;
    
    // then several partitions for hour scale data
    
    etc.  I do run queries on the minute_scale_fact_table and
    hour_scale_fact_table but never do so on abstract_fact_table.  I could
    certainly modify my schema such that the abstract table goes away entirely
    easily enough, but I find this easier for new developers to come in and
    comprehend, since the similarity between the table definitions is explicit.
    
    I'm glad this topic came up, as I was unaware that I need to run analyze on
    the parent partitions separately - and no data is every inserted directly
    into the top level of each granularity hierarchy, so it will never fire by
    itself.
    
    If I am using ORM and I've got functionality in a common baseclass in the
    source code, I'll often implement its mapping in the database via a parent
    table that the table for any subclass mapping can inherit from.  Again, I
    have no interest in maintaining statistics on the parent table, since I
    never query against it directly.
    

40. Re: No hash join across partitioned tables?

    Alvaro Herrera <alvherre@commandprompt.com> — 2010-10-16T15:29:39Z

    Excerpts from Samuel Gendler's message of sáb oct 16 02:35:46 -0300 2010:
    
    > An issue with automatically analyzing the entire hierarchy is 'abstract'
    > table definitions.  I've got a set of tables for storing the same data at
    > different granularities of aggregation.  Within each granularity, I've got
    > partitions, but because the set of columns is identical for each
    > granularity, I've got an abstract table definition that is inherited by
    > everything.  I don't need or want statistics kept on that table because I
    > never query across the abstract table, only the parent table of each
    > aggregation granularity
    
    Hmm, I think you'd be better served by using LIKE instead of regular
    inheritance.
    
    -- 
    Álvaro Herrera <alvherre@commandprompt.com>
    The PostgreSQL Company - Command Prompt, Inc.
    PostgreSQL Replication, Consulting, Custom Development, 24x7 support
    
    

41. Re: No hash join across partitioned tables?

    Sam Gendler <sgendler@ideasculptor.com> — 2010-10-18T06:13:01Z

    On Sat, Oct 16, 2010 at 8:29 AM, Alvaro Herrera
    <alvherre@commandprompt.com>wrote:
    
    > Excerpts from Samuel Gendler's message of sáb oct 16 02:35:46 -0300 2010:
    >
    > > An issue with automatically analyzing the entire hierarchy is 'abstract'
    > > table definitions.  I've got a set of tables for storing the same data at
    > > different granularities of aggregation.  Within each granularity, I've
    > got
    > > partitions, but because the set of columns is identical for each
    > > granularity, I've got an abstract table definition that is inherited by
    > > everything.  I don't need or want statistics kept on that table because I
    > > never query across the abstract table, only the parent table of each
    > > aggregation granularity
    >
    > Hmm, I think you'd be better served by using LIKE instead of regular
    > inheritance.
    >
    >
    Yep.  I inherited the architecture, though, and changing it hasn't been a
    high priority.
    
    --sam
    

42. Re: No hash join across partitioned tables?

    Alvaro Herrera <alvherre@commandprompt.com> — 2010-10-18T14:44:53Z

    Excerpts from Samuel Gendler's message of lun oct 18 03:13:01 -0300 2010:
    > On Sat, Oct 16, 2010 at 8:29 AM, Alvaro Herrera
    > <alvherre@commandprompt.com>wrote:
    > 
    > > Excerpts from Samuel Gendler's message of sáb oct 16 02:35:46 -0300 2010:
    > >
    > > > An issue with automatically analyzing the entire hierarchy is
    > > > 'abstract' table definitions.  I've got a set of tables for
    > > > storing the same data at different granularities of aggregation.
    > > > Within each granularity, I've got partitions, but because the set
    > > > of columns is identical for each granularity, I've got an abstract
    > > > table definition that is inherited by everything.  I don't need or
    > > > want statistics kept on that table because I never query across
    > > > the abstract table, only the parent table of each aggregation
    > > > granularity
    > >
    > > Hmm, I think you'd be better served by using LIKE instead of regular
    > > inheritance.
    >
    > Yep.  I inherited the architecture, though, and changing it hasn't been a
    > high priority.
    
    I understand that; my point is merely that maybe we shouldn't work
    through many hoops to solve this particular facet of the problem,
    because it seems to be pilot error.  (If you really needed to avoid the
    extra I/O that would be caused by unnecessary analyzes, you could turn
    autovac off for the abstract tables).
    
    -- 
    Álvaro Herrera <alvherre@commandprompt.com>
    The PostgreSQL Company - Command Prompt, Inc.
    PostgreSQL Replication, Consulting, Custom Development, 24x7 support
    
    

43. Re: No hash join across partitioned tables?

    Robert Haas <robertmhaas@gmail.com> — 2010-10-26T12:23:26Z

    On Sat, Oct 16, 2010 at 1:22 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
    > Alvaro Herrera <alvherre@commandprompt.com> writes:
    >> If we analyze the parent, do we also update the children stats, or is it
    >> just that we keep two stats for the parent, one with children and one
    >> without, both being updated when the parent is analyzed?
    >
    > The latter.
    >
    > The trick here is that we need to fire an analyze on the parent even
    > though only its children may have had any updates.
    
    Can we execute a SQL query at the point where we need this
    information?  Because it doesn't seem too hard to work up a query that
    totals the inserts, updates, and reltuples across all children of each
    table.
    
    -- 
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise PostgreSQL Company