X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8007B3A4252 for ; Wed, 17 Nov 2004 04:35:55 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 17099-05 for ; Wed, 17 Nov 2004 04:35:53 +0000 (GMT) Received: from mail.pws.com.au (mail.pws.com.au [210.23.138.139]) by svr1.postgresql.org (Postfix) with SMTP id C83033A4249 for ; Wed, 17 Nov 2004 04:35:51 +0000 (GMT) Received: (qmail 24709 invoked by uid 0); 17 Nov 2004 15:35:45 +1100 Received: from cpe-203-45-44-212.vic.bigpond.net.au (HELO wizzard.pws.com.au) (russell@pws.com.au@203.45.44.212) by mail.pws.com.au with SMTP; 17 Nov 2004 15:35:45 +1100 From: Russell Smith To: pgsql-performance@postgresql.org Subject: Re: query plan question Date: Wed, 17 Nov 2004 15:35:42 +1100 User-Agent: KMail/1.7 References: <07FDEE0ED7455A48AC42AC2070EDFF7C26B972@corpsrv2.tazznetworks.com> In-Reply-To: <07FDEE0ED7455A48AC42AC2070EDFF7C26B972@corpsrv2.tazznetworks.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200411171535.42690.mr-russ@pws.com.au> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/237 X-Sequence-Number: 9191 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