From: Joshua D. Drake <jd@commandprompt.com>
To: David Parker <dparker@tazznetworks.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: query plan question
Date: Tue, 16 Nov 2004 22:40:07 -0800
Message-ID: <419AF247.90209@commandprompt.com> (raw)
In-Reply-To: <07FDEE0ED7455A48AC42AC2070EDFF7C26B972@corpsrv2.tazznetworks.com>
References: <07FDEE0ED7455A48AC42AC2070EDFF7C26B972@corpsrv2.tazznetworks.com>
David Parker 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.
>
>
If they are the same and PostgreSQL are the same, are the intel machines
Xeons?
Sincerely,
Joshua D. Drake
>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
>
>
--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL
Attachments:jd.vcf (text/x-vcard, 283B) download
begin:vcard
fn:Joshua Drake
n:Drake;Joshua
org:Command Prompt, Inc.
adr:;;PO Box 215 ;Cascade Locks;OR;97014;US
email;internet:jd@commandprompt.com
title:Consultant
tel;work:503-667-4564
tel;fax:503-210-0334
x-mozilla-html:FALSE
url:http://www.commandprompt.com
version:2.1
end:vcard
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jd@commandprompt.com, dparker@tazznetworks.com
Subject: Re: query plan question
In-Reply-To: <419AF247.90209@commandprompt.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox