Re: Strangae Query Plans
От | Anil Kumar |
---|---|
Тема | Re: Strangae Query Plans |
Дата | |
Msg-id | 20030130090859.68342.qmail@web13906.mail.yahoo.com обсуждение исходный текст |
Ответ на | Strangae Query Plans (Anil Kumar <techbreeze@yahoo.com>) |
Ответы |
Re: Strangae Query Plans
("Rajesh Kumar Mallah." <mallah@trade-india.com>)
|
Список | 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
В списке pgsql-performance по дате отправления: