Re: Strangae Query Plans

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(at)yahoo(dot)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.