I have a query for which postgres is generating a different plan on different 
machines. The database schema is the same, the dataset is the same, the 
configuration is the same (e.g., pg_autovacuum running in both cases), both 
systems are Solaris 9. The main difference in the two systems is that one is 
sparc and the other is intel.

The query runs in about 40 ms on the intel box, but takes about 18 seconds on 
the sparc box. Now, the intel boxes we have are certainly faster, but I'm 
curious why the query plan might be different.

For the intel:

QUERY PLAN
Unique  (cost=11.50..11.52 rows=2 width=131)
  ->  Sort  (cost=11.50..11.50 rows=2 width=131)
        Sort Key: up.prefix, s.name, s.tuid, s.foundryversion
        ->  Hash Join  (cost=10.42..11.49 rows=2 width=131)
              Hash Cond: ("outer".dbid = "inner"."schema")
              ->  Seq Scan on "schema" s  (cost=0.00..1.02 rows=2 width=128)
              ->  Hash  (cost=10.41..10.41 rows=4 width=11)
                    ->  Nested Loop  (cost=0.00..10.41 rows=4 width=11)
                          ->  Nested Loop  (cost=0.00..2.14 rows=4 width=4)
                                ->  Seq Scan on flow fl  (cost=0.00..0.00 
rows=1 width=4)
                                      Filter: (servicetype = 646)
                                ->  Index Scan using usage_flow_i on "usage" u  
(cost=0.00..2.06 rows=6 width=8)
                                      Index Cond: (u.flow = "outer".dbid)
                          ->  Index Scan using usageparameter_usage_i on 
usageparameter up  (cost=0.00..2.06 rows=1 width=15)
                                Index Cond: (up."usage" = "outer".dbid)
                                Filter: ((prefix)::text <> 'xsd'::text)

For the sparc:

QUERY PLAN
Unique  (cost=10.81..10.83 rows=1 width=167)
  ->  Sort  (cost=10.81..10.82 rows=1 width=167)
        Sort Key: up.prefix, s.name, s.tuid, s.foundryversion
        ->  Nested Loop  (cost=9.75..10.80 rows=1 width=167)
              Join Filter: ("outer".flow = "inner".dbid)
              ->  Hash Join  (cost=9.75..10.79 rows=1 width=171)
                    Hash Cond: ("outer".dbid = "inner"."schema")
                    ->  Seq Scan on "schema" s  (cost=0.00..1.02 rows=2 
width=128)
                    ->  Hash  (cost=9.75..9.75 rows=1 width=51)
                          ->  Nested Loop  (cost=0.00..9.75 rows=1 width=51)
                                Join Filter: ("inner"."usage" = "outer".dbid)
                                ->  Index Scan using usage_flow_i on "usage" u  
(cost=0.00..4.78 rows=1 width=8)
                                ->  Index Scan using usageparameter_schema_i on 
usageparameter up  (cost=0.00..4.96 rows=1 width=51)
                                      Filter: ((prefix)::text <> 'xsd'::text)
              ->  Seq Scan on flow fl  (cost=0.00..0.00 rows=1 width=4)
                    Filter: (servicetype = 646)

I assume the problem with the second plan starts with doing a Nested Loop 
rather than a Hash Join at the 4th line of the plan, but I don't know why it 
would be different for the same schema, same dataset.

What factors go into the planner's decision to choose a nested loop over a hash 
join? Should I be looking at adjusting my runtime configuration on the sparc 
box somehow?

Thanks.

- DAP
----------------------------------------------------------------------------------
David Parker    Tazz Networks    (401) 709-5130
 

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Reply via email to