pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Anil Kumar <techbreeze@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: Re: Strangae Query Plans
Date: Thu, 30 Jan 2003 01:08:59 -0800 (PST)
Message-ID: <20030130090859.68342.qmail@web13906.mail.yahoo.com> (raw)
In-Reply-To: <20030130082518.8953.qmail@web13902.mail.yahoo.com>

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



view thread (3+ messages)  latest in thread

Message-ID: <20030130090859.68342.qmail@web13906.mail.yahoo.com>
Permalink:  ../20030130090859.68342.qmail@web13906.mail.yahoo.com/
Also on:    postgresql.org/message-id/20030130090859.68342.qmail@web13906.mail.yahoo.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: techbreeze@yahoo.com
  Subject: Re: Strangae Query Plans
  In-Reply-To: <20030130090859.68342.qmail@web13906.mail.yahoo.com>
  References: <20030130082518.8953.qmail@web13902.mail.yahoo.com> <20030130090859.68342.qmail@web13906.mail.yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox