On Wed, 17 Nov 2004 02:54 pm, you wrote:
> 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)
> 
Unique  (cost=11.50..11.52 rows=2 width=131)
Unique  (cost=10.81..10.83 rows=1 width=167)

The estimations for the cost is basically the same, 10ms for the first row.  
Can you supply Explain analyze to see what it's actually doing?

Russell Smith

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

Reply via email to