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
