From: Anil Kumar <techbreeze@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: Strangae Query Plans
Date: Thu, 30 Jan 2003 00:25:18 -0800 (PST)
Message-ID: <20030130082518.8953.qmail@web13902.mail.yahoo.com> (raw)
Greetings to all,
I have found strange query execution plans with the
same version of
PostgreSQL but on different types of server machines.
Here are the details
of the servers:
Server 1:
Pentium III, 800 MHz, 64 MB of RAM
RedHat Linux 7.2, Postgres ver 7.1
Server 2:
Dual Pentium III, 1.3 GHz, 512 MB of RAM
RedHat Linux 7.3 (SMP kernel), Postgres ver 7.1
Here is the query I tried:
--- query ---
explain
select bill.customer_no, bill.bill_no, bill.bill_date
from bill, ( select customer_no, max(
bill_date) as bill_date from
bill group by customer_no) as t_bill where
bill.customer_no = t_bill.customer_no and
bill.bill_date = t_bill.bill_date order by
bill.customer_no;
--- query---
Result on Server 1:
---result---
NOTICE: QUERY PLAN:
Merge Join (cost=2436.88..2571.99 rows=671 width=44)
-> Sort (cost=1178.15..1178.15 rows=8189 width=28)
-> Seq Scan on bill (cost=0.00..645.89
rows=8189 width=28)
-> Sort (cost=1258.72..1258.72 rows=819 width=16)
-> Subquery Scan t_bill
(cost=1178.15..1219.10 rows=819 width=16)
-> Aggregate (cost=1178.15..1219.10
rows=819 width=16)
-> Group (cost=1178.15..1198.63
rows=8189 width=16)
-> Sort
(cost=1178.15..1178.15 rows=8189 width=16)
-> Seq Scan on bill
(cost=0.00..645.89 rows=8189 width=16)
EXPLAIN
---result---
Result on Server 2:
---result---
NOTICE: QUERY PLAN:
Sort (cost=0.04..0.04 rows=1 width=44)
-> Nested Loop (cost=0.01..0.03 rows=1 width=44)
-> Seq Scan on bill (cost=0.00..0.00 rows=1
width=28)
-> Subquery Scan t_bill (cost=0.01..0.02
rows=1 width=16)
-> Aggregate (cost=0.01..0.02 rows=1
width=16)
-> Group (cost=0.01..0.01 rows=1
width=16)
-> Sort (cost=0.01..0.01
rows=1 width=16)
-> Seq Scan on bill
(cost=0.00..0.00 rows=1 width=16)
EXPLAIN
---result---
Can someone help me to figure out why the query plans
come out differently
despite the fact that almost everything but the number
of CPUs are same in
both the machines?
Also the dual processor machine is awfully slow when I
execute this query
and the postmaster hogs the CPU (99.9%) for several
minutes literally
leaving that server unusable.
thank you very much
Anil
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: techbreeze@yahoo.com
Subject: Re: Strangae Query Plans
In-Reply-To: <20030130082518.8953.qmail@web13902.mail.yahoo.com>
References: <20030130082518.8953.qmail@web13902.mail.yahoo.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