Thread: Strangae Query Plans

Strangae Query Plans

From

Anil Kumar

Date:

30 January 2003, 06:25:20

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

Re: Strangae Query Plans

From

Anil Kumar

Date:

30 January 2003, 07:09:01

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

Re: Strangae Query Plans

From

"Rajesh Kumar Mallah."

Date:

30 January 2003, 08:43:08


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.