Re: Strangae Query Plans - Mailing list 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