Re: Strangae Query Plans - Mailing list pgsql-performance
From | Rajesh Kumar Mallah. |
---|---|
Subject | Re: Strangae Query Plans |
Date | |
Msg-id | 200301301618.11952.mallah@trade-india.com Whole thread Raw |
In response to | Re: Strangae Query Plans (Anil Kumar <techbreeze@yahoo.com>) |
List | 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.
pgsql-performance by date: