pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rajesh Kumar Mallah. <mallah@trade-india.com>
To: Anil Kumar <techbreeze@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: Re: Strangae Query Plans
Date: Thu, 30 Jan 2003 16:18:11 +0530
Message-ID: <200301301618.11952.mallah@trade-india.com> (raw)
In-Reply-To: <20030130090859.68342.qmail@web13906.mail.yahoo.com>
References: <20030130090859.68342.qmail@web13906.mail.yahoo.com>



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.





view thread (3+ messages)

Message-ID: <200301301618.11952.mallah@trade-india.com>
Permalink:  ../200301301618.11952.mallah@trade-india.com/
Also on:    postgresql.org/message-id/200301301618.11952.mallah@trade-india.com

 · 

reply

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: mallah@trade-india.com, techbreeze@yahoo.com
  Subject: Re: Strangae Query Plans
  In-Reply-To: <200301301618.11952.mallah@trade-india.com>
  References: <20030130090859.68342.qmail@web13906.mail.yahoo.com> <200301301618.11952.mallah@trade-india.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