pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedStrangae Query Plans
3+ messages / 2 participants
[nested] [flat]
* Strangae Query Plans
@ 2003-01-30 08:25 Anil Kumar <techbreeze@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Anil Kumar @ 2003-01-30 08:25 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Strangae Query Plans
@ 2003-01-30 09:08 Anil Kumar <techbreeze@yahoo.com>
parent: Anil Kumar <techbreeze@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Anil Kumar @ 2003-01-30 09:08 UTC (permalink / raw)
To: pgsql-performance
Hi,
I got this solved. We ran "vacuum" with the --analyze flag on the
second server. And now the query plan is same as the first one and
it returns in a fraction of a second!
Anil
--- Anil Kumar <techbreeze@yahoo.com> wrote:
>
> 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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Strangae Query Plans
@ 2003-01-30 10:48 Rajesh Kumar Mallah. <mallah@trade-india.com>
parent: Anil Kumar <techbreeze@yahoo.com>
0 siblings, 0 replies; 3+ messages in thread
From: Rajesh Kumar Mallah. @ 2003-01-30 10:48 UTC (permalink / raw)
To: Anil Kumar <techbreeze@yahoo.com>; pgsql-performance
you could consider vacuuming thru a cron job daily..
its good for db severs' health ;-)
On Thursday 30 January 2003 02:38 pm, Anil Kumar wrote:
> Hi,
>
> I got this solved. We ran "vacuum" with the --analyze flag on the
> second server. And now the query plan is same as the first one and
> it returns in a fraction of a second!
>
> Anil
>
> --- Anil Kumar <techbreeze@yahoo.com> wrote:
> > 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
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
>
> __________________________________________________
> Do you Yahoo!?
> Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
> http://mailplus.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
--------------------------------------------
Regds Mallah
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)26152172 (221) (L) 9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-01-30 10:48 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-30 08:25 Strangae Query Plans Anil Kumar <techbreeze@yahoo.com>
2003-01-30 09:08 ` Anil Kumar <techbreeze@yahoo.com>
2003-01-30 10:48 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
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